How to handle Slowly Changing Dimension using Pentaho Data Integration?

The below steps help the user to handle Slowly Changing Dimension using Pentaho Data Integration (PDI) tool.

We can maintain historical attribute of a dimension by using "Combinational Lookup/Update" control in PDI.

Step 1: Design your package as shown below. In my example I had taken "dbo.Branch" table as source with the following columns:

SELECT     [BranchID]
    ,[BranchName]
    ,[Address1]
    ,[Address2]
    ,[City]
    ,[State]
    ,[Country]
    ,[Telephone]
FROM [dbo].[Branch]

Step 2: Add a Combination Look up, and configure the settings as shown below:

Step 3: Select the target table "dbo.DimBranch", map the source columns with the target columns, give th ename of the Surrogate Key in tachnical key field text box "Branch Key", select Use Auto incremental field and enter a datafield column name in Date of last update field.

Step 4: Save and Run the package, the package will execute successfully. Now change the values in any column of the source table and run the package again. A new row will be created with updated row and new surrogate key in target table.