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 ?

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)

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

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.

Thanks for all your help. All the responses gave me insights and helped me fix my design.

Thanks
RK