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

CHAPTER 23

IN THIS CHAPTER

. Aggregations and Collection

of Aggregations

Aggregation Design and
Usage-Based
Optimization

. Designing Aggregations

. Query Usage Statistics

. Manual Design and

Management of Aggregations

A nalysis Services has many objects, but physically it stores
data only in dimensions and partitions. Partitions serve as
storage for fact data. To provide fast access to the partition
data, Analysis Services allows for creating two auxiliary data
structures: partition indexes and aggregations. Aggregations
and indexes are created at processing time. (For information
about storing data, see Chapter 20, “Physical Data Model.”)

. Monitoring Aggregation Usage

Aggregations are data structures that store data that is precal-
culated (aggregated) based on the data stored in a specific
partition. Because the size of an aggregation is usually
smaller than the original data, you can query that aggre-
gated data and get a much quicker response. Designing a
subset of aggregations that best suits your query load is a
nontrivial task. The discussion that follows will help you to
understand the basic principles of aggregation.

Aggregations and Collection of
Aggregations

Let's use the Warehouse measure group from the FoodMart
2008 sample database to explore the definition and useful-
ness of aggregations. Fact data stored in a partition is the
same data as that in the fact table: a collection of measures
and the dimension keys on the granularity level of the
measure group. Figure 23.1 shows an example from the
Warehouse measure group.

The diagram in Figure 23.2 takes the same data shown in
Figure 23.1 and arranges it so that you can see the hierarchies
of the Product , Time , Store , and Warehouse dimensions.