Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

9.3. Derived Tables

The dimensional aggregates on which much of this book is focused are invisible to end users. As anonymous stand-ins for base schema tables, they are chosen when appropriate by the aggregate navigator. Not all dimensional schemas constructed from base schemas fit this profile. A derived schema, as introduced in Chapter 1, transforms the schema or alters its content. Some aggregation may be performed in this process, but the derived schema will be used in a different manner from the base schema.

You have already seen several examples of derived schemas. In Chapter 8, you saw that averaging the semi-additive fact of a snapshot schema resulted in a derived schema. Earlier in this chapter, you saw that the summarization of a dimensionally constrained fact table is a derived schema. In both cases, the new fact table provides different information from the base schema.

Derived tables can be powerful additions to the data warehouse, often used in the development of second level data marts that provide cross-functional analysis. A merged fact table combines information from multiple fact tables at a common grain. A pivoted fact table provides multiple facts in place of a single fact with a fact type attribute (or vice versa). A sliced fact table contains a dimensionally partitioned subset of base fact table data. In each case, the derived schema alters the base schema in design or content; the result will be accessed directly by application SQL.

9.3.1. The Merged Fact ...