Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

8.3. Other Schema Types

The aggregation techniques presented in previous chapters have focused primarily on star schema designs. Most of these techniques can also be applied to snowflake schemas as well as schema designs in third normal form. The key to understanding the proper application of aggregation techniques is to think dimensionally.

8.3.1. Snowflakes and Aggregates

The snowflake schema is a dimensional model in which dimension tables are normalized. This approach dramatically simplifies the process of building and maintaining aggregates, eliminating the need to build most aggregate dimensions and simplifying the process by which aggregate fact tables are loaded.

8.3.1.1. The Snowflake Schema

A snowflake schema is a dimensional model of a business process. Like the star schema, it describes the process in terms of facts and dimensions. Facts are stored in a central fact table, along with foreign keys that reference dimensions. It is in the configuration of the dimensions that a snowflake differs from a star. In a snowflake, the dimension tables are normalized.

Chapter 1 examined a snowflaked version of the Orders schema. The snowflake in Figure 1.3 made explicit several hierarchies within the dimensions. In the Customer dimension, for example, attributes that relate the industry of a customer have been moved to a separate table and replaced by a foreign key. In other dimensions, this normalization has resulted in more tables. Product, for example, is normalized to include ...