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