ODI 11g – Interface Builder Accelerator

Often using ANY tool there are scenarios where there is a lot of grunt work, imagine Microsoft tools like Excel without VB and macros to accelerate and customize those boring repetitive tasks. Data integration and ETL design is exactly the same, the tool needs to expose an SDK to a base platform that you can use to make your life easier. Something to automate the grunt work that is common and very repetitive. The ODI 11g SDK let’s you script these kind of repetitive tasks. As an aside the ODI common format designer (see this post here) has a way for migrating like named objects, however using the SDK let’s you control much much more.

To illustrate I have created a simple interface construction accelerator that you can download (interfaceAccelerator.java or 11.1.1.3 and after here), the accelerator generates ODI interfaces from a control file that defines the interface name, the source and the target – simple and a nice example for demo purposes. If you look at the java code, it is very basic (no pun intended).  It literally is a dozen lines of code. The image below illustrates the java program interfaceAccelerator using the ODI 11g SDK to take as inputs the configuration of the connection details and a control file specify the source to target datastore mappings.

image

The code when called has a bunch of command line parameters shown below and the standard input stream is the interface control file, so the command line looks like;

java –classpath <cp> interfaceAccelerator <url> <driver> <schema> <pwd> <workrep> <odiuser> <odiuserpwd> <project> <folder> < <control_file>

the control file provided in the standard input stream needs to be a comma delimited file with the following structure

    • interface_name,source_model,source_table,target_model,target_table

for example a sample command line using an Oracle repository could be

java –classpath <cp> interfaceAccelerator jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK < icontrol.csv

the interfaces will be created in the folder SDK and the project code is STARTERS. The icontrol.csv file used above was (remember the format is interface_name,source_model,source_table,target_model,target_table, this is just what I happened to use in this simple demo program);

    • INTFC1,SCOTT,EMP,STG_MODEL,STG_EMP
    • INTFC2,SCOTT,DEPT,STG_MODEL,STG_DEPT
    • INTFC3,SCOTT,BONUS,STG_MODEL,STG_BONUS

You can created as many interfaces from this driver control file as you desire, the interface generated will map from the source table to the target table and use ODI’s auto mapping to perform column level mapping of the source to target table, it will also create default source sets and use the default KM assignment. So you get a pretty useful set of stuff as a basis here.

The interfaces generated whilst executing this accelerator look like the following, the table to table map with all of the like-named columns mapped, the physical flow configured with defaults KMs!

image

You can take this code and customize to make it fit your needs or send in comments on how to do things. In summary if you are finding you desire ways of tuning your work to make using ODI even more productive, then you should look into the ODI 11g SDK and see if you can automate, automate, automate.

Comments:

Hello,

Thanks a lot for this example.
If I may, I would like to go further ... would it be possible to do the same with a column level mapping ?

Thanks in advance
Olivier

Posted by Olivier on August 22, 2011 at 09:21 AM PDT #

Hi Olivier

Yes you can also describe the column level mappings and use APIs to create the mappings within ODI.

For example check out the examples online, for example the SimpleInterfaceCreationSample.java illustration uses the MappingDefinition class to create the expressions on the target from datastores in the source...
https://www.samplecode.oracle.com/tracker/tracking/linkid/prpl1004/remcurreport/true/template/ViewIssue.vm/id/S564/nbrresults/51

Cheers
David

Posted by David on August 23, 2011 at 09:23 AM PDT #

Hi,

Am new to Java programming. Can u tell me how to execute this in Eclipse. I provided the 9 parameters and control file as VM arguments but it throws an error saying Java.lang.ClassNotFoundException

Posted by guest on November 19, 2011 at 02:00 AM PST #

Hi,

Am new to Java programming. Can u tell me how to execute this in Eclipse. I provided the 9 parameters and control file as VM arguments but it throws an error saying ClassNotFoundException.

Thank You.

Posted by guest on November 19, 2011 at 02:01 AM PST #

Hi,

Can u tell me how to execute this in Eclipse. I provided all 8 parameters but did not know where to provide the control file. When I try executing the program providing control file as VM arguments it's throwing an error saying ClassDefNotFound.

Thank You.

Posted by guest on November 19, 2011 at 05:08 AM PST #

Hi,

Can u tell me how to execute the program from Eclipse IDE. I provided all 8 arguments but did not know how and where to provide the control file.

Thank You.

Posted by guest on November 19, 2011 at 05:09 AM PST #

Hi,

I tried executing this program. But when I run it it fails throwing an exception saying Datastore cannot be null for all the interfaces:

at org.springframework.util.Assert.notNull(Assert.java:113)
at oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddSourceDataStore.<init>(InterfaceActionAddSourceDataStore.java:60)
at odi.sdk.DevelopIntf.main(DevelopIntf.java:118)
java.lang.IllegalArgumentException: DataStore shouldn't be null
at org.springframework.util.Assert.notNull(Assert.java:113)
at oracle.odi.interfaces.interactive.support.actions.InterfaceActionAddSourceDataStore.<init>(InterfaceActionAddSourceDataStore.java:60)
at odi.sdk.DevelopIntf.main(DevelopIntf.java:118)

Can u tell me why am getting this error?

Thank You.

Posted by guest on November 24, 2011 at 12:50 AM PST #

Hi

Glad you got past this issue, worth posting what the problem was and how you resolved so others can see.

Cheers
David

Posted by David on November 28, 2011 at 02:56 AM PST #

hi guys i am new to odi please tell me the site for odi user manuals

thaks
yugandhar

Posted by guest on August 20, 2012 at 04:56 AM PDT #

Hi Yugandhar

The ODI OTN page is here;
http://www.oracle.com/technetwork/middleware/data-integrator/documentation/index.html

The documentation set is linked from this page, there is a really useful 'Getting Started Guide' which I would suggest anyone picking up ODI for the first time use. There is a nice small demo environment shipped with the product for quickly getting to grips with the product. The rest of the documentation set has full reference manuals.

Another useful URL to be aware of is the ODI OTN forum, worth bookmarking and using this, best place to ask questions and get help from the community;
https://forums.oracle.com/forums/forum.jspa?forumID=374

Finally there is an Oracle Data Integration group on LinkedIn;
http://www.linkedin.com/groups/Oracle-Data-Integration-140609?mostPopular=&gid=140609

Cheers
David

Posted by David on August 20, 2012 at 08:44 AM PDT #

Hi,

I'm new to ODI, what is the best way to start with it.
Goal is to learn:
ODI
ODI automation

Posted by Tish on October 17, 2013 at 03:16 PM PDT #

Hi Tish

There are ODI 11g OBEs (Oracle By Example) below that are useful to look at;
http://apex.oracle.com/pls/apex/f?p=44785:24:14696599790351::::P24_CONTENT_ID%2CP24_PREV_PAGE:5185%2C24

There are ODI 12c getting started series below;
http://apex.oracle.com/pls/apex/f?p=44785:24:0::::P24_CONTENT_ID,P24_PREV_PAGE:7952,1#!

What do you mean by automation? Using scripting/SDK?

Cheers
David

Posted by David on October 17, 2013 at 03:24 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today