In this final, 1-hour video of this course focused on SSAS cube design, Chris tackles the goal we all aspire to: performance. Aggregations are one of the easiest ways to improve the performance of your queries, assuming that those queries do a significant amount of work in the Storage Engine. An aggregation is a pre-aggregated copy of data in a fact table, like the output of a SQL GROUP BY query, created by SSAS at processing time. If an aggregation exists, it allows SSAS to avoid having to do a lot of expensive calculations when a query runs.
The more aggregations you build, the slower your processing becomes and the more disk space you need. That said, there is usually a happy medium that can be found where your cube processing is not unduly affected, but your query performance is boosted sufficiently. If you were to pre-aggregate all your data, you would find cube processing times would increase exponentially and the size of the cube would explode.
Aggregations are only useful if you have at least a few million rows of data in a partition, and your queries are slow, because SSAS has to do a lot of work to aggregate data at query time. They are no use if your queries are slow because of MDX calculations.
The aggregations that you want to build for a measure group are specified in an object called an Aggregation Design. When you associate an Aggregation Design with a partition, then the aggregations listed in the Aggregation Design will be built for that partition, as we show in the detailed demo.
Before you design aggregations, you must ensure that your dimensions are not going to change significantly in the near future because of dimension design. Especially, attribute relationships can have a big impact on the effectiveness of aggregations. After that, you will need to set a number of properties which effect how the aggregation wizards behave, such as the various count properties and the AggregationUsage property, and then you will be ready to run the Aggregation Design wizard, which Chris shows in the video, showing you how to build aggregations. This wizard designs aggregations based on the structure of your cube and dimensions, and can be a little hit-and-miss. Once you have deployed into production, you should turn on the Query Log and then run the Usage-Based Optimisation Wizard. If all else fails you can even design aggregations manually in SSDT, although this can be a very difficult and time-consuming task. After having designed aggregations and processed them, you should use a Profiler trace to determine whether your queries are using these new aggregations—watch Chris profile queries to find out if his aggregations are being used.
Bear in mind, that when designing aggregations, and especially when designing aggregations manually, you must be aware of cube design features that can affect how aggregations are used such as many-to-many relationships, semi-additive measures, partitioning strategies, parent-child hierarchies and MDX calculations. Make sure to watch the previous modules in this course, which explain these subjects in great detail.
