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 ...

Get Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.