Skip to main content


Loading a Slowly Changing Dimension Table with ODI

As I have been writing my blog posts about ODI, my director recently reached out to me asking why I don’t put a blog posting on how to load a slowly changing dimension table.  I thought that was a great idea as when I first started to use the tool this was a major requirement and at first I had no idea how to do this. When I learned how to do this within ODI, I was so surprised how easy this was to setup.  With that being said, let me get into the steps in showing you all how this can be done with ODI.

Now before we get started, there are a few things I want to point out.  Now the example in which I will be going over will be using an Oracle table as my ODI datastore.  For ODI to handle your slowly dimension table you will need to have the following required columns.

  • Surrogate Key – This should be self-explanatory.
  • Current_Flag_Field –  This will be a numeric field in 1 char length.
  • Starting Timestamp – This is a date or timestamp field in which will record the current create/update date/timestamp.
  • Ending TimeStamp –  This is a date or timestamp field in which will record the ending update date/timestamp in which when a record is no longer a current record.

Once the following required columns have been checked off in your table you can get started.  Now as we get started make sure you are logged into ODI.  Once you have you have logged into ODI, navigate to the Models tab within ODI.

Screen Shot 2014-09-07 at 5.22.45 PM

Next locate the ODI datastore in which you would like to update.   If your datastore cannot be found you will need to import your datastore.  Here in this example I will be using a CUSTOMER_ADDRESS Oracle table.

Screen Shot 2014-09-07 at 5.23.17 PM

Once you have located the datastore in which you will be updating, expand the “Columns” option in which will list all the columns for the table.

Next take the first column and double click and open its settings.

Screen Shot 2014-09-07 at 5.23.55 PM

Here in the definition tab, locate the “Data Service Operations” section and update the service options as necessary.  In this example I will be leaving all the columns options checked.

Next click on the “Description” tab.  Here you will need to identify the column properties in which you will be able to handle your slowly changing dimension behavior.

Screen Shot 2014-09-07 at 5.24.33 PM

ODI allows you to select one of the following ODI column options “Surrogate Key, Natural Key, Overwrite on Change, Add Row on Change, Current Record Flag, Starting Timestamp, and Ending Timestamp.”

Please note, you will need to update all columns in your target datastore to handle your slowly changing dimension behavior.

Once you have updated all the column settings your datastore will be able to handle your slowly changing dimension behavior.  Again as I said from the start of my blog post these are very simple steps.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Chris Evans

Chris Evans has extensive management, technical, consulting and customer service skills. He provides hands-on expertise in project leadership and management, assessments, methodologies, data modeling, database design, meta data, systems analysis, and development. He has worked with multiple platforms, and his experience spans a wide range of operational and data warehouse environments. In addition to his BI background, Chris Evans has a experience in marketing strategy for major consumer brands.

More from this Author

Follow Us