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

no attribute from the Time , Store , or Warehouse dimensions. The absence of attributes
indicates that these dimensions are aggregated to the highest level. You can think of this
as if the granularity of the data is set to the All level for a dimension that has all its attrib-
utes set to 0 .
You can calculate the total number of different series of digits like this one for the
Warehouse measure group. Use the following formula: 2 to the power of (num Product
attributes + num Time attributes + num Store attributes + num Warehouse attributes) = 2
to the power of (6 + 4 + 4 + 4) = 262,144—a huge number.
The total number of aggregations is a little smaller than that because even in a relational
reporting-style dimension, all the attributes are related to the key attribute. If the key
attribute of the dimension participates in the aggregation, it doesn't make sense for any
other attribute from the same dimension to participate.
Use the following formula to calculate the number of potential aggregations in the
Warehouse measure group:
2 to the power of (5 + 3 + 3 + 3) [the aggregations that contain nonkey attributes] +
2 to the power of (3 + 3 + 3) [the aggregations that contain the key attribute of the
Product dimension and nonkey attributes from other dimensions] +
2 to the power of (5 + 3 + 3) [the key attribute of the Time dimension] +
2 to the power of (5 + 3 + 3) [the key attribute of the Store dimension] +
2 to the power of (5 + 3 + 3) [the key attribute of the Warehouse dimension] =
22,528
This is a huge number. We considered only 4 dimensions with a total number of 18 attrib-
utes. You can see now that it's very expensive to optimize your cube when you have rela-
tional reporting-style dimensions. Even with a small number of attributes, it's really
expensive to maintain good overall query performance with relational reporting-style
dimensions.
It's even harder to optimize cube performance with relational reporting-style dimensions
because queries can't reuse data from smaller aggregations. For example, in the earlier
dimension with natural hierarchies, a query to the Store State attribute could use data
from the aggregation with the Store City attribute. When you have a relational report-
ing-style dimension, a query can use data from only the aggregation with the Store State
attribute if it exists. Otherwise, the data for the query has to come from the fact data.
Flexible Versus Rigid Aggregations
Depending on the way you defined relationships between attributes in a dimension,
Analysis Services assigns a type (either rigid or flexible) to an aggregation. The type is
assigned based on a set of rules about those relationships. Flexible aggregations are