ODI – Incremental Update and Surrogate Key using Database Sequence

I am writing this because it had me lose my sanity for some time. If,

• You’re using Oracle Data Integrator
• You’ve defined a dimension table with a surrogate key even if it is not a SCD type 2 (recommended by Kimball)
• The surrogate key is maintained through a database sequence (Oracle sequence in my case)
• Your IKM in Incremental Update
• You’re frustrated about how to control updates to a dimension having a sequenced surrogate key

Then read on for the solution that worked for me. The solution is pretty standard – you only need to know what you’re doing and setting the right options.

ODI – How to Reverse Flat Files

The most important thing to remember before using flat files as datastores in ODI is that the Designer looks for them in the client PC where it runs. I haven’t tried using a UNC (\\computer\share\…) but that should work.

ODI comes pre-configured with a folder for dealing with flat files – in the Topology Manger, you’ll find the FILE_GENERIC data server set up to serve files from the ODI_HOME/oracledi/demo/file directory. But if you want your own file data server then follow these steps: