MSDN Documentation

Developing with Aggregations in SQL Server Analysis Services

Aggregations are pre-calculated summaries of data that significantly improve query performance in SQL Server Analysis Services (SSAS). By storing aggregated data, SSAS can retrieve answers to complex analytical questions much faster than if it had to calculate them on the fly from the detailed transaction data.

Understanding Aggregations

In SSAS, aggregations are defined in relation to measures and dimensions. They represent different levels of granularity for your data. For example, you might have detailed sales data, but you also want to quickly see total sales by year, by product category, or by region. Aggregations allow you to pre-calculate these summaries.

Types of Aggregations

• Basic Aggregations: Simple summations, counts, averages, etc., at various dimension hierarchies.
• Marts: Pre-defined aggregation designs that can be applied to your cube.
• Aggregation Warehouses: A separate database that can be used to store and manage complex aggregation designs.

Benefits of Using Aggregations

• Performance Improvement: The primary benefit is dramatically faster query response times.
• Reduced Processing Load: Reduces the computational burden on the server at query time.
• Scalability: Enables cubes to handle larger datasets and more concurrent users.

Designing and Implementing Aggregations

The process of designing aggregations involves analyzing query patterns and identifying the most frequently accessed data at different levels of detail. SSAS provides tools and wizards to assist in this process.

Key Steps:

1. Analyze Usage: Understand how users query your cube. Tools like the Aggregation Usage Tracking feature in SSAS can help identify popular queries and aggregation combinations.
2. Design Aggregations: Use the Aggregation Design Wizard in SQL Server Data Tools (SSDT) to create aggregation designs. The wizard can suggest aggregations based on usage data or allow you to manually define them.
3. Calculate Aggregations: After designing aggregations, you need to process the cube. During processing, SSAS builds the aggregation tables based on your design.
4. Review and Optimize: Monitor query performance after implementing aggregations and refine your aggregation design as needed.

Aggregation Wizard

The Aggregation Design Wizard is a powerful tool that guides you through the process of creating aggregation designs. It offers several design modes:

• Automatic: The wizard analyzes usage data and automatically generates an aggregation design.
• Recommended: The wizard suggests aggregation designs based on best practices.
• Custom: You manually define the aggregations you want to create.

Aggregation Strategies

Choosing the right aggregation strategy is crucial. Over-aggregating can lead to excessive storage space and processing time, while under-aggregating will not yield the desired performance benefits.

Trade-offs:

• Storage Space: Aggregations consume disk space. More aggregations mean more storage is required.
• Processing Time: Calculating and maintaining aggregations adds to the cube processing time.
• Performance Gain: The goal is to find a balance that provides significant performance improvements without excessive overhead.

Advanced Concepts

• Inline Aggregations: Aggregations that are processed and stored directly within the cube's partitions.
• Linked Aggregations: Aggregations that are defined once and can be referenced by multiple cubes.
• Aggregation Manager: A tool within SSAS for managing aggregation designs.

Example: MDX Query with Aggregations

When a query is executed, SSAS's query engine determines if it can satisfy the query using pre-calculated aggregations. For instance, a query asking for total sales by year might directly hit an aggregation table, bypassing the need to scan millions of detailed records.

SELECT
    [Measures].[Internet Sales Amount] ON COLUMNS,
    [Date].[Calendar Year].MEMBERS ON ROWS
FROM
    [Adventure Works Cube]
WHERE
    ([Product].[Category].&[Bikes])