AS2000 – Partition Aggregation Utility

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 jesperzz@hotmail.com with the subject "Partition Aggregation Utility" and I can email it over.
 
 
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s