Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

Chapter 8. Advanced Aggregate Design

Previous chapters have covered all the basics of dimensional aggregates, including their selection, design, usage, construction, and how they factor into software development projects. This chapter returns to the topic of design, exploring the impact of advanced dimensional design techniques and alternative schema design approaches.

The advanced dimensional modeling techniques to be explored fall into two broad categories: those that primarily concern facts or fact tables, and those that primarily concern dimensions or dimension tables. Each base schema design technique brings limitations and implications for aggregate design. Advanced fact table techniques explored include snapshot models, accumulating snapshots, and schema designs in which the fact table contains no facts at all. Dimensional techniques explored include transaction dimensions, bridge tables, and solutions for heterogeneous attributes.

The approach to aggregation detailed in this book can be adapted to other types of schema design. The snowflake schema, in particular, is well suited for dimensional aggregation. A fully normalized schema can also benefit from carefully constructed dimensional aggregates, although their identification and construction may prove more challenging.

Previous chapters have described dimensional design concepts in sufficient detail for the uninitiated reader to understand how a base schema works. For some of the advanced design topics covered here, ...