Aggregations, or pre-summarized data, are stored in the cube. If an end user were to ask for the Revenue of a certain customer that would potentially have to be summarized on the fly from thousands of transactions. This process can be speeded up by pre-summarizing measures per Customer.
In this way you can pre-summarize measures for particular Dimensions or even combinations of dimensions. Maybe you often query for Revenue of Products per Month. In that case a combined aggregation of the 2 dimensions Product and Period (on the Month-level) could be relevant.
When you create Aggregations you need to be aware that they can take up a lot of space.
As a consequence, it is not a good idea to pre-summarize on every Dimension and combination of Dimensions – that would generate an enormous amount of data.
As a rule of thumb the aggregations should not take up more than 1/3 of the size of the original OLAP database.
Working with Aggregations in Analysis Services we have 3 possible methods:
- Creating the Aggregations using the Aggregation Wizard with only a few general choices to make before an Aggregation Design is automatically generated
- Usage Based Optimization. Logging what the users actually query and using the Aggregation Wizard to generate an Aggregation Design taking the log into consideration
- Working with the Advanced Aggregations User Interface where you can set up Aggregations in a fully detailed view – either by editing an existing Aggregation Design or by creating a new one from Scratch
Aggregation Designs are assigned to a certain Measure Group. If Partitions are used, you can even assign an Aggregation Design to a Partition within a Measure Group.
Please sign in to leave a comment.