Slowly changing facts

In IIW, all dimensions are modeled as Slowly Changing Dimension (SCD) Type 2, which means a new row is added to the dimension table whenever the tiniest change occurs in any attribute value.

How best to create versions? What happens to one or more related fact tables when versioning a dimension? Should a new instance of the fact table be created for all new versions of any of its dimensions?

The time dimension (for example, the Calendar date Dimension) in fact entities presents the "truth" at a point in time. When it's a transaction fact entity, the values of the transaction facts are the value of the transaction at that (transaction) time. Likewise, the foreign keys (FKs) point the dimension rows that are "true" at the same (transaction) time. So, in such cases, having attribute values that change in a dimension is not a problem because the dimensions are kept attached as they were at the transaction time.

However, with a slowly changing fact (some snapshots are typical examples), there might be an overhead if the fact does not change but the dimensions do. Indeed, do you want to recreate a new row in the fact table, most probably without any change to the facts themselves, simply because a few (minor) attribute values might change in a couple of the dimensions? Probably not always.

Therefore it is possible to apply version management on such slowly changing fact tables in a less "expensive" way. Two options exist:
  1. You override the dimension's FK value, without recalculating the facts. You use the Calendar date Dimension (part of the fact entity's PK) as the "effective from date" and can reuse the Calendar date Dimension a second time (but now simply as a FK) as the "effective to date".
  2. You use the supernatural key only (that is, the anchor id) instead of the FK to the dimension, which is combined with the Calendar date Dimension as above. In this case, it is always possible to join the dimension version to the fact table (via the anchor id and the calendar dates), for example through a view. The view then presents a star schema that provides the "truth" of both the facts and the dimensions, but without having the risk of an "explosion" of the physical fact table's volume due to dimensions' changes.