Step-4: Pros and Cons of Handling

• Option-1:  Overwrite existing value

+         Simple to implement

+         No tracking of history

• Option-2:  Add a new dimension row

+         Accurate historical reporting

+         Pre-computed aggregates unaffected

+         Dimension table grows over time

• Option-3:  Add a new field

+         Accurate historical reporting to last TWO changes

+         Record keys are unaffected

+         Dimension table size increases

There are number of ways of handling slowly changing dimensions. Some of the methods are simple, but not desirable; but all have their own pros and cons. The simplest possible “solution” is to overwrite history. If the customer was earlier single, and gets married, just change his/here status from single to married. Very simple to implement, but not desirable, as a DWH is about recording historical data, and by virtue of overwriting, the historical data is destroyed. Another option is to add a row when the dimension changes, the obvious benefit is that history is not lost, but over the period of time the dimension table will grow as new rows are added corresponding to the changes in the dimensions. The third, rather desirable approach is to add an additional column, that does increase the table size, but the increase is not non-deterministic. The column records the last two changes, if the dimension changes more than twice, then historical data is lost.

Previous

TOC

Next