Type3 SCD

[#1]

Hi,

I am working on Incremental load. I have come across following kind of source data. There is no 'Modification Time'. The data gets updated without any modification timestamp, how to do incremental load for this kind of Source data.

Appreciate help, if anybody has done this kind of implementation.

Type 3

This method tracks changes using separate columns and preserves limited history. The Type 3 preserves limited history as it is limited to the number of columns designated for storing historical data. The original table structure in Type 1 and Type 2 is the same but Type 3 adds additional columns. In the following example, an additional column has been added to the table to record the supplier's original state - only the previous history is stored.

This record contains a column for the original state and current state—cannot track the changes if the supplier relocates a second time.

One variation of this is to create the field Previous_Supplier_State instead of Original_Supplier_State which would track only the most recent historical change.

[hic]

The document on Slowly Changing Dimensions describes how to solve a Type 2 SCD – that each affiliation (dimension key + interval in time) is stored in its own record.

But you have a Type 3 SCD, where each dimension key only has one record, and this has one field for current affiliation and a second for the previous affiliation. This means that information for the affiliation before the previous one has been lost.

I would solve a Type 3 SCD by converting it to the same structure as a Type 2 (one record per affiliation), and assigning all old records to the previous affiliation (which isn't quite correct, but the best you can do given what data you have). So, you need to load every record twice: once for the current affiliation; once for the old. Hence:

Intervals:

Load

  Supplier_Key,

  CurrentState as State,

  ChangeDate as FromDate,

  Today() as ToDate

  From Type3Table ;

Load

  Supplier_Key,

  OldState as State,

  MakeDate(1900) as FromDate,

  Date(ChangeDate-1) as ToDate

  From Type3Table ;

With this table you can build a Type3 SCD solution using the method for Type2 SCD.

HIC