Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

Automating ODI development tasks using the SDK

By Ayush Dash, Oracle Consulting Services

Oracle Data Integrator (ODI) 11g uses
Interfaces to move data from a source to a target datastore. ODI Studio is a
very convenient drag and drop UI to build, test and execute such interfaces. These
interfaces can have different processing logic and complex transformations for
disparate requirements, but there could be interfaces which behave in the exact
same manner except the source and target are different.

Let’s say, I have these
requirements defined, able to identify the different buckets of processing
required and develop respective interfaces. That’s easily done! However, if I change
the requirement such that I need 10 interfaces for each bucket, the requirement
gets little complex and am I face increased level of effort. What about 100
such interfaces for each buckets? Much more effort required now! It’s the same
repetitive set of tasks but it needs to be done for each interface for each
bucket. The problem we face here is to somehow expedite and automate the entire
sequence of steps for each bucket and reduce the redundant, manual development
of ODI interfaces. As the number of interface grows, our problem (increase in
effort) compounds.

Note, this is not limited to
interfaces only, it can be extended to generate scenarios, packages etc.

Use Case:

In one of my ODI engagements,
we had the below requirements with aggressive timelines.

  1. Incremental
    Loads from a Source Database to Incremental Database. (ODI interfaces)
  2. Data
    masking on Incremental Database (not an ODI task)
  3. Incremental
    loads from Incremental Database to Target Database. (ODI Interfaces)

This had to be done for Oracle
and PeopleSoft (2 buckets) and a total of 2300 tables (So a total of 4600
interfaces. 2300 interfaces for step 1 and 2300 for step 3) and eventually
respective scenarios.

ODI SDK Groovy Scripts:

ODI Studio provides a Groovy Editor; a java based scripting
language as part of its install. Groovy can be leveraged to work with ODI SDK
and build automation scripts. Below is the list of scripts;

  • CreateProject
    – Creates an ODI Project with a ProjectName and FolderName.
  • ImportKnowledgeModules
    – Imports the specified Knowledgemodules to the KM directories.
  • CreateModels
    – Creates the source and target Models for existing Datastores.
  • CreateModelsandDatastore
    – Creates Datastores and Models.
  • CreateInterfaceIterative
    – Iterates through all the Source Datastores and generates an interface for
    each with respective Target Datastores.
  • CreateInterfaceIterativeandSetKMOptions
    – Creates Interfaces and set a KM options iteratively.
  • CreateScenario
    – Create scenarios for all the interfaces.
  • ExecuteScenario
    – Executes all the scenarios under all the interfaces.
  • CountInterface
    – Counts the no. of interfaces, can be used al validation.

The scripts and guide have been uploaded to the Oracle Data Integration project on Java.net: http://www.oracle.com/technetwork/middleware/data-integrator/oracle-data-integrator-exchange-3754343.html.

All the scripts can be downloaded from here: http://www.oracle.com/technetwork/middleware/data-integrator/downloads/odi11g-automation-scripts-3756667.zip

Please refer to the ODI SDK Automation Guide for detailed steps: odi_11g_sdk_automation_guide.doc

Join the discussion

Comments ( 8 )
  • guest Monday, April 27, 2015

    Thanks for this brief description.

  • guest Tuesday, November 29, 2016

    Hello Julien,

    I am just wondering why did you still have to manually reverse the datastores in the description on how to "Create Models and Datastores".

    As part of the automation, I am actually looking forward to create a new empty datastore in my model, and then count its rows and reverse engineer it from within the datastore itself (or at least that's how I do it right now because its more convenient in the company Im working right now).

    Can't find any reference of methods to use on the SDK documentation http://docs.oracle.com/cd/E14571_01/apirefs.1111/e17060/toc.htm .


  • Julien Tuesday, November 29, 2016


    The ODI SDK doesn't support reverse engineering datastores at this time which is why this was done manually. Adding support for reverse engineering operations is in the roadmap.



  • Julien Friday, February 3, 2017

    Hi Mangal,

    It looks like a classpath issue, are you running this from the Groovy Editor in ODI Studio 11g? This is an old article so it may also be a version mismatch issue and you may have to update the code to make it work in your ODI version.



  • Mangal Singh Friday, February 3, 2017

    Hi Julien

    I am executing groovy script from IntelliJ Idea 15 Community Edition.

    I am using ODI version


    Mangal Singh

  • Julien Friday, February 3, 2017

    You could try to run it directly in ODI Studio, this will ensure all libraries are included in the classpath.



  • Mohit Wednesday, June 6, 2018
    Hi Juliet,Thanks for blog. links don't work anymore.
    Is there any way to get around that.?

  • Julien Wednesday, June 6, 2018
    Thanks for reporting the broken links. It should be fixed now.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.