Browsing a Cube and Influencing Aggregations
You can browse a cube through the cube browser in Analysis Services or through the one in the Management Studio. I usually use the one in the Management Studio, as it gives your more space to look. In the cube I created in Analysis Services, I added a Budget hierarchy in my Budget dimension. I decided to check the data for the new hierarchy in comparison to the money that was assigned to it in the Budget fact table. And it was problematic…I was opening the Budget hierarchy and in the first 3 levels it reacted pretty quickly, but at level 4 (and I have about 9 levels in the hierarchy) it would practically die and bring my computer down with it! I went into the Partitions tab in the Cube in the SSAS Project. I went into the Aggregation Design Wizard and pretty much went through it all! I gave it a performance gain that reaches 100%, I gave it an estimated storage of 100M, I gave it a chance to run aggregation till I clicked stop. BTW – this is also when I found out that at times, it doesn't really matter that I don't click stop. The aggregation wizard can, at times, stop making aggregations on its own, if it doesn't find it needs any more aggregations. Yes, very smart wizard :) …I also decided to manually click high values in the count the aggregation wizard runs before enabling you to manipulate the aggregations. Even that didn't work.
The only thing that seemed to have the slightest influence was connecting the dimension directly to the fact table (rather than leaving it as a referenced dimension as I did before). That made the cube browser react quickly even at the 4th and 5th level, but I still had 4 more levels to go.
In a moment of desperation, I decided to check how it would look to the user in the Panorama Nova View. And it reacted great there! In all of the levels! Needless to say, I was stunned! It was as if all my efforts from before were in vain. Checking what was happening in the background, I understood the reason for the difference in performance. In the cube browser, the back engine creates lots of sql queries to determine what it needs to show. In the Panorama Viewer on the other hand, there is just a single MDX query being generated for each click you make. And that – makes all the difference!
So my last word of advice is this: when you want to look at your cube – do it in the proper viewer…