Mention aggregations to any OLAP geek and you will likely hear that aggregations are the cornerstones of OLAP databases.

In the new attribute paradigm of Analysis Services 2005, designing effective aggregations can be a somewhat challenging task. In fact there are many components that go into building an effective aggregation design.

In this entry, I am going to highlight the design components that determine which attributes are considered for aggregation:

·         Aggregation Definition- An aggregation summarizes measures by a combination of attributes.

·         Attribute Relationships - Creating EFFECTIVE attribute relationships BETWEEN your attributes is a best practice for EVERY cube implementation. Having the correct attribute relationships in your dimension can have all sorts of benefits beyond aggregations since attribute relationships influence how data is stored, retrieved, and queried. Unfortunately, the inverse scenario is also true, keeping the default attribute relationships that directly relate every attribute to the key attribute can lead to querying and processing performance issues. Wherever possible, the ideal scenario is to create cascading attribute relationships that indirectly relate attributes to the key attribute.

·         Aggregation Candidates –Aggregation candidates are attributes that are considered for aggregation. By default, not every attribute is considered for aggregation. It has to be invited, of courseJ. You can view the aggregation candidates for a partition when you use the Aggregation Design Wizard to design aggregations (see screen shot below). On the Specify Object Counts page of the Aggregation Design Wizard, the bolded attributes represent the aggregation candidates. If an attribute is not bolded, then it is not an aggregation candidate.

·         Aggregation Usage - The Aggregation Usage property determines whether an attribute is considered for aggregation, i.e. whether or not it is an aggregation candidate. By default, it has the value of, you guessed it, Default. Default has a variety of rules that it applies depending on the attribute type. Below are the highlights of the Default rule for a standard dimension design:

o        For the granularity attribute, no aggregation restrictions are placed on the aggregation design, i.e. the granularity attribute is a candidate.

o        If the dimension has natural hierarchies, then all attributes participating in the natural hierarchy are candidates.  Remember that a natural hierarchy is a special instance of a user-defined hierarchy with many-to-one attribute relationships defined for each level in the hierarchy. If attribute relationships are not defined for every level in the hierarchy, then it is not natural and the attributes missing relationships are not considered for aggregation.

o        For any other attribute, do not consider the attribute for aggregation.

To see how these components work together to define the aggregation candidates, the Screen Shot below for the Aggregation Design Wizard displays the aggregation candidates for 6 different design scenarios of the Territory dimension.

Each design scenario represents a different configuration based on the following three variables:

(1)   Attribute hierarchies or natural hierarchies – Scenarios 1 – 4 only use attribute hierarchies. Scenarios 5 and 6 include natural hierarchies.

(2)   Default or modified attribute relationships. Either the default Attribute relationships have been left as-is (No AR) or the attribute relationships have been modified (With AR)

(3)   Default or unrestricted Aggregation Usage property. Default uses the Default rule. Unrestricted means consider this attribute as an aggregation candidate; however, it must pass the standard aggregation cost/benefit tests.

What’s the bottom line? In the scenarios outlined above, Scenario 6 and Scenario 2 provide the best solutions from an aggregation candidate perspective. In fact, all things being equal, they should produce the same aggregation design, but I have not fully tested this to ensure that this in the case J.  

Personally I prefer Scenario 6. From an end user browsing perspective, user hierarchies provide users with pre-built navigation paths. Also from an MDX coding perspective, natural hierarchies provide easy access to navigation functions (ancestor, descendants, etc. to move around the hierarchy). You can always hide the attribute hierarchies if having both attribute hierarchies and user hierarchies is confusing to end users. Also important to note is that the SSAS OLAP client tools are taking slightly different approaches for exposing the attribute hierarchies to end users. The tools seem to be more predictable in the way that they expose natural hierarchies.

Keep in mind that all we have done in this example is to influence the aggregation candidates. You still need to specify the other aggregation options including specifying record and members counts, deciding the percent aggregation, and storage mode.

Even with these user-defined inputs, the Analysis Services aggregation design algorithm still has to evaluate the costs and benefits of each aggregation to determine whether or not it is ultimately built. … unless, of course, you build your custom aggregations using XMLA but that is a topic for another day.