Aggregations in Multidimensional Models

Aggregations are pre-calculated summaries of data that significantly improve query performance in SQL Server Analysis Services (SSAS) multidimensional models. By pre-computing common calculations, such as sums, averages, or counts, at various levels of granularity, Analysis Services can retrieve results much faster than if it had to calculate them on the fly from the base fact data.

On This Page

Why Use Aggregations?

The primary goal of aggregations is to speed up query response times. Large fact tables in data warehouses can contain millions or even billions of rows. When users query this data, especially at higher levels of aggregation (e.g., sales by year or quarter), performing these calculations dynamically can be computationally expensive and slow. Aggregations store these summarized results, allowing Analysis Services to serve queries much more efficiently.

Key benefits include:

Aggregation Design

Designing effective aggregations is crucial. It's a balance between improving query performance and managing storage space. Too few aggregations might not yield significant performance gains, while too many can lead to excessive storage requirements and slower cube processing.

Analysis Services provides tools and wizards to assist in aggregation design:

When designing aggregations, consider the following:

Aggregation Types

Aggregations are defined as groups of measures that are pre-calculated across specific levels of dimension hierarchies. An aggregation definition typically specifies:

For example, an aggregation could pre-calculate the SUM of `Sales Amount` and `Quantity Sold` for all combinations of `Year`, `Month`, and `Product Category`.

Managing Aggregations

You can manage aggregations using SQL Server Management Studio (SSMS) or by programmatically using XMLA or AMO (Analysis Management Objects).

Using the Aggregation Manager in SSMS:

  1. Connect to your Analysis Services instance in SSMS.
  2. Navigate to the database, then the cube.
  3. Right-click on the cube and select 'Aggregation Manager'.
  4. You can create a new aggregation design, use the wizard, or edit existing ones.
Note: The Aggregation Wizard uses a "Usage-Based Optimization" approach. It analyzes query logs to identify common query patterns and suggest aggregations that would benefit them most.

Aggregation Storage

Aggregations are stored in a separate table called an aggregation table, which is linked to the fact table. Analysis Services manages this storage internally.

There are different storage modes for aggregations:

When you process or reprocess a cube with aggregations, Analysis Services calculates and stores the aggregated data.

Performance Impact

Properly designed aggregations can yield substantial performance improvements. However, poorly designed aggregations can:

It's essential to monitor cube performance and storage after implementing aggregations and to adjust the aggregation design as query patterns evolve.

Tip: Use the "Aggregation Usage" property on dimensions and measures to control how aggregations are applied. Setting this property to 'None' can prevent aggregations from being used for that element, which can be useful in specific scenarios.

For more detailed information, refer to the official Microsoft documentation on SSAS aggregations.