SSAS multidimensional: Are you sure your aggregations are okay?

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.

    1. From cache
    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')

Aggregations

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.

Aggregations 2

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?

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