The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

7.1. Why Analysis Services?

The relational database is a great place to store and manage the data in your DW/BI system. But the relational database doesn't, by itself, have enough intelligence. To build a great DW/BI system, you need an analytic server to deliver excellent query performance and provide analytic capabilities beyond those provided by the SQL language.

7.1.1. Aggregation Management

The single most important thing you can do to improve the query performance of any DW/BI system is to define aggregations. Aggregations are pre-computed and pre-stored summarizations of the detailed data in the fact table. They're nothing mysterious: They're simply summary tables at different grains, for example monthly, or by geographic region, or both. Defining a good set of aggregations is more valuable to query performance than indexing and cheaper than upgrading your hardware.

The first challenge in aggregation management is figuring out which aggregations are most useful. The best way to do this is to monitor how business users are querying the system. You need to consider which aggregations will benefit you the most: Aggregating daily data to monthly reduces data volume by thirty-fold, whereas aggregating monthly to quarterly provides only a three-fold improvement. The problem gets much more complex when you think about aggregating multiple attributes across multiple dimensions. This is a task that software can help you with, as Analysis Services does.

The next challenge is to ...