KB9862: How do aggregate tables work?

Aggregate tables store pre-summarized totals at a higher level of aggregation than the most granular fact table. They allow reports to be generated from small, rather than large, tables; therefore, performance is enhanced. A successful aggregation strategy seeks to choose aggregate tables that will have the most impact while taking the least amount of space.
 
Aggregation decisions are driven by the following factors:
 

• Usage patterns: Build aggregate tables that are likely to be used the most.
• Compression ratios: The compression ratio between two tables is defined as the size of the aggregate compared to the size of the smallest table from which the aggregate can be derived.
• Volatility: Changes in hierarchies over time impact the accuracy of aggregate tables. Sometimes aggregate tables must be rebuilt as a result of changes in dimensions.

A good candidate for aggregation should have at most 10-15 percent of the size of the smallest table from which it is derived.
 
EXAMPLE:
The Strategy Tutorial project uses aggregate tables by default. A simple metric sum (Revenue) will go to different aggregate tables depending on the attributes on the template.
 

• Create a report with Year on the rows and Revenue on the columns.
• Execute the report and view the SQL:
  

• Drill from Year to Item and view the SQL:
  

The query will go from using ORDER_FACT to ORDER_DETAIL. When Year is on the template, the engine selects the smaller table and the fact is calculated as:
 
sum(a11.ORDER_AMT)
instead of:
 
sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)))