This article shows you how to create a performance layer for your dimensional data warehouse -- a set of cubes and tables designed specifically for performance.
The article covers two types of performance design: aggregate tables and derived tables.
- Aggregate Tables store partially summarized copies of existing data. These tables (or cubes) improve performance for queries that do not require granular detail. The article fully explains aggregates, including summarization of metrics, hierarchies, and implementation with or without aggregate navigators.
- Derived Tables store restructured copies of existing data. They do not necessarily summarize information, but they may. Derived tables deliver performance benefits by streamlining the query and reporting processes. In the article, I show how to supplement an existing schema with merge, pivot, and set operation fact tables (or cubes).