Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

Chapter 4. Using Aggregates

For the well-designed portfolio of aggregates to deliver on its promise of substantial performance gains, it must be used. While this may go without saying, the proper use of aggregates introduces a new set of requirements for the data warehouse architecture. Mechanisms must be in place to ensure that the most appropriate table is used to respond to every warehouse query. Errors can result in inefficiencies or inaccurate results.

The best practices explored in this chapter ensure that the power of aggregates is fully leveraged. The chapter begins by looking at the query environment of a dimensional data warehouse that includes aggregates, and studies how it differs from one that does not. Next, these differences will be translated into a set of requirements for the data for an aggregate navigation function.

A wide variety of commercial products offer aggregate navigation capabilities. Each implementation is different; each has its own pros and cons; all are constantly changing. Implementations are classified into three styles, and each style is evaluated against the requirements.

This process will equip you to evaluate the aggregate navigation capabilities of specific commercial products, even as the capabilities of these products evolve. In addition, you will study two of the most commonly implemented technologies: Oracle's materialized views and DB2's materialized query tables. Last, this chapter looks at the implications of working without an aggregate ...