Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

2.1. What Is a Potential Aggregate?

To identify potential aggregates, distinguish between them, and choose which ones to add to the data warehouse, it is necessary to have a way to describe each candidate. The easiest way to do this is to describe them in terms of their dimensional grain. If a potential aggregate schema partially summarizes a dimension table, the grain statement should indicate the base dimension table to which it conforms. Grain can be used to describe pre-joined aggregates as well.

2.1.1. Aggregate Fact Tables: A Question of Grain

As you saw in Chapter 1, an aggregate schema is a special type of star schema. Like a base schema, the aggregate schema comprises a fact table and several dimension tables. The fact table of the aggregate schema is an aggregate fact table. Like a base fact table, it is composed of facts and foreign keys. Each of the dimension tables of the aggregate schema is a base dimension table or a rollup dimension. Like any other dimension table, these are composed of dimensional attributes and warehouse keys.

The only real difference between a base star and an aggregate star is one of grain. Consider the orders example from Chapter 1. The grain of the base schema from Figure 1.2 is:

• Order lines by day, customer, salesperson, product, order, and order line

The grain of the aggregate schema from Figure 1.8 is:

• Orders by day, salesperson and product

A potential aggregate, then, can be identified using a grain statement.

Some other potential aggregates ...