Implementing Pure SCD Type 6 in Pentaho

Question

I have an interesting task to create a Kettle transformation for loading a table which is a Pure Type 6 dimension. This is driving me crazy

Assume the below table structure

|CustomerId|Name|Value|startdate|enddate|
|1|A|value1|01-01-2001|31-12-2199|
|2|B|value2|01-01-2001|31-12-2199|

Then comes my input file

Name,Value,startdate
A,value4,01-01-2010
C,value3,01-01-2010

After the kettle transformation the data must look like

|CustomerId|Name|Value|startdate|enddate|
|1|A|value1|01-01-2001|31-12-2009|
|1|A|value4|01-01-2010|31-12-2199|
|2|B|value2|01-01-2001|31-12-2199|
|3|C|value3|01-01-2010|31-12-2199|

• Check for existing data and find if the incoming record is insert/update
• Then generate Surrogate keys only for the insert records & perform inserts.
• Retain the surrogate keys for the update records and insert it as new records and assign an open end date for the new record ( A very high value ) and close the previous corresponding record as new record's start date - 1

Can some one please suggest the best way of doing this? I could see only Type 1 and 2 using the Dimension Lookup-Update option

Answer 1

I did this using a mixed approach of ETLT.