Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

3.2. Design Principles for the Aggregate Schema

After defining the base schema and choosing aggregates, the next major step is to design and document the aggregate schemas. Given that the aggregate schema bears a very close resemblance to the base schema, the aggregate design process will seem relatively simple. A few basic rules will ensure that aggregates adhere to the principles laid out in Chapter 1.

3.2.1. A Separate Star for Each Aggregation

Chapter 1 asserted that aggregates should be stored in separate schemas, either as a star schema or as a pre-joined aggregate. The best way to understand the reasons for this approach is to study the alternative.

3.2.1.1. Single Schema and the Level Field

The alternative to storing aggregate facts in separate tables is to store them in the same fact tables that hold the base data. The fact table serves multiple purposes, offering base data as well as summary data.

Consider the base Sales schema as shown in Figure 3.8. The grain of this schema is sales facts by Day, Product, Salesperson, and Customer. It is similar to the Orders schema from Chapter 1, but does not include a degenerate dimension or the order_type dimension.

Suppose that you decide to build an aggregate that contains order facts by Day, Brand, Salesperson, and Customer, and you want to store it in the same fact table The proposed aggregate summarizes sales facts by Brand, but the sales_facts table contains a product_key. This key cannot be optional; this would destroy ...