Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Optimize a SQL Server Analysis Services Measure Group Partition for Performance


By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | > Analysis Services Measure Groups

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

In SQL Server Analysis Services(SSAS), each measure group by default has at least one partition. When a cube is created, this partition would not have any aggregation schemes defined, so these partitions are not optimized for performance. As you know, aggregations are pre-calculated sets of data which improve query response time and calculations during query evaluation. In this tip we wil learn how to design aggregations for a partition and optimize it for performance.

Solution

The Aggregation Design Wizard in SSAS is the easiest way to design an aggregation for a partition. For the purpose of this tip, we would use the project that we have developed using the SSAS Tutorial. From this tutorial, we already have a SSAS Project, a Cube and two measure groups. Based on these objects, follow the steps below to create an aggregation design.

Step 1:Open the SSAS tutorial project in BIDS, open the cube and aggregation tab. Select any measure group and you will see that an aggregation design has not been applied to the partition. Right-click on the "Unassigned aggregation design" partition and you would find the "Design Aggregations" option listed in the right-click menu. Selecting this option will invoke the aggregation design wizard for the selected partition.

using the aggregation design wizard in ssas

Step 2: On the first step of the wizard, you will find options to review the aggregation usage. Here all the cube dimensions will be listed along with all of the attributes. You can choose whether you want to design full aggregations, no aggregations, default or unrestricted mode. You can learn more about these modes here.

open the ssas tutorial project in bids

Step 3:On the next page of the wizard, you have the option to specify the number of rows in the partition, as well as member counts for each attribute. An easier way to get all of these counts is by clicking on the count button which will retrieve all the counts for you.

aggregation design wizard

Step 4:On the next page of the wizard, you have the option to specify the volume of aggregations that you intend to create. There are three different options, and the fourth one is not a design aggregation at all. The other three options are to design aggregations until the storage space required to design the aggregation reaches a specified capacity, until the performance gain in the query optimization reaches a specified percentage or until you permit the wizard to continue. At that point, you click the Stop button to stop the wizard from creating further aggregations.

specify the volume of aggregations

Step 5:On the next page of the wizard, you have the option of either "Deploy and process now" or "Save the aggregations but do not process them". At this point, the wizard has just created the plan to calculate the aggregations and store it, but the exercise has not been carried out. The same plan has to be executed now and aggregations have to be processed and deployed. We have chosen the option in Step 4 to create aggregation till performance reaches 30%, so we have named it accordingly.

deploy and process

Step 6:After processing and deploying the aggregation, you would find that the aggregation has now been applied to the partition. You can always change the aggregation design or further modify it.

the aggregation has now been applied to the partition
Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, February 24, 2015 - 11:43:46 PM - Shoaib Back To Top

Very nice article Siddharth. But i have confusion related to SSAS cube. In fact i am new to SSAS just want to explore myself in this technology. My concern is

- What i know, SSAS cube pre aggregates data and stores to its specialized file system (MOLAP storage mode)

- If there is no aggregation design associated with any partition of measure group means there is no pre aggregation available in the data?

- Means data in specialized file system is just the copy of data RDMS or any data source? (with respect to given data grain)

- When we explore or browse data with respect to dimension(s) / Hirarchies and facts, data from specialized files aggregates at run time? (if no aggregation design is associated)

- The purpose of these aggregation designs are to pre aggregate data with respect to all dimension/ hirarchies?

- Means if there is no aggregation design associated with any partition, no pre aggregated data available?


Thanks in advance


Regards,

Shoaib Anwar


Wednesday, August 14, 2013 - 1:16:55 AM - Varun Gulati Back To Top

Thanks for this article, pretty straight forward on aggregations.


Sunday, February 24, 2013 - 1:42:51 PM - Cyrille Back To Top

Nice and straight forward post, Siddharth. Thank you very much indeed.


Learn more about SQL Server tools