SCD Type 3

Type 3 - Adding a new column.

In this type usually only the current and previous value of dimension is kept in the database. The new value is loaded into 'current' column and the old one into 'previous' column.

Generally speaking the history is limited to the number of column created for storing historical data. This is the least commonly needed technique. 

Pre-requisites:

EMPLOYEES table

EMP table with PREV_SALARY as extra column

Incremental Knowledge Module (IKM Oracle Incremental KM)

Approach:

1.    Reverse Engineer both Employees datastore as well as EMP datastore into model

2.    Import IKM Oracle Incremental KM

3.    Create Mapping and Drag and Drop EMP and EMPLOYEES datastores into logical editor

4.    Connect EMPLOYEES datastore to EMP

5.    Select PREV_SAL attribute in EMP datastore and go to property inspector, disable update checkbox and enable UD2 checkbox as below

6.    Select SAL attribute in EMP datastore and go to property inspector, enable UD1 checkbox as below

7.    Customize the KM whichever we imported earlier as below. (i.e., by adding one more step to it)

update <%=odiRef.getTable("L" , "TARG_NAME" , "D" )%> T

                      set <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"(UD2)" )%> = 

                        <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"(UD1)" )%>

                      where <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"UK" )%> in 

                                      (SELECT <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"UK" )%> 

                              FROM <%=odiRef.getTable("L" , "TARG_NAME" , "D" )%> T,

              <%=odiRef.getTable("L" , "INT_NAME" , "W" )%> S

                                   WHERE <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"UK" )%> =

                  <%=odiRef.getColList( "" , "S.[COL_NAME]" , ",nt" , "" ,"UK" )%>

                               AND <%=odiRef.getColList( "" , "T.[COL_NAME]" , ",nt" , "" ,"(UD1)" )%> 

                           <> <%=odiRef.getColList( "" , "S.[COL_NAME]" , ",nt" , "" ,"(UD1)" )%>)

8.    Execute the mapping

9.    Update the employees table and re-execute the mapping

UPDATE REPLICA.EMPLOYEES

SET SALARY = 22200

WHERE EMPLOYEE_ID = 100;

COMMIT;

10. Output as below