Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

2.2. Identifying Potentially Useful Aggregates

The description of an aggregate has been reduced to familiar dimensional terms: fact table grain and dimensional conformance. You've also seen that the number of aggregates can be quite large. The next step is to link potential aggregates to actual business requirements. This process is not hard if you remember one thing: think dimensionally.

This section provides techniques you can use during three different stages of the lifecycle of the base schema:

• During the initial design of the base schema

• Once the schema design has been frozen

• When the base schema is in production

During design of the base schema, you will be provided with numerous clues to potentially valuable aggregates. If your base schema has already been designed, consult with the designers or examine notes and artifacts of the schema design process.

The next place to look is the base schema design itself. More specifically, you will scrutinize the dimensional conformance bus. Looking at fact tables that will be used together in "drill-across" reports will suggest aggregates that are quite likely to be among the most valuable.

Last, once your base schema is in production, you can consult a large number of new artifacts.

Wherever you look to find aggregates, remember: Think dimensionally.

2.2.1. Drawing on Initial Design

One of the keys to identifying potential aggregates is being able to think like an end user. If you think about the business the way an end user does, ...