Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

Chapter 3. Designing Aggregates

Once you have chosen dimensional aggregates, they must be designed and documented. This is the point of greatest risk for aggregate implementation. If not designed properly, dimensional aggregates can violate the guiding principles laid out in Chapter 1, rendering the schema very difficult to use. Worse, design errors can render aggregates capable of producing incorrect results. This chapter lays out guiding principles for avoiding these pitfalls.

Before diving into the design of the aggregate schema, this chapter examines some key elements of the base schema design. Part of any good schema design, these elements must be in place prior to the development of a successful group of aggregates.

Next, the chapter provides a set of principles for aggregate design. Following these principles ensures that dimensional aggregates are easy to use, and that they will always return correct results. We will also look at the problems introduced when these principles are not followed.

Unlike the base schema, dimensional aggregates are likely to change over time. As usage patterns change, aggregates will be added and removed as appropriate. This makes documentation of aggregate implementation extremely important. The last section of this chapter enumerates elements of the aggregate design that must be included in schema documentation.