Databases Reference
In-Depth Information
Design. To optimize the performance of queries that begin from the
dimension tables, the Entity Keys that join to Type 1 d imension tables
should each be covered by a secondary index, and the Instance Keys that
join to Type 2 dimension tables should each be covered by a secondary
index.
tIme summAry tABles
The detailed and granular data in a fact table can be summarized by any
dimension hierarchy referenced in that fact table. Transactions can be
summarized by a Product hierarchy up to the Department level of that
hierarchy. Events can be summarized by a Labor hierarchy up to the
Skill level of that hierarchy. The Time hierarchy is a ubiquitous sum-
mary scheme. Typically, a data warehouse will summarize individual
transactions up to the Day and Week. From those two Time summa-
ries, other less frequent Time summaries can be performed at run time.
The “Summary Tables” section of Chapter 11 explained that Time sum-
maries in this Time Variant Solution Design can be defined as either
Type 1 Summary tables or Type 2 Summary tables. A Type 1 Summary
table is different from its source Fact table in that a Type 1 Summary table
has only Entity Keys and no Instance Keys. A Type 2 Summary table is dif-
ferent from its source Fact table in that a Type 2 Summary table has only
Instance Keys and no Entity Keys.
A Type 2 Summary table could serve both purposes as a Type 1 a nd
Type 2 Summary table by retaining both the Entity Keys and Instance
Keys. Such a hybrid Type 1/Type 2 Summary table is feasible because hier-
archically all Instance Keys for an Entity Key are contained within that
Entity Key. When a hybrid Type 1/Type 2 Summary table joins to a Type 1
Dimension table, a summation operation must occur again to sum the
quantitative measurements up to the Entity Keys in the Summary table.
When a hybrid Type 1/Type 2 Summary table joins to a Type 2 Dimension
table, no additional summation operation is required to sum the quantita-
tive measurements up to the Instance Keys in the Summary table.
The decision to deploy a Type 1 Summary table, Type 2 Summary table,
or hybrid Type 1/Type 2 Summary table is a judgment call between the
requirements, disk capacity, and CPU and I/O capacity of the data ware-
house RDBMS.
