SCD2 Type Change Question

View previous topic View next topic Go down

SCD2 Type Change Question

Post  vermarajiv on Mon Dec 14, 2009 6:18 pm

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 ?

vermarajiv

Posts : 7
Join date : 2009-12-14

View user profile

Back to top Go down

Re: SCD2 Type Change Question

Post  ngalemmo on Tue Dec 15, 2009 3:08 am

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)
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD2 Type Change Question

Post  gunjan_shah80 on Tue Dec 15, 2009 5:00 am

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

gunjan_shah80

Posts : 1
Join date : 2009-12-11

View user profile

Back to top Go down

Re: SCD2 Type Change Question

Post  VHF on Wed Dec 16, 2009 12:43 pm

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.


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

View user profile

Back to top Go down

Re: SCD2 Type Change Question

Post  vermarajiv on Thu Dec 17, 2009 2:45 pm

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

Thanks
RK

vermarajiv

Posts : 7
Join date : 2009-12-14

View user profile

Back to top Go down

Re: SCD2 Type Change Question

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum