Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

3.4. Summary

Aggregates can offer tremendous performance advantages, but they must be designed properly. An improper approach to aggregate design can destroy the usability and accuracy of aggregates, and even of the base schema itself.

Good aggregate design begins with the base schema. Grain should be defined explicitly for each and every fact table; the conformance bus must be documented; and rollup dimensions must be treated carefully. Once the base schema design is in order, aggregate design begins.

The process of designing the aggregates is much like the process of designing the base schema. It requires definition of grain, facts, and dimensions. The difference is that these aggregate tables will be based on the base schema. This means the design is governed by the same rules of conformance that govern rollup dimensions in a base schema. This chapter looked at the specific principles that apply to the design of the dimensional aggregates, and then enumerated design elements that should be explicitly documented.

The next chapter studies how these aggregates will be used. Here, the power of the aggregates is showcased in the context of various technologies that can leverage them.