SCD type 2

Tools & Utilities
Enthusiast

SCD type 2

Hi All,

How do we implement SCD type 2 in teradata.

Thanks,
kiran
5 REPLIES
Teradata Employee

Re: SCD type 2

Type 2 method tracks historical data by creating multiple records. It writes a record with the new attribute information and preserves a record of the old dimensional data.

Example: Employee table with following structure

Employee_ID - Employee identification
Employee_Name - Name of Employee
Employee_GroupID - Group to Which Employee Belongs
Employee_typeID - Contractor/Permanent/Temporary
Employee_Country - Country
Effective_Start_dt - Record Start date when record was created
Rec_End_dt - Effective End Date when record get expired also for existing record it could be NULL or '2999-12-31'

Existing Record

Employee_ID|Employee_Name|Employee_GroupID|Employee_typeID|Employee_Country|Effective_Start_dt|Effective_End_dt
2025|Anand Agarwal|ADC-India|Contractor|India|2004-Aug-09|2999-12-31

Employee status changes from Contractor to Permanent new row will be inserted and old record will be updated, record would look like

Employee_ID|Employee_Name|Employee_GroupID|Employee_typeID|Employee_Country|Effective_Start_dt|Effective_End_dt
2025|Anand Agarwal|ADC-India|Contractor|India|2004-08-09|2009-08-09

2025|Anand Agarwal|ADC-India|Permanent|India|2009-08-09|2999-12-31

Hope this help.

Thanks
Anand Agarwal
Consultant
ADC India
Fan

Re: SCD type 2

I read a very good article on SCD types with good examples http://www.einfobuzz.com/2010/04/slowly-changing-dimension-scd-types.html
WAQ
Enthusiast

Re: SCD type 2

In addition to what anandagarwal told, we can also handle SCD type 2 using two other methods:
1) using sarrogate key
2) using version number
gkk
Enthusiast

Re: SCD type 2

Hai anand,
You explained SCD Type 2 method with example very well . but how to implement the same in Mload script ? already i checked this solution given in the below link . http://forums.teradata.com/forum/tools/scd-type-2-in-mload#comment-14717

Please provide me the working Mload script . Thanks in Advance.
Enthusiast

Re: SCD type 2

Hi Folks,

Refer to the below link for sample script to implement SCD type II  in teradata using Multiload

http://forums.teradata.com/forum/general/implementing-scd-typeii-using-mload