Pentaho Data-Integration (aka. Kettle): SCD Type2

The Type 2 method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. With Type 2, we have unlimited history preservation as a new record is inserted each time a change is made.

Following are the steps:

 1. Create required tables in database.

2. Insert following Steps with following properties

2.1 Table Input

2.2 Database Lookup

2.3 Filter Rows

2.4 Get System Info

2.5 Add Sequence 2

2.6 Select Value

2.7 Table Output

 

2.8 Filter row 2

2.9 Dummy(do nothing)

 

2.10 Execute SQL Script

2.11 Add System Info 2

2.12 Add Sequence

 2.13 Select Value 2

2.14 Table Output 2

3. The overall steps arrangement will be as follows:

                        

4. Save and execute.