ODIExperts.com

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:

clip_image002

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:

clip_image004

clip_image006

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

clip_image008

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

clip_image010

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

clip_image012

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

clip_image014

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.

clip_image016

This will then show in the diagram:

clip_image018

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

clip_image020

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”:

clip_image022

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:

clip_image024

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

clip_image026

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:

clip_image028

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

clip_image030

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:

clip_image032

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

clip_image034

The required interfaces are then generated in that default directory:

clip_image036

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

5 Comments

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

  2. Hi Craig,

    I have an issue with one of my customer. The Issue in detail is as below :

    ODI Version : 10.1.3.5
    OS : Linux x86

    The Customer says :

    We are using ODI to generate a SOAP web service over a MySQL database, but are experiencing problems when testing published services containing MySQL datetime or timestamp fields.

    Step by Step Description of the problem :

    We are testing an ODI generated web service (generated by the service knowledge module) which exposes data in a MySQL database via Axis2. The database connection is working ok, and using SOAP UI or other client we can retrieve data from tables that have no datetime or timestamp columns, but where there is a datetime or timestamp column the service returns either: a) A SOAP Fault if the datetime elements are empty or omitted or b) no results

    Steps to Reproduce :

    1. Use ODI to generate and deploy an Axis2 web service exposing data in a MySQL database table which contains at least 1 datetime or timestamp column.
    2. Build a web services client and try calling the get****Filter method, to retrieve some records. Omit all optional elements in QueryFilter, and leave an empty entity element.
    3. Observe returned SOAP fault:

    soapenv:Server

    end_datetime can’t be null

    java.lang.IllegalArgumentException: end_datetime can’t be null
    at oracle.odi.dataservices.fwk.axis2.OMSerializer.serializeManagedEntity(OMSerializer.java)
    at oracle.odi.dataservices.fwk.axis2.OMSerializer.serializeManagedEntityList(OMSerializer.java)
    at oracle.odi.dataservices.fwk.axis2.OMSerializer.serializeOMElement(OMSerializer.java)
    at oracle.odi.dataservices.fwk.axis2.DataServicesMessageReceiver.invokeBusinessLogic(DataServicesMessageReceiver.java)
    at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.receive(AbstractInOutSyncMessageReceiver.java:39)
    at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:144)
    at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:279)
    at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:116)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:711)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:368)
    at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:866)
    at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:448)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:302)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:190)
    at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
    at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
    at java.lang.Thread.run(Thread.java:595)

    4. enter a valid XSD datetime value element, e.g:
    0001-01-01T00:00:00.0
    Observe that no results are returned.

    Please suggest me with your points on how to go further with this issue. This is very crucial for me.

    Thanks,
    Venkat

  3. Thanks Craig,

    I did try the multiple table drag and drop and it did come with the reference links in tact, perhaps this is fixed now. As you stated it is relatively easy to use, which is great.

  4. Great Stuff Craig. Thanks for Sharing

  5. Craig,

    Thanks. Few days back I tried to do DDL generation thing and ODI hung for a long time (2 hrs). Later I killed it and did not have the opportunity since then.

    Do you know if 11g provides a way to script the generation of interfaces? Even for the ones that requires join between multiple surce data stores or requires filter?

Leave a Reply

Required fields are marked *.