Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

8.2. Aggregating Dimensions

Advanced techniques for dimension tables may also affect the design of dimensional aggregates. Time-stamped dimensions track slow changes within a dimension regardless of the presence of facts. Bridge tables resolve situations where a dimension attribute can take on multiple values with respect to a fact. Core and custom schemas cope with dimensions where a subset of attributes will vary depending on the specific item described. In each situation, the design possibilities for dimensional aggregates are affected.

8.2.1. Transaction Dimensions

Transaction dimensions track slow changes within a dimension regardless of the presence of facts through timestamps. Summarizing a transaction dimension without eliminating the timestamps often fails to reduce the size of the aggregate by a significant factor. Updates to the expiration timestamp render incremental updates to the aggregate prohibitively complex. In most cases, a transaction dimension is best summarized by eliminating the timestamps.

8.2.1.1. Timestamping a Dimension

Many business situations call for the careful tracking of changes to dimensional values over time. You have already seen that a type 2 slowly changing dimension can be used to record changes to an attribute by adding a new row to the dimension table when the attribute value associated with a particular natural key value changes. This technique is useful in that it neatly associates transactions with the correct dimensional attribute. ...