Hi Shumsuddeen

The purpose of a type 2 record is so that we can record changes in attribute values for a given entity over time. A simple Example is.

In June while living in Canada (Record 1) John buys 1 bike from an online store, in July he moves to Mexico (Record 10) where he buys another bike from the online store.

When we record the sales in a fact table we want the first sales to be linked with record 1 and the second sales to be linked to record 10 this way we can tell where the customer lived when we sold them the bike.

If you update the first sale to link to record 10 then when we report it would look like we sold and shipped two bikes to a customer in Mexico which is not accurate.

For this reason you do not want update past facts but only new facts as they arrive to maintain this historical view. If you do not want this historical view then we use a Type 1 dimension which simply overwrites the dimension with any change.

• Marked as answer by Shamsuddeen 1 hour 34 minutes ago

Hello,

I have a query regarding SCD type 2 implementation.

while we load dimension, for historical attribute we insert a new record with new surrogate key, and this will become the live or current record. for example employee abc is surrogate key was 1 for the first load and fact table or other child dimensions are having references to this id 1. Now for incremental load abc surrogate key got changed to say 10 (because of some change in the historical attribute), here my doubt is that still other child dimensions (snow flake) and fact table is still having references to the old id that is 1. 

So we should make the first loaded record that is id=1 is current always right with the updated data.

can anybody explain this?

Thanks!

Shamsuddeen

In dataware housing surrogate keys are just foreign keys to the fact table. If you are implementing type 2 dim, you expire record and surrogate key based on business key changes.

Yes, it is true that old records in fact table point to expired dim surrogate key whereas the new/active fact records are pointing to new dim surrogate keys. But still the business key(s) remain the same. And this helps us (along with valid from and to dates plus active flag) in analyzing the historical trends in the data.

http://www.databasejournal.com/features/mssql/managing-slowly-changing-dimension-with-merge-statement-in-sql-server.html

Thanks all for your reply