SCD2 Type Change Question
4 posters
Page 1 of 1
SCD2 Type Change Question
Guys,
Need your help related to SCD2 type change. I have a table MotorCarrier and we need to keep historical records. The primary key is a column with Identity values (Primary key col name is MCId). There are multiple members belonging to a motorcarrier and are maintained in a separate table called Members. Members dont care about the history of Motorcarrier. For eg MotorCarrier has following records :
Motocarrier
MCId MCName Status StatusChangeDate RecordStatus
1 ABC Active 1/1/2009 Current
2 MM Active 5/1/2009 Current
Member
MemberId MCId MemberName DateJoined
1 2 Mem1 6/1/2009
Now, a new record is created for MotorCarrier MM because its status changed. So we have following records
MCId, MCName, Status, StatusChangeDate RecordStatus
1 ABC Active 1/1/2009 Current
2 MM InActive 5/1/2009 Expired
3 MM Active 12/1/2009 Current
Now the problem is that the Member table still has older Expired record's MCId which is incorrect and should have value 3. One way to deal with this is that you go to all the tables that points to this MC record and update it to current MCId. But there are lots of tables with millions of records that refer to MotorCarrier table. Is there a better way to deal with this situation or a different architecture ?
Need your help related to SCD2 type change. I have a table MotorCarrier and we need to keep historical records. The primary key is a column with Identity values (Primary key col name is MCId). There are multiple members belonging to a motorcarrier and are maintained in a separate table called Members. Members dont care about the history of Motorcarrier. For eg MotorCarrier has following records :
Motocarrier
MCId MCName Status StatusChangeDate RecordStatus
1 ABC Active 1/1/2009 Current
2 MM Active 5/1/2009 Current
Member
MemberId MCId MemberName DateJoined
1 2 Mem1 6/1/2009
Now, a new record is created for MotorCarrier MM because its status changed. So we have following records
MCId, MCName, Status, StatusChangeDate RecordStatus
1 ABC Active 1/1/2009 Current
2 MM InActive 5/1/2009 Expired
3 MM Active 12/1/2009 Current
Now the problem is that the Member table still has older Expired record's MCId which is incorrect and should have value 3. One way to deal with this is that you go to all the tables that points to this MC record and update it to current MCId. But there are lots of tables with millions of records that refer to MotorCarrier table. Is there a better way to deal with this situation or a different architecture ?
vermarajiv- Posts : 7
Join date : 2009-12-14
Re: SCD2 Type Change Question
There are a few issues here... but first, "Members dont care about the history of Motorcarrier.", then why would you care about which row it points to? I assume you mean the member/carrier relationship should reflect the current state of the carrier.
Which leads to the next issue. Member looks like a dimension table with carrier as a snowflake. You are better either maintaining a factless fact table which relates member and carrier OR putting appropriate carrier attributes into the member dimension.
If you do the latter, include a carrier status attribute and update all members for the carrier if the status changes. If you to the former, do a self join on the carrier dimension to locate the current state or implement an additional type 1 version of the dimension and reference that. (This is covered in http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/scd-s-and-fact-tables-t329.htm)
Which leads to the next issue. Member looks like a dimension table with carrier as a snowflake. You are better either maintaining a factless fact table which relates member and carrier OR putting appropriate carrier attributes into the member dimension.
If you do the latter, include a carrier status attribute and update all members for the carrier if the status changes. If you to the former, do a self join on the carrier dimension to locate the current state or implement an additional type 1 version of the dimension and reference that. (This is covered in http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/scd-s-and-fact-tables-t329.htm)
Re: SCD2 Type Change Question
Hi,
I would like to suggest you to go with 'Type 3' instead of 'Type 2' as you don't need to take
care of the historical data.
For Motocarrier dimenstion, you could have the following columns:
MCId MCName Original_Status Current_Status StatusChangeDate Org_RecordStatus Cur_RecordStatus
Where Original_Status contains the status prior to the current status.
The data will look like this:
1 ABC Active Active 1/1/2009 Current Current
2 MM Active Active 5/1/2009 Current Current
If 'MM' gets inactive, the rows will be:
1 ABC Active Active 1/1/2009 Current Current
2 MM Active Inactive 12/1/2009 Current Expired
Further more,
1 ABC Active Active 1/1/2009 Current Current
2 MM Active Active 20/1/2009 Current Current
This way, you dont need to make any change in the queries refering to MCId.
Regards,
Gunjan
I would like to suggest you to go with 'Type 3' instead of 'Type 2' as you don't need to take
care of the historical data.
For Motocarrier dimenstion, you could have the following columns:
MCId MCName Original_Status Current_Status StatusChangeDate Org_RecordStatus Cur_RecordStatus
Where Original_Status contains the status prior to the current status.
The data will look like this:
1 ABC Active Active 1/1/2009 Current Current
2 MM Active Active 5/1/2009 Current Current
If 'MM' gets inactive, the rows will be:
1 ABC Active Active 1/1/2009 Current Current
2 MM Active Inactive 12/1/2009 Current Expired
Further more,
1 ABC Active Active 1/1/2009 Current Current
2 MM Active Active 20/1/2009 Current Current
This way, you dont need to make any change in the queries refering to MCId.
Regards,
Gunjan
gunjan_shah80- Posts : 1
Join date : 2009-12-11
Re: SCD2 Type Change Question
You could add a Durable Surrogate Key (DSK) to your MotorCarrier dimension in addition to the current ID. The DSK maintains the same value across SCD2 changes. Your snowflaked Member dimension would then point to the DSK rather than the ID. Using the original example:
MotorCarrier
MCId MCDSK MCName Status StatusChangeDate RecordStatus
1 1 ABC Active 1/1/2009 Current
2 2 MM Active 5/1/2009 Current
Member
MemberId MCDSK MemberName DateJoined
1 2 Mem1 6/1/2009
Now, a new record is created for MotorCarrier MM because its status changed. So we have following records
MCId, MCDSK, MCName, Status, StatusChangeDate RecordStatus
1 1 ABC Active 1/1/2009 Current
2 2 MM InActive 5/1/2009 Expired
3 2 MM Active 12/1/2009 Current
You would join Member to MotorCarrier ON Member.MCDSK = MotorCarrier.MCDSK WHERE MotorCarrier.RecordStatus = Current. (You could also create a view on the MotorCarrier dimension that only includes Current records and then join to the view.)
Sometimes fact tables will store both the SK and DSK to allow doing either Type 1 or Type 2 queries involving that dimension. You could do the same thing in your snowflaked Member dimension unless you are 100% sure you only need Type 1 for the Member-to-MotorCarrier join.
It is common to use the value of the first surrogate key (SK) for a dimension member as the DSK, but there is no design requriement to do so. Other designs use the natural business key as the DSK, but I don't like that approach for the same reasons that that a business key shouldn't be used as a SK.
MotorCarrier
MCId MCDSK MCName Status StatusChangeDate RecordStatus
1 1 ABC Active 1/1/2009 Current
2 2 MM Active 5/1/2009 Current
Member
MemberId MCDSK MemberName DateJoined
1 2 Mem1 6/1/2009
Now, a new record is created for MotorCarrier MM because its status changed. So we have following records
MCId, MCDSK, MCName, Status, StatusChangeDate RecordStatus
1 1 ABC Active 1/1/2009 Current
2 2 MM InActive 5/1/2009 Expired
3 2 MM Active 12/1/2009 Current
You would join Member to MotorCarrier ON Member.MCDSK = MotorCarrier.MCDSK WHERE MotorCarrier.RecordStatus = Current. (You could also create a view on the MotorCarrier dimension that only includes Current records and then join to the view.)
Sometimes fact tables will store both the SK and DSK to allow doing either Type 1 or Type 2 queries involving that dimension. You could do the same thing in your snowflaked Member dimension unless you are 100% sure you only need Type 1 for the Member-to-MotorCarrier join.
It is common to use the value of the first surrogate key (SK) for a dimension member as the DSK, but there is no design requriement to do so. Other designs use the natural business key as the DSK, but I don't like that approach for the same reasons that that a business key shouldn't be used as a SK.
Last edited by VHF on Wed Dec 16, 2009 12:45 pm; edited 2 times in total (Reason for editing : correction)
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Re: SCD2 Type Change Question
Thanks for all your help. All the responses gave me insights and helped me fix my design.
Thanks
RK
Thanks
RK
vermarajiv- Posts : 7
Join date : 2009-12-14
Similar topics
» SCD2 Product Dim has multiple categories which can change over time
» SCD2 : question of date ...
» Type 2 Change Handling
» basic question, change DIM table
» SCD Type 2 Change Reasons
» SCD2 : question of date ...
» Type 2 Change Handling
» basic question, change DIM table
» SCD Type 2 Change Reasons
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum