Posted Sat, 27 Sep 2014 02:21:00 GMT by

I am evaluating ETL platforms and have a need to use type 2 slowly changing dimensions. Several tools I've looked at do not support this easily without writing user scripts or code outside the tool. Can I do it in RODIN?

Romano

Posted Thu, 16 Jun 2016 13:00:00 GMT by

Hi Romano,

Good question. RODIN handles type 2 SCD easily and mostly automatically. When you define the data warehouse table (called a data set in RODIN), you specify that it uses a surrogate key (which is necessary for SCD support), and that it tracks type 2 SCD.

In any SCD implementation, you need certain columns to be included in the table:

An Effective-To date is mandatory. This is used to set the range of dates for which the row is active.
Some data modelers also include an Effective-From date, however this is not necessary, so is optional in RODIN
Similarly you may wish to include a Current Row Flag, but again this is not necessary.
Another requirement is an index over the table that includes the original key(s) of the data and the effective to date.
Finally, you must of course identify those columns that are to be tracked as type 2 SCD.
RODIN ensures that you define all of the mandatory elements when defining the data set.

Then when you add this data set to an ETL definition, RODIN automatically generates all of the code and mappings needed to identify if one of the the type 2 SCD columns have changed when loading an existing entity, and if so, a new row is inserted using a new surrogate key. The old 'current row' is updated setting the effective to date to today, which effectively 'closes' it out.

I hope that answers your question. Thanks for looking at RODIN!

PS. Keep an eye on the web site.. we're planning to post a video demonstration of our SCD support soon.

Originally Posted By AJ

You must be signed in to post in this forum.