Design Tip – Analysis Services Aggregations
I was cleaning up a cube design for a customer, primarily working with the current implementation to resolve some performance issues and make some minor alterations to the dimensions, when I ran across an aggravation in designing aggregations for the partitions I implemented ( there were none before, part of the issue).
I had just finished designing my partitions when I started to design the aggregations for my partitions – when the designer returned this obnoxious message:
0 aggregations have been designed. The optimization level is 0% (0 bytes).
I will mention again that this cube was not my design, and when given the opportunity, everything from the dimensional model to the entire cube will be redesigned, however, this message was a nuisance. Fortunately, I kept my cool and figured out what I was doing wrong. The Aggregation Design Wizard will not allow a resulting aggregation set to exceed a certain percentage of the fact table (I hear it is 30%, but I can’t find this documented anywhere).
Here are a few general rules with aggregations that will help avoid this message:
- Make sure the partitions you are trying to assign an aggregation design are evenly distributed. In other words, do they have a similar number of facts/rows in them or are they varied? One aggregation design can work for multiple partitions so long as those partitions are similar or close in overall row counts
- If you have a large number of attributes in your dimensions related to a particular partition set you are designing for, you may consider changing some of the options on the Review Aggregation Usage screen in the wizard. Attributes that are rarely used can potentially be set to None
- On the Specify Object Counts page, check the counts tabulated for each dimension as well as the measure group. On the dimensions, the wizard does not calculate the partition count. This number is the count of that member distinctly appearing inside the specific partition. For example, if your partition broken by year, then the count of dates on your Calendar dimension would be 365, months would be 12 and so on.
In addition to the above, take a look at your individual partitions — are the individual partitions too large? BIDS Helper suggests a 20 million plus row count per partition is probably not partition with enough detail.
As for my specific problem – due to the "funky" (lack of a better word) design of the fact tables, I did not have reasonable splits in both my row counts and related member counts for my initial partition designs as I had initially suspected, and being in a hurry that evening, I did not update the counts inside the partition design screen before moving over to designing aggregations. I simply assumed I had picked a good way of doing it on the first try!
So, a lesson in patience reiterated yet again, and after getting some sleep, I looked at the issue the next morning with some fresh eyes and found my solution.
Hopefully this little explanation will save some time for someone out there – feel free to let me know if any further explanations are necessary, or feel free to share your aggregation design issue!