SCD - Creating a Type 2 Dimension using Static Lookup

SCD - Creating a Type 2 Dimension using Static Lookup

The Slowly Changing Dimension Type 2 is used to maintain complete history in the target. The source rows based on user-defined comparisons and inserts both new and changed (as a new entry) dimensions into the target.

Changes are tracked in the target table by maintaining an effective date range for each version of each dimension in the target. In the Type 2 Dimension/Effective Date Range target, the current version of a dimension has a begin date with no corresponding end date.

In the below scenario trying the maintain the history for Employee details in case ENAME, JOB, MGR, SAL, COMM and DEPTNO get change. So that complete history of changes can be maintained using SCD type 2.

Below is the final mapping snaps using Static Lookup:

Please connect to the Repository and open Folder where you want to create mapping and workflow.

1. Connect and Open the folder if not already opened.

2. Select Tools --> Mapping Designer

3. Select Mappings --> Create --> Entry the mapping name you want to create. Then click on "OK".

4. Drag the Source and Target definitions into workspace if they are already exist. If not click here to know how to create or import Table definitions.

Note: Take 2 instance of target one for insert new & change records as insert and another for update the history record.

5. Add a Lookup transformation on target table from menu Select 'Transformation' from Menu --> Create

It will pop up below screen, entry the name for the lookup transformation, click on Create and Done

You can create lookup from icon as show below select lookup icon highlighted

This is popup a below screen select the location of the lookup table as Target as we need to maintain history for the target table and select the lookup table you want from the tables list then click on OK.

This will create lookup in workspace as below

6. Drag/map the ports all/required to lookup transformation from source qualifier

7. Edit the lookup transformation go to --> Condition tab to defined lookup condition and then click on OK

Note: Lookup condition can be one or more ports

8. Drag/map the ports the expression transformation to check and validate the incoming records are new/changed one.

Drag ports from lookup to expression transformation which you required for check as below

Note: In expression uncheck output port which are not for out

9. Edit the expression transformation goto --> Port tab to create two ports (variable & output port)

In variable port validate the incoming records are new or modified as if the lookup pm key is null then is new records because there is no match record in target so it will get null for lookup pm key then it will return value as “0”, in case lookup pm key is not null then we need to check source columns with lookup columns which are defined to identify the data modification then it will return value as “1” and in case there is no change in data then it will return value as “3” as below.

Then Click on Validate (in case no error) then click OK

Note: This return values are used as flag to route the records for insert, update and update as insert for modified records

10. Add a Router transformation to route flagged records for insert/update history table

Edit the router transformation and goto --> Group tab to create 2 groups one for insert and another for update

In Insert Group use flag as “0” and “1” where 0 is for complete new records & 1 is for modified records as new insert in history table.

Then Click on OK

In Update Group use flag as “1” to table only modified records which is used to close existing record in the target table as history record

11. Add a sequence generator & expression where the sequence is used to generate sequence values while inserting new/modified records to the target table and expression is used to get “SESSSTARTTIME” which is to known when the records is inserted in history table

Drag port from Insert Group of Router transformation

Sequence: Edit go to Properties table to defined start value as “0”, increment by “1”, End Value let it default only (otherwise you can defined whatever you want)

Then Click OK

Note: Do not check cycle or reset as this is used for history table

Expression: Edit go to --> Ports tab

Add an output port for Start_Date as “SESSSTARTTIME”

Then Click OK

12. Add a Update Strategy next to expression used for insert flow as below

Edit and go to --> Properties tab under Update Strategy Expression define “0” which indicate insert (dd_insert)

Then Click OK

Then map related column with target instance and the Port created for “SESSSTARTTIME” under previous expression map with start_date column of the insert target instance

13. Add an expression next to router transformation for update flow, drag lookup pm key from update group of router transformation as show below