Manage join between two SCD2 type dimensions PDF Print E-mail
Written by Administrator   
Tuesday, 01 February 2011 09:08
Article Index
Manage join between two SCD2 type dimensions
Using the history join plugin
All Pages

Join two dimension type SCD2

Dimensions SCD2 type (Slowly Changing Dimension) are dimension with the story of the record's change with date range columns. The primary problem is to join two table considering them history. For example two dimension A and B where A is linked to B:

Table A:

ID - DATA - DATE FROM(dd/MM/yyyy) - DATE TO(dd/MM/yyyy)

1 - VAL1 - 01/01/2002 - 31/12/9999

 

Table B

ID - DATA - ID A - DATE FROM(dd/MM/yyyy) - DATE TO(dd/MM/yyyy)

1 - VAL A - 1 - 01/10/2005 - 10/10/2009

2 - VAL B - 1 - 11/10/2009 - 31/12/9999

 

Join these two table must create 3 records

Table A + Table B

ID A - ID B - DATA A - DATA B - DATE FROM - DATE TO

1 - null - VAL1 - null - 01/01/2002 - 30/09/2005

1 - 1 - VAL 1 - VAL A - 01/10/2005 - 10/10/2009

1 - 2 - VAL 1 - VAL B - 11/10/2009 - 31/12/9999

 

Now this example is more simply but you can have more cases. In the Pentaho Data Integration you must draw more steps and write more code to manage these cases.

 



Last Updated on Tuesday, 01 February 2011 14:10