30 Aug 2018

Aggregations in SSAS Cube

If you have ever searched on how to optimize a SSAS Cube or any similar thing then I am sure you must have heard of Aggregation in SSAS Cube.

What is Aggregation?

○ Aggregation is like creating summary table in SSAS engine, benefit is we do not need to maintain these summary table and it contains information which can be accessed quickly.
○ In Default Aggregation type, MOLAP we cannot see the indexed view in which aggregation are stored while in ROLAP we can see the indexed view below is the example:
○ Aggregation is a data structure that stores pre-calculated data that analysis service uses to enhance query performance.
○ Aggregation Design can be defined for each partition independently ○ Each partition can be thought of as being an aggregation at the lowest granularity of the measure group.
○ Aggregations that are defined for a partition are processed out of the leaf level partition data by aggregating it to a higher granularity
○ Correct User Hierarchies are important for aggregation and they improve the performance of the system.

Types of Aggregation Usage

• Default

○ Here SSAS engine itself take decision of whether to include the attribute for aggregation or not based on multiple condition or situation present at that time.

• Full

○ Include in every aggregation (or lower level attributes are added which can roll up to the desired level attribute).
○ Used only for most commonly used attribute.

• None

○ Exclude in every aggregation.
○ Used for infrequently used attributes.

• Unrestricted

○ No constraint.
○ Rule of thumb is there should not be more than 5-10 attribute per dimension in unrestricted more than this cube might take long time to come up with aggregation design.

Some Important Tips

• Rule of thumb is to store 5% to 10% of aggregation as full store.
• Another Standard rule which SSAS follow for optimization is to do not store the calculation levels which have more then 1/3 size of total rows in facts.

So Aggregation is good option for Cube optimization. Although what I feel is Aggregation are little bit overrated (No offence to any Aggregation Lover). The reason I feel so because success of aggregation depends on if you are aware about the combination of attribute which you are going to hit most using your report or query.
For Example let's assume we have 2 tables with 4 and 3 attributes respectively.

Product>

  Product_ID
  Product_Name
  Product_Color
  Product_Catrgory

Customer>

  Customer_ID
  Customer_Name
  Customer_Location

Now in the given example if I am sure that most of the time my business user want to see what is the Sales of a Particular Product_Category by Customer_Location then I can create a aggregation on them. This will definitely increase the query performance but if I also include any 3rd attribute in query then it will simply not work. Which I think can happen most often.