My first Publication Agile-Data-Warehouse-Design-eBook | Page 274

254
Chapter 8
How Many
Collapsed Dimension Aggregate Pattern
Collapsed
aggregates are
pre-joined
aggregates
Collapsed dimension aggregates are created by summarizing a fact table using
selected dimensional attributes, and storing the facts and the dimensional attrib-
utes in a single, denormalized summary table. Figure 8-16 shows a collapsed
dimension aggregate for Sales by Quarter and Product Type.
Figure 8-16
Collapsed
dimension
aggregate
Collapsed dimension aggregates can offer additional query acceleration because
the dimensions and facts are pre-joined. However if many attributes are included
the increased record length will make the table too large. An aggregate might have
20 times fewer records than its base fact table but if it is three times wider it will
not deliver sufficient performance improvements to justify itself.
Aggregation Guidelines
The following guidelines will help you get a good set of aggregates in place:
Budget up to a 100% overhead for aggregate storage and ETL processing.
Create aggregates that are approximately 20 times smaller than their base fact
tables. Spread aggregates, by designing aggregates of aggregates (400 times
smaller than the base fact tables).
Use (fast refreshable) materialized views whenever possible to build aggregates,
and enable DBMS aggregate navigation and query rewrite features.
Design invisible aggregates that the DBMS will automatically redirect queries
to. Don’t allow BI users, reports, or dashboards to become directly dependent
on an aggregate. Hide them from query and reporting tools.
Trust base star schemas to handle highly constrained queries using star join
indexes—and focus aggregates on addressing broad summary queries.
Monitor aggregate utilization, drop those that are seldom used, and add new
aggregates as query patterns change.
Make sure that you initially build aggregates that will speed up comparisons
against budgets, targets, and forecasts. These are the most obvious quick-win
aggregates.