SQL Server Analysis Services
Does the aggregation wizard have issues with a large number of dimensions Adding one more dimension to my cube (the dimension added only has 64 members, Key and All levels only, both with aggregationusage set to Unrestricted) causes the aggregation wizard to stop designing at 14 aggregations instead of the original 155 before adding the dimension.
If you remove a dimension, not the one you just added, does the pattern change significantly
B.
The design wizard varies greatly without making changes at all. For instance, if I click start and it stops at 18%, I can click reset and then have it go up to 24%. This is all in the same session of using the wizard.
As to changing dimensions, given this behavior it is difficult to tell.
That's weird. The wizard is complex but I thought it was deterministic so that should record counts and cube structure be the same, two runs would produce the same results.
I'm not aware of specific issues with the wizard that would cause this problem that were fixed with the services packs but you should make sure you are on SP2.
B.
So as we do more and more testing, we continue to see odd results from the aggregation design wizard. The biggest problem is inconsistency. Has anyone else experienced an instance such as this Meaning, in the same session of the design wizard, after resetting the aggregations, you get completely different results.
It would seem that the aggregations go away around 28 dimensions, though I'm guessing the number of the dimenions has less to do with it and the number of attributes (which may exceed 100) could have more to do with it.
Further testing to come over the next two days. Any ideas to go on while testing
I vaguely recall being told that the algorithm used by the wizard was not deterministic, and used some clever goal-seeking logic to determine the optimum aggregation design.
Overall, though, I wouldn't worry too much about what the Aggregation Design wizard produces - you should just use it as a starting point. It sounds like you need to set AggregationUsage to None on attributes which aren't used much, and use Default and Unrestricted very carefully (I assume you've read the AS2005 Performance Guide on this topic If not, you can find it here: http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx; the paper on resolving MDX query bottlenecks is also useful: http://www.microsoft.com/downloads/details.aspx familyid=975c5bb2-8207-4b4e-be7c-06ac86e24c13&displaylang=en) and get a good basic set of aggregations. What you then need to do is work out whether these aggregations are in fact useful and whether you need to build any more to improve the performance of your queries. When or if you need to design aggregations manually I can recommend installing BIDS Helper (http://www.codeplex.com/bidshelper) which integrates the Aggregation Manager sample app into Visual Studio.
Chris
Chris,
Thanks for the reply. This is essentially the path we decided on this morning. Basically we are going to start with a smaller sub-set of dimensions that need to be aggregated and use the wizard to provide a starting point. Then we are going to rest of the dimensions and examine our report requirements to build any other aggregations with BIDS Helper (one of my favorite tools).
That MDX link looks interesting and I will definately add it to my collection of documentation (which of course includes the performance guide you mentioned).