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].&
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.
A few days back I wanted to optimize a SSAS multidimensional cube. I was testing different queries to see if any of them used my aggregations. As I understand SSAS multidimensional provides results on different ways.
2. From aggregations
3. Build the result from storage engine / formula engine
Number 1 is the fastest way and number 3 is the slowest. So it’s quite important for query performance that you have well considered aggregations. Please note that aggregations adds time to cube processing. When I saw that my queries did not hit any aggregations I started to Google around. And when reading some forum posts I started to suspect that my aggregations was unprocessed. I was running a query similar to this to check the size of my aggregations
SELECT * FROM SystemRestrictSchema($system.discover_partition_stat ,
DATABASE_NAME = 'Adventure Works' ,
CUBE_NAME = 'Adventure Works' ,
MEASURE_GROUP_NAME = 'Internet Sales' ,
PARTITION_NAME = 'Internet_Sales_2013')
This result looks to be okay. But the result from my cube was that almost every size was set to 0. How can I reproduce and identify this issue? I did read the documentation on processing options and figured out that “all flexible relationships we be dropped when performing a process update on a dimension”.
To figure out what was going on I tried to change a value in a dimension with a flexible relations and then run a process update on the dimension. Then followed by a full process of my test partition.
Now you can see that most of the size is set to 0. How can I get them back now? Process partion / measure group / cube with “Process index”. And the your aggregations should be fine. If you run “Process default” on your dimension or full process on the cube the aggregations should be fine.
I will certainly check my processing routines after this 🙂 Does anyone have any comments on this?