Wednesday 27 March 2013

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

Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.

The Type 3 method tracks changes using separate columns. Whereas Type 2 had unlimited history preservation, Type 3 has limited history preservation, as its limited to the number of columns we designate for storing historical data.
Following are the steps:

1. Create following tables in database:

CREATE TABLE SCOTT.EMP
(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2)
)


CREATE TABLE SCOTT.EMP_TYPE3
(
  EMP_KEY    NUMBER(4),
  EMPNO      NUMBER(4),
  ENAME      VARCHAR2(10 BYTE),
  SAL        NUMBER(7,2),
  ENAME_OLD  VARCHAR2(10 BYTE)
)




2. Open SPOON à Fileà New à Transformation
  
3. Insert Table Input Step
















                               
4. Right clickàEdit Step. Set the following properties:













                                                 

5.Insert DataBase Lookup step

6. Set the following properties:

 

7. Create a Hop between Table Input step and Database Lookup Step














                      
8. Add a Sequence Step. Set the following properties.














                 
Note: A sequence must be created in database.

9. Add Filter Rows. Set the following properties:
 













               
10. Insert Select Value Step and set the following properties:




                                    











             
   














                                         
11.Add Filter Row 2 Step. Set the following properties:















12. Add a Dummy(do nothing) Step.















13. Add Execute SQL Script Step to write a Query.


Set the following properties:
 













                                                  
14. Insert a Update Step. Set the following properties:

 


15. Create appropriate Hop between steps. The overall structure will look like following:
















16. Save and execute.