Chapter 55. To aggregate or not to aggregate—is there really a question?

 

Erin Welker

Aggregations have long been the key to gaining the best query performance from Analysis Services, yet they are commonly misunderstood. What, exactly, are aggregations? What is the best approach for designing aggregations? What tools are available to assist you with this task? What changes have occurred in the various releases of Analysis Services that affect your approach to aggregation design?

I hope to answer all of these questions and more in this chapter. Read on.

What are aggregations?

You can think of aggregations as precalculated answers to OLAP queries. Let’s take the scenario of a query on a cube with three dimensions—Product, Customer, and Sales date—with the attributes listed in table 1.

Table 1. Aggregation example

Product

Customer

Sales date

Category*

Country

Fiscal year

Subcategory

State*

Fiscal month*

Product

City

Date

A query might be issued that asks the question, “What were the total sales for nonfiction books (a category of books) in Texas for last month?” If an aggregation has been created on the cube to precalculate summed values of sales by state, month, and product category (indicated by the asterisk (*) next to the aggregated levels in table 1), this query will be extremely fast because all Analysis Services has to do is retrieve the appropriate aggregation from disk or cache.

Designing aggregations

Influencing aggregations

Attribute relationships

Usage-based optimization

High-level approach

Other considerations

Summary

About the author