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. Hi,

    How can we globally change the behavioural property of the columns for SCD2
    I came across some groovy related script for global change, coz changing 500+ columns is hectic.


  2. Hi,
    In my SCD Type 2 logic, is there a way to track the version number also?
    Ex: My target table should look like below:
    cust_id vrsn_bgn_dt vrsn_end_dt flag vrsn_num
    100 02/01 03/05 N 1
    101 06/01 09/05 Y 1
    102 04/07 08/05 Y 2
    Everything is working, except the vrsn_num logic.
    Any suggestions.

    • Here is the correct example:

      cust_id vrsn_bgn_dt vrsn_end_dt flag vrsn_num
      100 02/01 03/05 N 1
      101 06/01 09/05 Y 1
      100 03/06 08/05 Y 2

      • Hi Divya,

        If you have got the answer to your question, can you please forward it to me also..? Because I also have same scenario.
        Thanks in advance.

        • Hi guys,

          I believe that is possible mapping the version column with a “rank” command.

          In fact I never tried it, but seems be possible in theory…

  3. Hi Cezar, I did Simulation and have PDF ready and Can I have your email so that I can send PDF?


  4. Thanks Cezar, I am using ODI 11G. I think the porblem is ODI is Set based ETL tool and all the data keeps in Oracle DB memory as ODI doesn’t have server memory (cache) option like Informatica ETL has and also Informatica is row by row based tool.

    ODI SCD type2 works perfectly with delta load as you will have few records compare to historical load.

    for ex: we are trying to implement brand new DM but we have history in the source systems.
    STG_PLCY – Source
    PLCY_DIM – Target

    for a PLCY_NUM=100 Nat Key, I have 100 records with changes over the time and I want to load the data into PLCY_DIM table but update the current flag to ‘0’ for 1-99 records and flag the 100th record to ‘1’


  5. We are trying to load historical data and as Steve said in earlier thread: my source can contain multiple rows which need to be inserted once, and then updated several times in my target – this SCD KM will generate multiple inserts – and no updates (set the current flag =’0′ and eff_to_dt = SYSDATE to the previous record in this current load) – since at the time it determines insert/update, the record does not exist in the target – and therefore all source records get flagged for insert.

    for we have PLCY_DIM target table with below fields:
    PLCY_SK – Surrogate KEy
    PLCY_NUM (Natural Key)
    PLCY_TERM (Add row on Change)
    PLCY_EFF_DT (Add row on Change)
    PAY_PLAN_TYPE(Add row on Change)
    ROW_EFFECTIVE_FROM_DATE (Starting Timestamp)
    ROW_EFFECTIVE_TO_DATE (Ending Temestamp)
    ROW_CURRENT_FLAG (1 for new record, 0 for old record)

    I am trying to load history for the same PLCY_NUM (Nat Key) and all the records need to be inserted and update the below fields for old records and insert the new records for these updates:

    ROW_EFFECTIVE_TO_DATE (Ending Temestamp) —> TRUNC(SYSDATE) to old record and ’31-DEC-9999′ for the new record (for the update)
    ROW_CURRENT_FLAG (1 for new record, 0 for old record) —> 0 to old record and 1 to new (updated record).

    when I use IKM Oracle Slowly Changing Dimension, I see all the records inserted for the same PLCY_NUM with ROW_EFFECTIVE_TO_DATE to ’31-DEC-9999′ and ROW_CURRENT_FLAG =1 but I want previous records to be updated on the fly and I want to see only one record as latest record.

    could you please advice how to accomplish this?

    Thanks in advance.


    • HI Janardhan,

      it should work as you wish… if you are using ODI 11G, could perform a simulation and send me the generated PDF?

  6. Doing the Same process and it’s supposed to put an END Date to old record [after inserting new ,changed dimesnion] but i’m seeing only new records added and the old end_dates are left null. Validated flag is set to 1 for both old and new records.. Any Ideas on what’s missing? I have deleted Historize old rows command as it’s causnig this error: ORA-01747: invalid user.table.column, table.column, or column specification

    Thanks for any help you can provide

  7. Hi, i have followed your instruction, but I have such kind of error!!!
    Do you know what is the problem?

    The application script threw an exception: java.lang.IllegalArgumentException: Illegal group reference BSF info: Create index on flow table at line: 0 column: columnNo

    out.print(“create index\t\t”) ;

    out.print(odiRef.getTable(“L”, “INT_NAME”, “W”).replaceAll((char)34+”\\Q%INT_PRF”+odiRef.getInfo(“TARG_NAME”)+”\\E”+(char)34, (char)34+”%INT_PRF”+odiRef.getInfo(“TARG_NAME”)+”_IDX”+(char)34)) ;

    out.print(“\non\t\t”) ;

    out.print(odiRef.getTable(“L”,”INT_NAME”,”A”)) ;

    out.print(” (“) ;

    out.print(odiRef.getColList(“”, “[COL_NAME]”, “, “, “”, “SCD_NK”)) ;

    out.print(“)\n”) ;

    out.print(odiRef.getUserExit(“FLOW_TABLE_OPTIONS”)) ;

    out.print(“\n”) ;

    ****** ORIGINAL TEXT ******

    create index

    on ()

    • You need to mark the columns in the interface with Unique Keys or create them in the ODI Target Datastore , based on the unique Key/PK the index are created.

    • I have found the problem! The problem was the name of the table! For example i have named at first as P$TEST#TABLE, then we had the problem with index! Then i renamed the table to P_TEST_TABLE! Then error is disappeared !!

  8. Hi,

    I am using IKM SCD Slowly Changing Dimension. In my dimension, I already have surrogate key, natural key etc. I have couple of columns marked as “Overwrite on change” and couple of “Add row on change”. I am building event based ETL process. So I have one CREATE event and multiple UPDATE event of same natural key. And I need to store these events in my dimension as SCD type 2.

    So when I execute my interface, I am having multiple updates in my source and I am getting error on ‘Historize old rows’ step as ORA-01427: single-row subquery returns more than one row.

    Can anyone please help me out in this?


  9. Make sure the box beside “staging area is different from target” is not ticked on the definition tab

  10. Hi Kshitiz,

    I tried the same steps but none of the rows are being inserted.

    Any advice?

    Thanks in advance..

  11. Hi Kshitiz,

    I was using the surrogate key as a natural key in target table.
    May be for this reason, the problem was happening…
    Now it’s ok!

    Thanks for help me ;D

  12. Hey guys!

    I’m trying to implement SCD in ODI. I’ve got a target table like the example above, with sorrogate key, natural key, columns to be updated, columns with the option “ADD ROW ON CHANGE”, etc… I’m using IKM Oracle Slowly Changing Dimension too. But the issue is the following: when I try to update some row in my source table and to execute the interface to update my target table, doesn’t happen anything in target table.

    Is missing something?
    What can I do to see the changes in my target table?
    Can someone help me?


    • Josue ,

      If you are updating the Columns which have ‘update row on change’ the column should have got updated . Please check for that.

      Can you see those records in I$ and also in the Step
      ‘Flag Rows for Update ‘
      ‘ Update Existing Rows ‘ , can you see any number of inserts or update in the operator.
      Please check for the that.
      You can enable option ‘DELETE_TEMPORARY_OBJECTS’ to YES ,to debug I$ table.

      If you need more help , Please send me a mail at kdevendr@gmail.com .

  13. This works great – except when I can have multiple source rows – which need to become an insert , and then multiple updates to the same target row.

    Since the target row does not exist when loading the SCD, the KM marks all the rows as INSERTs.

    Any way to get this to work??

  14. This all works great … IF your source only contains one of each SCD target row to be inserted.

    However, if your source can contain multiple rows which need to be inserted once, and then updated several times in your target – this SCD KM will generate multiple inserts – and no updates – since at the time it determines insert/update, the record does not exist in the target – and therefore all source records get flagged for insert.

    Any way to get around this??

    • Steve , if you are looking for Insert and then Update then you should go for Oracle Incremental Update. This KM is created only to support SCD type 2 logic.you can select the option ‘Update Row on change’ for the columns to be only updated.
      Please let me know with an example ,as what exactly is your requirement,if you are looking for some thing else.


Leave a Reply

Required fields are marked *.