Generating ODI Packages

Question

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

Foreign keys in datawarehouse??? Create tables dynamically??? Are you sure that the right design? Maybe you should consider creating partitions in the table and load into single partition? — Rusty
Let me elaborate: Were using the Inmon datawarehouse setup where we have three distinct layers: * L1 where we copy the data from the different source tables * L2 where we do some transformations on the tables but keep the normalisation * L3 where we create star schemas with dimensions and facts (the data marts) The load is done daily from source up until the third layer (only the updated records since last load). — rtemperv
There is no problem in having foreign keys in a data warehouse. Some people like to have it enabled. For Oracle I personally disable then and add the NOVALIDATE and RELY clause. Even if the database won't check it when inserting, it's used by the Oracle optimizer and it's seen by other tools. For instance they can be imported in OBIEE so you don't have to redo all the joins manually. I have no problem with the dynamic creation of tables either for the staging or for the foundation layer in the Oracle Information Management Reference Architecture. — JeromeFr

JeromeFr JeromeFr · Accepted Answer · 2014-10-22T07:13:28

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.

Generating ODI Packages

2 Answers