Aggregation Design and Usage-Based Optimization - Microsoft SQL Server 2008 Analysis Services - page 415

Time Store Warehouse

Product

All
Year
Quarter
Month
Date

All
Store Country
Store State
Store City
Store

All
Country
State Province
City
Warehouse

All

Product Family

Product Department

Product Category
Product Subcategory

Brand

Product

FIGURE 23.4

The upper line represents the granularity of our aggregation.

With partitions, you can store your data on the hard drive in a separate data structure (the
aggregation). The next time there is a request for data of the same granularity as repre-
sented in the aggregation, the result is ready.

An aggregation is typically smaller than the fact data it is based on. The smallest aggrega-
tion is one that contains, in a single row, the grand totals or all levels for the data stored
in the partition (see Figure 23.5).

Product Time Store Warehouse Units_Ordered Units_shipped

All All All All 227238 227238

FIGURE 23.5

This single row represents the grand totals and all levels of the partition data.

This aggregation gives us the grand total of units ordered and units shipped for all years, in
all stores, for all products, and in all warehouses. When it's built, the aggregation will
contain the single row shown in Figure 23.5. It will return responses to queries very quickly.

Designing Aggregations

If one aggregation increases query performance, why not build all the possible aggrega-
tions for the cube so that every query is answered by some aggregation? Because the
number of potential aggregations is so large that if you were to build all of them, it would
take a lot of time and a lot of space.

For example, we'll calculate how many potential aggregations you can have for just the
Warehouse measure group in the Warehouse and Sales cube. (Figure 23.4 shows a diagram
of the Warehouse measure group aggregations.) If you choose a single attribute from every
dimension in the measure group, you would define the aggregation by { Product.
attribute, Time.attribute, Store.attribute, Warehouse.Attribute } . You can calcu-
late the number of aggregations you could create; it would be the product of the number