DW: SCD (Slowly Changing Dimensions)

After working for years in the DW and BI world I have seen a variety of Slowly Changing Dimensions (SCD).

And this is my perfect solution.
It is basically Type 2 (see source link) but then with some additional information.

• Effective Date: the date-time the row/transaction got entered or when a change occurs
  
• Expiration Date: the date-time when the row was either removed or changed from the OLTP system. The current row/transaction has always 12/31/2999
• Counter: a column that simply counts all the variations of a unique row/transaction
• UID: The first unique row/transaction will be I (Insert), a change will have a U (Update) If then the row/transaction gets removed/deleted from the source system we replace the U with a D (Delete)

Advantages:

• If you want to know how the source system looked like at a certain moment in time; you can do a between statement.
• The current mirror of the source system is where the Expiration Date is 12/31/2999
• The counter shows the number of variations
• The UID tells you what the first record is (note: the counter "1" tells you the same) But if you see a D you know that there is no following record.

Source(s):
http://en.wikipedia.org/wiki/Slowly_Changing_Dimension