X

Welcome to All Things Warehouse Builder

  • ETL
    April 20, 2011

ODI 11g – Interface Builder Accelerator

David Allan
Architect

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.

Join the discussion

Comments ( 12 )
  • Olivier Monday, August 22, 2011

    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


  • David Tuesday, August 23, 2011

    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


  • guest Saturday, November 19, 2011

    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


  • guest Saturday, November 19, 2011

    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.


  • guest Saturday, November 19, 2011

    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.


  • guest Saturday, November 19, 2011

    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.


  • guest Thursday, November 24, 2011

    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.


  • David Monday, November 28, 2011

    Hi

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

    Cheers

    David


  • guest Monday, August 20, 2012

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

    thaks

    yugandhar


  • David Monday, August 20, 2012

    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


  • Tish Thursday, October 17, 2013

    Hi,

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

    Goal is to learn:

    ODI

    ODI automation


  • David Thursday, October 17, 2013

    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


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha