Aggregations

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:

1. Creating the Aggregations using the Aggregation Wizard with only a few general choices to make before an Aggregation Design is automatically generated
   
   
2. Usage Based Optimization. Logging what the users actually query and using the Aggregation Wizard to generate an Aggregation Design taking the log into consideration
   
   
3. 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.