Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

5.3. Loading the Aggregate Schema

The process of loading the aggregate schema is very similar to the process of loading the base schema. This should come as no surprise; an aggregate star schema is a star schema itself, albeit with a different grain. And pre-joined aggregates share similar properties.

As it turns out, much of the complexity involved in processing the base schema is eliminated during aggregate processing. Examples include multiple-source loads, changed data identification, and the transformation of data for processing one row at a time. Chapter 6 shows how these complexities are eliminated by sourcing the aggregate schema from the base schema.

But before examining the specific processes that load aggregate tables, it is important to consider how aggregate processing fits into the overall load process.

Aggregate loads usually follow the same approach as base schema loads, where a separate program, or process, is developed for each table. The presence of aggregates requires that the load process for the base schema manage the availability of aggregates, taking them off-line during processes that update base tables. This also affects the frequency of aggregate loads. The use of RDBMS features such as materialized views or materialized query tables eliminates the need to design a load process, but availability and load frequency must still be attended to.

Additionally, a choice must be made on the approach to aggregate loads. They may be rebuilt entirely with each ...