Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

Chapter 6. ETL Part 2: Loading Aggregates

The previous chapter explored the process that loads the base schema and looked at how the introduction of aggregates would affect that process. This chapter examines the specific tasks required to build the aggregate tables themselves.

Aggregate tables are processed most efficiently when their source is the base schema. This provides a simple mechanism for changed data identification, eliminates redundant processing, and helps ensure conformance. Unless the aggregate is to be updated in real time, there is no reason to reach back to the transaction systems to load them.

As with the base schema, aggregate dimension tables are loaded prior to aggregate fact tables. Each of these processes is explored in turn. Both bear marked similarities to the corresponding processes that load the base schema. In the case of aggregate dimensions, complications will arise if the aggregate contains a type 1 attribute that is not dependent on another of its attributes. In the case of aggregate facts, complications arise when time is summarized.

Instead of loading aggregates incrementally, they may be dropped and rebuilt during each load. While this requires handling much larger volumes of data, the actual processes are greatly simplified, and they avoid some of the potential pitfalls that threaten incremental loads.

The process that loads a pre-joined aggregate looks much different from those that load the tables in an aggregate star. A drop and rebuild ...