The blog for Oracle Data Integrator ( ODI )

Using one of ODI’s lesser-known secrets: the Common Format Designer (CFD) – by Craig Stewart

Hi Reader,

It’s a great pleasure starts a new “sector” from ODI Experts. It is our “Invited Experts” and we will start with one of the main ODI Guru in the world. Craig Stewart!!!

Thank you Craig!

Please, enjoy his post!


As I’m essentially in pre-sales, I like tools which I can use to rapidly do tasks which might otherwise take a lot of grind. Things like the Knowledge Modules in Oracle Data Integrator (ODI) are well known to take complex tasks and to make them simple – like integrating slowly changing dimensions: Simple with the IKM. One of the things which was introduced into ODI (actually into Sunopsis, before the acquisition by Oracle, was the Common Format Designer, In the latest release of ODI (11g) it is actually much better documented (even if it has not evolved much!) than in earlier versions – but what I’d like to show you briefly here is how it can be used to rapidly implement a new schema and to generate the interfaces to populate it.

To start with, let’s say I have a set of tables in my source database, in my case I’ll use MySQL. If I want to move that set of tables, with data across to Oracle, I’ll need to have a schema in Oracle where I can put the tables. In ODI’s Topology Navigator (see, I’m already using the new 11g terminology!) I need to define the Data Servers, the physical schemas and associate them with the relevant logical schemas. This is the point at which I’ll begin, I have a data model in ODI which has my “source” data defined in it:


I want to put this into my Oracle schema which I’ll call ORCL_TMP, so in Desgner, I create a new, empty data model in the Oracle technology for this schema:



Now I create a diagram, by using the Right-Mouse-Button on the Diagrams Node:


I must simply give this a name and any description, and naturally, the documentation…


Then, we go to the Diagram tab so we can start doing the business:


Now we drag & drop the datastores from our MySQL model, onto the new ORCL_TMP diagram, ODI will prompt us with a dialog box:


Which of course we know, and accept. The table is then added to the model, automatically using the metadata about datatype translations to construct the target table.


This will then show in the diagram:


We can then drag across additional datastores to make our model:


Note that in 11g, you are able to select multiple datastores and drag them across to the diagram. One drawback to this is that when referential integrity is defined, using this technique won’t get you the referential integrity, whereas if you drag one at a time, you will. It will give you all keys, indexes foreign keys as defined in the source system, and applicable in the target.

Now for some magic: if you right-mouse-button the Data Model, one of the optrions is to “Generate DDL”:


If we select this option, ODI will ask if we want to process tables not in the moidel (i.e. generate deletes to synchronize the two, or just to generate the delta code for the tables we have in the model. I’ll select “No” for this:


This will bring up a dialog where I can choose which of the differences I want synchronized:


If I select all, and press the OK, it will generate an ODI procedure for me in the Default Folder I entered when I defined the model:


I can press the “Play”/”Execute” button to get ODI to execute the generated code, which I can then follow in the Operator Navigator:


Often, you will get errors with the tables which contain DATE/DATETIME/TIMESTAMP fields as ODI is not always clever about translating them. In this instance I just edited the generated code to take out the TIMESTAMP(23) and changed it to TIMESTAMP. So now I have a fully created data model. The next step is to get some data moved in, so I need interfaces. ODI will of course remember where the data stores were dragged from, and to, so will generate for me an interface which just does a one-to-one mapping using the default knowledge modules:


I select the “Generate Interfaces In” and ODI brings up a dialog for the tables:


The required interfaces are then generated in that default directory:


I need to go into each one in order to execute it (If you try to execute without going in and saving it, you will get an error) You have to force a change, so that ODI can go through the process of saving it, which in turn makes it do some internal stuff, enabling execution. Then execute it.

Now you have data in your newly created data structure!

Craig Stewart


  1. Pingback: ODI 11g – Interface Builder Accelerator | Annoying things on search engines

Leave a Reply

Required fields are marked *.

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