Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

6.3. Loading Aggregate Fact Tables

Aggregate fact tables are processed much like their base schema analogs. Because they are sourced from the base schema, several steps in the process are streamlined. A single source query will select the necessary source data, filter out new records only, and aggregate the facts. Lookups are necessary only for surrogate keys that link to aggregate dimensions.

The only complication faced will be processing data that is aggregated over time. Special care must be taken to properly accumulate data for the current period.

6.3.1. Requirements for Loading Aggregate Fact Tables

Like an aggregate dimension, the load requirements for an aggregate fact table are the same as those for its counterpart in the base schema. As stated in Chapter 5, a fact table load must:

• Acquire source data

• Calculate the facts

• Aggregate the facts to match the grain of the fact table

• Identify surrogate keys for each of the dimensions

• Load the new fact table records into the warehouse

A set of sub-tasks that meets these requirements is depicted in Figure 6.6. Not surprisingly, it looks very similar to the process of loading a base fact table, presented in Figure 5.5.

The subsequent sections walk you through each sub-task of the load process.

6.3.2. Acquire Data and Assemble Facts

The first steps in loading an aggregate fact table involve acquiring the source data and assembling the necessary facts. In Figure 6.6, these tasks are accomplished in steps 2.2.1 and 2.2.2.

Figure 6.6. ...