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