Search code examples
oracle-databasescriptingoracle11gdata-warehouseoracle-data-integrator

Generating ODI Packages

I am currently working on a data warehousing project where I often need to load tables to layer 1 and layer 2 from the source. Layer 1 is a copy of the source data plus some technical fields and layer 2 handles foreign keys and does some minor transformations.

The process goes as follows:

  • Create the DDL scripts to create the tables in L1 and L2
  • Use ODI (Oracle data integrator) to define interfaces which define the transformations from one layer to another.

This is quite a repetitive task where the transformations stay roughly the same.

I was wondering if there was a way to generate ODI packages + interfaces + variables on th odi agent from a scripting language so that I can automate the largest part of this time consuming part.

ODI version: ODI_11.1.1.7.0_GENERIC Platform: Windows 7

Thanks in advance

Solution

  • Using the ODI SDK, it is indeed possible to perform almost any tasks you can do in ODI Studio : http://docs.oracle.com/cd/E29542_01/apirefs.1111/e17060/toc.htm. This Java API can also be used in Groovy scripts executed directly from ODI Studio (Tools -> Groovy -> New Script). Groovy is a programming language for the JVM, it uses a syntax similar to the Java Syntax with some shortcuts and it's dynamically compiled. With a few exceptions, Java code can be used in Groovy.

    Michael Rainey did a nice presentation to introduce the ODI SDK, Groovy and some use case. Here are the slides : https://s3.amazonaws.com/rmc_docs/biforum2013_slides/odi_mclass_6_sdk_groovy.pdf

    I guess this complete example by the same author might be interesting for you as it adds a few columns to the tables of his Foundation layer (equivalent of your layer 1 in the Oracle Information Management Reference Architecture) : http://www.rittmanmead.com/2012/05/oracle-data-integrator-11g-groovy-add-columns-to-a-datastore/

    Once you understand the concepts, the Oracle Data Integrator team posted a nice script to automate the creation on their blog a few years back. I think it was tested on 11.1.1.5 so it might need some adapations but this is a nice starting point : https://blogs.oracle.com/dataintegration/entry/interface_builder_accelerator

    One limitation with the SDK is that there is nothing to use the versioning capabilities of ODI.