In a previous article I detailed designing and implementing Analysis Services partitions as a way to performance tune your cube. Now it is time to take the next step in performance optimization by designing aggregations on our measure groups.
Aggregations are calculated summaries of the cube data. When you setup aggregations Analysis Services computes different possible aggregations or queries that a user may run and stores them in a file. This way before a user even drags over a query in excel for the total sales by region Analysis Services already knows the answer and query time can be drastically faster. With aggregations designed on a measure group Analysis Services knows the answer before you even ask the question. This is the best way to describe aggregations when you are asked what they are.
There are two types of aggregations you can design. A regular aggregation is designed by Analysis Services while designing an aggregation using Usage-Based Optimization (UBO) uses actual user queries to design aggregations. In this article, I will detail the steps to creating a regular aggregation and in a future article show how to create aggregations using UBO.
Aggregation designs can be used multiple times across several partitions in a measure group or you can design separate aggregations for each partition in a measure group. However, aggregations are specific to a measure group so you cannot assign an aggregation design for one measure group to another measure group.
There is a point of diminishing return when it comes to aggregations. Remember these aggregations are stored in a file so you are fighting file size versus number of aggregations it stores. You can reach a point when developing an aggregation design that your file size will continue in increase yet your aggregations do not increase. As soon as you reach that point you will know because the design graph will flat line indicating that your file size continues to grow but your performance optimization does not.
Now that you know what aggregations are and why they are beneficial I’ll detail the steps of creating an aggregation design on a measure group. If you read the previous article on Analysis Services partitions I am going to pick up where I left off on that example. In this example, I will show you how to design a regular aggregation on the Internet Sales measure group in the Adventure Works 2008 sample solution.
· Navigate to the Aggregations tab in the Cube designer (BIDS).
· Right-Click on the measure group you wish to design an aggregation for and select Design Aggregations
· Hit Next on the Welcome screen then select the partitions you wish to add your aggregation design to then hit Next again. Remember you can design an aggregation for just individual partitions or multiple partitions.
· New to SQL Server 2008 is a manual adjustments screen to the aggregations that Analysis Services will create. Keep with the default selection then hit Next. Below is a description of each setting.
The designer applies a default rule based on the type of attribute and dimension.
This setting means every aggregation designed must include this attribute. Avoid this setting when the attribute has many members because then the designer may have to create many more aggregations then necessary. For Example don’t design aggregations all the way down to the Date level. Year, Month and Quarter are better.
No aggregations will include the selected attribute.
No restrictions are place on the selected attribute. The designer will check to see if the attribute will be valuable in the aggregation design.
· The next menu is looking to obtain a row count of the records in your measure group and related dimensions. Hit the Count button to count dimensions and measure groups that don’t already have a count. This will help Analysis Services determine which records to aggregate on. It is less likely to fully aggregate on an attribute that has thousands of records then it is one that only has five records. Hit Next after you hit the Count button.
This is also a great opportunity to manually add to a record count to better represent a production environment when you are working with development data. For example, you may only have a few hundred products in your development database but you want to design this aggregation to be production ready so you can manually change the row count to better represent your production environment.
· Next you will tell Analysis Services how to create the aggregation. Your options are to continue aggregating until the file reaches a certain size, continue aggregating until the performance gain reaches a certain percentage or lastly continue aggregating until you tell Analysis Services to stop. Select the option to aggregate until the performance gain reaches 30% then hit Start. When the aggregation completes a 45.9 KB file was created with 38 aggregations and with a 30 % performance gain. Hit Next when it completes.
· Lastly give the aggregation a name and process the cube. It will assign the aggregation the partitions you defined in the beginning of the wizard.
Now you have completed the development of an aggregation design that will be used on your partition. In the next article we will take a look at another type of aggregation called Usage-Based Optimization (UBO).