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')

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?