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

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]

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”

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

Then save and do a full process

Re-run the previous query and watch profiler

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.