Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

9.4. When Rollups Are Deployed Before Detail

In most situations, detail data is made available before any summarizations are put in place. As a best practice for fact tables, this ensures that transactions are captured at the lowest possible grain. As you saw in Chapters 5 and 6, this also allows for the most efficient load process. Base tables are loaded from operational sources, while aggregates are loaded from the base tables.

In the case of dimension tables, what is considered a summary table may also serve as a base schema table. In Chapter 2, you saw an example where Brand and Month dimension tables were part of the sales forecast star schema, a base star. These dimensions were conformed rollups of Day and Product dimensions, respectively, which are referenced by other base schema tables such as order facts.

Occasionally, the incremental development of data marts leads to an unusual situation in which the rollup table is needed before the base dimension. If sales forecast data is to be implemented before orders, for example, you will need Month and Brand before you need Day and Product. Similarly, financial data marts frequently call for a corporate_organization dimension that will be needed in other subject areas at a more detailed level. The same is often true of account dimensions.

If a conformance bus has been developed for the data warehouse, the implementation team will know in advance when a dimension table being deployed summarizes a detail table needed in the future. ...