Friday Apr 10, 2015

This Week's A-Team Blog Speaks to Automating Changes after Upgrading ODI or Migrating from Oracle Warehouse Builder

The A-Team not only provides great content, they are humorous too!

Check out this week’s post, the title says it all: Getting Groovy with Oracle Data Integrator: Automating Changes after Upgrading ODI or Migrating from Oracle Warehouse Builder

The article covers various scripts written in Groovy and leverage the ODI SDK that assist in automating massive changes to one’s repository. These initially came to be as a result of customer desire in enhancing their environment in their effort to move from Oracle Warehouse Builder (ODI) to Oracle Data Integrator (ODI), but in the end came the realization that these scripts could be used by any ODI user.

Happy reading!

Tuesday Sep 09, 2014

ODI 12c - Models in Data Modeler and ODI

Ever wondered how to get your models from Oracle SQL Developer Data Modeler (SDDM) in to ODI data models? The most common practice is to generate and execute the physical DDL scripts from SDDM to the target database. Another technique is possible leveraging the ODI SDK and the SDDM SDK - that's what I will illustrate here. There is an example script posted here on the site.

There is an end to end demo viewlet below to see the script in action, check it out;

The viewlet shows the transformation script in action creating the ODI model from the Data Modeler design, here's a peek at the script in action;

In the viewlet you will see how I added the groovy scripting engine as an engine for SDDM - I can then leverage my groovy skills and the ODI SDK to build the script and provide useful capabilities.

This script is publicly available at link above, take, enhance and comment! Join the communities on LinkedIn for Oracle Data Integration and the OTN forum to learn and exhange with other members of the community.

Tuesday Aug 06, 2013

ODI - Accelerator Launchpad, getting Groovy

To organize your groovy scripts and package up utilities there are various approaches to take. This one will exploit a little known fact in the groovy studio support. The code executed has some contextual variables available that you may be aware of - such as the odiInstance variable which represents a handle to the repository in the SDK. There is also another contextual variable which is 'this', this happens to be the groovy shell reference, which is very useful to execute other scripts from. For example the command below if executed will execute the groovy script from the filesystem from my script when executed. 

this.evaluate(new File("d:\\accelerators\odi_create_model.groovy"))

 This is a useful approach if you want to execute your scripts from a launchpad, or quick start/accelerator control. The image below shows a launchpad with buttons for various activities, one of which is the create model accelerator groovy script I created (this was blogged here).

When the '1. Create Model' button is pressed, the groovy script to execute the create model groovy is executed. This can do whatever you want, below is what my groovy script does to create a model;

Each button in the accelerator launchpad is defined as below, the script representing the action is evaluated when the button is pressed, its quite straightforward; 

  1.         button(action: action(name: '1. Create Model', closure: {
  2.           gshell.evaluate(new File("d:\\accelerators\odi_create_model.groovy"))
  3.         })

 You can quite easily invoke your companies specific accelerator routines with this approach.

Here is the full script...

  1. import groovy.swing.SwingBuilder
  2. import java.awt.FlowLayout as FL
  3. import javax.swing.BoxLayout as BXL

  4. def startLaunchPad() {
  5.   def s = new SwingBuilder()
  6.   s.setVariable('myDialog-properties',[:]) 
  7.   def vars = s.variables 
  8.   def dial = s.dialog(title:'ODI Accelerator Launchpad',id:'myDialog',modal:true) { 
  9.   def gshell = this
  10.   panel() {
  11.     flowLayout(alignment:FL.RIGHT)
  12.     label(icon:imageIcon(url:new'file:///d|/accelerators/images/odi_launchpad.jpg')))

  13.     panel() {
  14.         boxLayout(axis:BXL.Y_AXIS)
  15.         panel(alignmentX:0f) {
  16.             flowLayout(alignment:FL.CENTER)
  17.         button(action: action(name: '1. Create Model', closure: {
  18.           gshell.evaluate(new File("d:\\ accelerators\odi_create_model.groovy"))
  19.         }))
  20.         }
  21.         panel(alignmentX:0f) {
  22.             flowLayout(alignment:FL.CENTER)
  23.         button(action: action(name: '2.         ..........', closure: {
  24.           gshell.evaluate(new File("d:\\ accelerators\anotherscript.groovy"))
  25.         }))
  26.         }
  27.         panel(alignmentX:0f) {
  28.             flowLayout(alignment:FL.CENTER)
  29.         button(action: action(name: '3.         ..........', closure: {
  30.           gshell.evaluate(new File("d:\\ accelerators\yetanotherone.groovy"))
  31.         }))
  32.         }
  33.         panel(alignmentX:0f) {
  34.             flowLayout(alignment:FL.LEFT)
  35.             button('OK',preferredSize:[80,24],
  36.                    actionPerformed:{
  37.                        vars.dialogResult = 'OK' 
  38.                        dispose()
  39.             })
  40.             button('Cancel',preferredSize:[80,24],
  41.                    actionPerformed:{
  42.                        vars.dialogResult = 'cancel'
  43.                        dispose()
  44.             })
  45.         }
  46.     }
  47. }  }
  48.   dial.pack()
  50.   return vars
  51. }

  52. vars= startLaunchPad()

The SwingBuilder framework is quite simple and lets you build up these kind of wrapper interfaces very simply.

Wednesday Mar 20, 2013

ODI - Slowly Changing Dimension Quick SDK Setup

You can quickly configure your SCD metadata on ODI datastores using this helper script here. Executing this script from the UI you can specify a driver file to quickly annotate your datastores with slowly changing metadata flags on the columns. To illustrate, let's use the dimension from an earlier posting here, the driver file below will configure all of the column metadata we need on the datastore in ODI.



We can have many datastores configured from one file and use * to set the same value for all columns (that's what I did for overwrite on change above). The script also allows the omission of the model and datastore so you can just specify the column name and the scd type (the add row change line above).

After executing the script the metadata tags defined in the input file are applied to the model in ODI;

Scripting is a great way to beat those boring tasks when you are building large systems where you realize you can work more efficiently. The groovy script invokes the setScdType method on the OdiColumn class here. A little groovy goes a long way.

Friday Oct 05, 2012

Hidden Gems: Accelerating Oracle Data Integrator with SOA, Groovy, SDK, and XML

On the last day of Oracle OpenWorld, we had a final advanced session on getting the most out of Oracle Data Integrator through the use of various advanced techniques.

The primary way to improve your ODI processes is to choose the optimal knowledge modules for your load and take advantage of the optimized tools of your database, such as OracleDataPump and similar mechanisms in other databases. Knowledge modules also allow you to customize tasks, allowing you to codify best practices that are consistently applied by all integration developers.

ODI SDK is another very powerful means to automate and speed up your integration development process. This allows you to automate Life Cycle Management, code comparison, repetitive code generation and change of your integration projects. The SDK is easily accessible through Java or scripting languages such as Groovy and Jython.

Finally, all Oracle Data Integration products provide services that can be integrated into a larger Service Oriented Architecture. This moved data integration from an isolated environment into an agile part of a larger business process environment. All Oracle data integration products can play a part in thisracle GoldenGate can integrate into business event streams by processing JMS queues or publishing new events based on database transactions.

  • Oracle GoldenGate can integrate into business event streams by processing JMS queues or publishing new events based on database transactions.
  • Oracle Data Integrator allows full control of its runtime sessions through web services, so that integration jobs can become part of business processes.
  • Oracle Data Service Integrator provides a data virtualization layer over your distributed sources, allowing unified reading and updating for heterogeneous data without replicating and moving data.
  • Oracle Enterprise Data Quality provides data quality services to cleanse and deduplicate your records through web services.

Tuesday Jun 19, 2012

ODI 11g – Insight to the SDK

This post is a useful index into the ODI SDK that cross references the type names from the user interface with the SDK class and also the finder for how to get a handle on the object or objects. The volume of content in the SDK might seem a little ominous, there is a lot there, but there is a general pattern to the SDK that I will describe here.

Also I will illustrate some basic CRUD operations so you can see how the SDK usage pattern works. The examples are written in groovy, you can simply run from the groovy console in ODI

Entry to the Platform


Object Finder SDK
odiInstance odiInstance (groovy variable for console) OdiInstance

Topology Objects

Object Finder SDK
Technology IOdiTechnologyFinder OdiTechnology
Context IOdiContextFinder OdiContext
Logical Schema IOdiLogicalSchemaFinder OdiLogicalSchema
Data Server IOdiDataServerFinder OdiDataServer
Physical Schema IOdiPhysicalSchemaFinder OdiPhysicalSchema
Logical Schema to Physical Mapping IOdiContextualSchemaMappingFinder OdiContextualSchemaMapping
Logical Agent IOdiLogicalAgentFinder OdiLogicalAgent
Physical Agent IOdiPhysicalAgentFinder OdiPhysicalAgent
Logical Agent to Physical Mapping IOdiContextualAgentMappingFinder OdiContextualAgentMapping
Master Repository IOdiMasterRepositoryInfoFinder OdiMasterRepositoryInfo
Work Repository IOdiWorkRepositoryInfoFinder OdiWorkRepositoryInfo

Project Objects

Object Finder SDK
Project IOdiProjectFinder OdiProject
Folder IOdiFolderFinder OdiFolder
Interface IOdiInterfaceFinder OdiInterface
Package IOdiPackageFinder OdiPackage
Procedure IOdiUserProcedureFinder OdiUserProcedure
User Function IOdiUserFunctionFinder OdiUserFunction
Variable IOdiVariableFinder OdiVariable
Sequence IOdiSequenceFinder OdiSequence
KM IOdiKMFinder OdiKM

Load Plans and Scenarios


Object Finder SDK
Load Plan IOdiLoadPlanFinder OdiLoadPlan
Load Plan and Scenario Folder IOdiScenarioFolderFinder OdiScenarioFolder

Model Objects

Object Finder SDK
Model IOdiModelFinder OdiModel
Sub Model IOdiSubModel OdiSubModel
DataStore IOdiDataStoreFinder OdiDataStore
Column IOdiColumnFinder OdiColumn
Key IOdiKeyFinder OdiKey
Condition IOdiConditionFinder OdiCondition

Operator Objects


Object Finder SDK
Session Folder IOdiSessionFolderFinder OdiSessionFolder
Session IOdiSessionFinder OdiSession
Schedule OdiSchedule

How to Create an Object?

Here is a simple example to create a project, it uses IOdiEntityManager.persist to persist the object.

import oracle.odi.domain.project.OdiProject;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

project = new OdiProject("Project For Demo", "PROJECT_DEMO")

How to Update an Object?

This update example uses the methods on the OdiProject object to change the project’s name that was created above, it is then persisted.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.finder.IOdiProjectFinder;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

prjFinder = (IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class);
project = prjFinder.findByCode("PROJECT_DEMO");

project.setName("A Demo Project");


How to Delete an Object?

Here is a simple example to delete all of the sessions, it uses IOdiEntityManager.remove to delete the object.

import oracle.odi.domain.runtime.session.finder.IOdiSessionFinder;
import oracle.odi.domain.runtime.session.OdiSession;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

sessFinder = (IOdiSessionFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiSession.class);
sessc = sessFinder.findAll();
sessItr = sessc.iterator()
while (sessItr.hasNext()) {
  sess = (OdiSession)

This isn't an all encompassing summary of the SDK, but covers a lot of the content to give you a good handle on the objects and how they work. For details of how specific complex objects are created via the SDK, its best to look at postings such as the interface builder posting here. Have fun, happy coding!

ODI 11g – Expert Accelerator for Model Creation

Following on from my post earlier this morning on scripting model and topology creation tonight I thought I’d add a little UI to make those groovy functions a little more palatable. In OWB we have experts for capturing user input, with the groovy console we open up opportunities to build UI around the scripts in a very easy way – even I can do it;-)

After a little googling around I found some useful posts on SwingBuilder, the most useful one that I used for the dialog below was this one here. This dialog captures user input for the technology and context for the model and logical schema etc to be created. You can see there are a variety of interesting controls, and its really easy to do.

The dialog captures the users input, then when OK is pressed I call the functions from the earlier post to create the logical schema (plus all the other objects) and model. The image below shows what was created, you can see the model (with typo in name), the model is Oracle technology and references the logical schema ORACLE_SCOTT (that I named in dialog above), the logical schema is mapped via the GLOBAL context to the data server ORACLE_SCOTT_DEV (that I named in dialog above), and the physical schema used was just the user name that I connected with – so if you wanted a different user the schema name could be added to the dialog.

In a nutshell, one dialog that encapsulates a simpler mechanism for creating a model. You can create your own scripts that use dialogs like this, capture input and process.

You can find the groovy script for this is here odi_create_model.groovy, again I wrapped the user capture code in a groovy function and return the result in a variable and then simply call the createLogicalSchema and createModel functions from the previous posting. The script I supplied above has everything you will need. To execute use Tools->Groovy->Open Script and then execute the green play button on the toolbar.

Have fun.

Wednesday Mar 07, 2012

New productivity features in ODI Global KMs, Variable tracking, Groovy Editor

ODI introduces a number of features that make the everyday tasks of ELT developers easier. This blog explains the new features Global Knowledge Modules, Variable and Sequence Tracking, and Groovy Editor.

[Read More]

Friday Jul 08, 2011

ODI SDK: Leveraging ODI Tools with Groovy

How to leverage the ODI SDK to dynamically create a scenario that uses an ODI tool and execute the scenario.[Read More]

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« May 2015