The blog for Oracle Data Integrator ( ODI )

SCD Type 2

Hi Guys,

Today we are going to talk about an old question that we already got several, hundreds, thousands (lol) of questions but, at true, is pretty easy!

I would like to indroduce you to the Mr. SCD!!!!!

In few lines, you will be able to configurate and use it…. Let’s start:

This post defines the necessary setting to use the Oracle Slowly changing dimension .


Set the columns with required properties namely,


NATURAL_KEY – Primary or Unique Key

OVERWRITE ON CHANGE – Column for which data needs to be updated

ADD ROW ON CHANGE – Column for which a new Surrogate Key and New rows to generated when the data for this Column changes

CURRENT RECORD FLAG – 0 or 1  .  The KM populates this column so no need to populate this column .(1 : Current Record , 0 : Past Records)

STARTING TIMESTAMP – Column  indicating the beginning of a Record’s availability. use SYSDATE or SYSTIMESTAMP depending on the datatype.

ENDING TIMESTAMP -Column indicating the end of the Record’s availability. The value is a default of “01 January 2400”









Map the columns in this way where the Surrogate Key needs to be sequence .  The ending timestamp and Current Flag columns  need to be mapped even that the  KM will populate those column accordingly. Just add any value to them.

Note :

1. If in case you don’t  have any ‘Overwrite on change‘ column , please check the option ignore errors on Update Existing row., since in the select statement it wont find the column to do query and will error out.

2. If the Target datastore is table , Flow control or static control have to yes and the appropriate CKM have to be selected , else there will be error in Historize old rows saying ‘invalid user.table.column, table.column, or column specification”.

Well, as you can see, no secrets to use it!!!!


  1. Pingback: Ora 01747 Error

Leave a Reply

Required fields are marked *.

This site uses Akismet to reduce spam. Learn how your comment data is processed.