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?