Aggregations are the lifeblood of OLAP. Properly set aggregations either make or break the user experience.
Through Analysis Manager there are two options for setting aggregations: Usage-Based Optimization and the Storage Design Wizard. Usage-Based Optimization works well when you have a few weeks or months of query logs to base the aggregations off of. Storage Design Wizard is for setting up aggregations using a percentage based number.
At first I let the Storage Design Wizard take care of the aggregations. The cube went into production and immediately I started receiving complaints from users about queries taking a long time, some of those queries not even completing. It was obvious some of the queries weren’t hitting any aggregations. Since our largest cube goes through 100 million fact rows a day those queries that weren’t hitting aggregations were taking a long time to return.
After consulting with the users, and being a consumer of the data myself, I knew what aggregations were missing.
This is where the Partition Aggregation Utility comes into play. The PAU allows you to custom design your own aggregations by either modifying current aggregations or adding new aggregations.
Partition Aggregation Utility
Setting the dimension to "All" means that that aggregate sums that dimension at the All level – it’s ignored in the aggregation. Don’t want that dimension in the aggregation…set it to "All".
I absolutely love this tool and couldn’t live without it. It’s an absolute must for anyone dealing with large OLAP cubes.
To get the Partition Aggregation Utility download the SQL Server Accelerator for Business Intelligence: http://www.microsoft.com/sql/prodinfo/previousversions/ssabi/download.mspx. The file you want is named ‘PartAggUtil.exe’.
If you can’t figure out how to get it from the BI Accelerator or are just too lazy (nothing wrong with that, the best programmers are also the laziest), shoot me an email at email@example.com with the subject "Partition Aggregation Utility" and I can email it over.