Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

1.4. Summary

This chapter has laid the foundation for the chapters to come, reviewing the basics of star schema design, introducing the aggregate table and aggregate navigator, defining some standard vocabulary, and establishing some guiding principles for invisible aggregates.

• While operational systems focus on process execution, data warehouse systems focus on process evaluation. These contrasting purposes lead to distinct operational profiles, which in turn suggest different principles to guide schema design.

• The principles of dimensional modeling govern the development of warehouse systems. Process evaluation is enabled by identifying the facts that measure a business process and the dimensions that give them context. These attributes are grouped into tables that form a star schema design.

• Dimension tables contain sets of dimensional attributes. They drive access to the facts, constrain queries, and serve as row headers on reports. The use of a surrogate key permits the dimension table to track history, regardless of how changes are handled in operational systems.

• Facts are placed in fact tables, along with foreign key references to the appropriate dimension tables. The grain of a fact table identifies the level of detail represented by each row. It is set at the lowest level possible, as determined by available data.

• Although the specific questions asked by end users are unpredictable and change over time, queries follow a standard pattern. Questions that cross subject areas ...