Getting started with aggregations in SSAS multidimensional

In this post I will describe how to get started with aggregations in SSAS multidimensional. Since this is a rather complicated thing I will start with a really basic sample.

First I will modify my Adventure Works database. I will create a new aggregation design with no aggregations. This can be done by running the aggregation wizard and selecting “No aggregations”. Then save the aggregation design and assign it to the partitions.

It will look like something like this

Aggregations 1

Do a full process on the measure group

Then run a test query against this cube using one of the measures from that measure group

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Subcategory].[Subcategory] ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2007]

Subscube

As you can see here I get a query subcube event. I only get one of these events because this query is really simple. For more complex queries you get loads of them. In a typical production environment you will get loads of them before the aggregations are optimized.

My main goal now is to eliminate this subcube event by designing aggregations.

To do this I open the aggregations. Then chooses “Advanced view”. And then “AggregationDesign EMPTY”

Aggregations 2

Right click in the gray area in the middle and choose “New aggregation”

In the check boxes check the following. Product -> Subcategory and Date -> Calendar Year

Aggregations 3

Then save and do a full process

Re-run the previous query and watch profiler

Aggregations 4

Now you can see that my query did hit the aggregation.

If you choose to run the queries multiple times on each step make sure you clear the cache in between.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s