CODEWITHSUNDEEP
data-warehousebusiness-intelligence
Kirill
Kirill

Reputation: 193

Slowly changing Facts

We'he slowly changing dimension in DWH. Type 1, Type 2, Type 3 etc. This allows us to track changes in dimensions tables. But, what is the way to track changes in the fact table?

Upvotes: 2

Views: 1323

Answers (1)

Rich
Rich

Reputation: 2279

There are a number of different ways to track history in a fact table, and a similar range of techniques can be applied as those used in dimensions. As always the technique you choose depends on the types of changes you have coming through and the ways in which users will want to analyse the data.

For example:

  • You might be lucky and not need to track the history of a fact table at all, as with many transactional facts which are just inserts of new rows.
  • You could apply the equivalent of a Type 1 change to a Fact record, updating and replacing the values.
  • You might supplement your design with a periodic snapshot, to see the measures at a point in time or for a defined period.
  • It might be the history of a long running process might be best dealt with in an accumulating snapshot fact, for example an insurance claim which might have various dates (claim date, resolution date) and measures (claim amount, resolution amount) which only transpire over time. In this case you are filling in the fact over time and tracking history that way.
  • You might need to supplement your accumulating snapshot with time stamped row versions, similar to a Type 2 change.
  • You might present simple versions of this complex picture behind the scenes, for most users who don't care about history, with views.

Some of these techniques may benefit from having surrogate keys on the fact tables.

This article gives a few hints into this more complex world of tracking changes in facts:

Kimball Design Tip

Upvotes: 4

Related Questions