By Julien Testut-Oracle on Oct 24, 2014
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.
In one of my ODI engagements, we had the below requirements with aggressive timelines.
- Incremental Loads from a Source Database to Incremental Database. (ODI interfaces)
- Data masking on Incremental Database (not an ODI task)
- 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: https://java.net/projects/oracledi.
All the scripts can be downloaded from here: https://java.net/projects/oracledi/downloads/download/ODI/SDK%20Samples/ODI%2011g%20SDK%20Automation/Automation%20Scripts/Automation%20Scripts.zip
Please refer to the ODI SDK Automation Guide for detailed steps: https://java.net/projects/oracledi/downloads/download/ODI/SDK%20Samples/ODI%2011g%20SDK%20Automation/ODI%2011g%20SDK%20Automation%20Guide.doc