SSAS Interview question: What are aggregates? What is the purpose of defining an aggregation design in Analysis Services?

Aggregates are summarized values, each of which corresponds to a combination of an attribute from each dimension and a measure group. An aggregate in SSAS is the differentiating factor between OLAP and OLTP, and is the fundamental principle of SSAS/OLAP, which offers blazing fast performance.

In theory, many people believe that SSAS stores aggregated values for every combination of each attribute from each dimension and in each measure group. However, in reality, SSAS stores only a part of all combinations and not all the possible combinations. In some scenarios it might be helpful to create certain percentage of aggregations every time the cube is processed, without leaving the decision to SSAS. This is achieved by defining aggregation design.

The Aggregation Design Wizard is used to design aggregations in SSAS and it provides the following options as part of the aggregation design process:

  • Design aggregations until estimated storage reaches “X” MB.
  • Design aggregations until performance gain reaches “X” percentage.
  • Design aggregations until the person designing the aggregations, clicks the “Stop” button.
  • Do not design aggregations at all (0% aggregation).

Until next time,

                           keep learning, keep searching and keep succeeding…

Powered by CodeReview – Let’s make it Better!