Thursday Jun 07, 2012

ODI 11g – Faster Files

Deep in the trenches of ODI development I raised my head above the parapet to read a few odds and ends and then think why don’t they know this? Such as this article here – in the past customers (see forum) were told to use a staging route which has a big overhead for large files. This KM is an example of the great extensibility capabilities of ODI, its quite simple, just a new KM that;

  1. improves the out of the box experience – just build the mapping and the appropriate KM is used
  2. improves out of the box performance for file to file data movement.

This improvement for out of the box handling for File to File data integration cases (from the companion CD and on) dramatically speeds up the file integration handling. In the past I had seem some consultants write perl versions of the file to file integration case, now Oracle ships this KM to fill the gap. You can find the documentation for the IKM here. The KM uses pure java to perform the integration, using classes to read and write the file in a pipe – it uses java threading in order to super-charge the file processing, and can process several source files at once when the datastore's resource name contains a wildcard. This is a big step for regular file processing on the way to super-charging big data files using Hadoop – the KM works with the lightweight agent and regular filesystems.

So in my design below transforming a bunch of files, by default the IKM File to File (Java) knowledge module was assigned. I pointed the KM at my JDK (since the KM generates and compiles java), and I also increased the thread count to 2, to take advantage of my 2 processors.

For my illustration I transformed (can also filter if desired) and moved about 1.3Gb with 2 threads in 140 seconds (with a single thread it took 220 seconds) - by no means was this on any super computer by the way. The great thing here is that it worked well out of the box from the design to the execution without any funky configuration, plus, and a big plus it was much faster than before,

So if you are doing any file to file transformations, check it out!

Tuesday Mar 27, 2012

ODI 11g – Oracle Multi Table Insert

With the IKM Oracle Multi Table Insert you can generate Oracle specific DML for inserting into multiple target tables from a single query result – without reprocessing the query or staging its result.

When designing this to exploit the IKM you must split the problem into the reusable parts – the select part goes in one interface (I named SELECT_PART), then each target goes in a separate interface (INSERT_SPECIAL and INSERT_REGULAR).

So for my statement below…

/*INSERT_SPECIAL interface */ insert  all
when 1=1 And (INCOME_LEVEL > 250000) then

/* INSERT_REGULAR interface */ when 1=1  then
/*SELECT*PART interface */ select   
where    (1=1)

Firstly I create a SELECT_PART temporary interface for the query to be reused and in the IKM assignment I state that it is defining the query, it is not a target and it should not be executed.

Then in my INSERT_SPECIAL interface loading a target with a filter, I set define query to false, then set true for the target table and execute to false. This interface uses the SELECT_PART query definition interface as a source.

Finally in my final interface loading another target I set define query to false again, set target table to true and execute to true – this is the go run it indicator!

To coordinate the statement construction you will need to create a package with the select and insert statements. With 11g you can now execute the package in simulation mode and preview the generated code including the SQL statements.

Hopefully this helps shed some light on how you can leverage the Oracle MTI statement. A similar IKM exists for Teradata. The ODI IKM Teradata Multi Statement supports this multi statement request in 11g, here is an extract from the paper at

Teradata Database offers an SQL extension called a Multi-Statement Request that allows several distinct SQL statements to be bundled together and sent to the optimizer as if they were one. Teradata Database will attempt to execute these SQL statements in parallel. When this feature is used, any sub-expressions that the different SQL statements have in common will be executed once, and the results shared among them.

It works in the same way as the ODI MTI IKM, multiple interfaces orchestrated in a package, each interface contributes some SQL, the last interface in the chain executes the multi statement.

Friday Mar 02, 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!

Tuesday Feb 28, 2012

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.

ODI 11g - Scripting the Model and Topology

Scripting is the ideal mechanism to automate start up and teardown for repeated tasks and those that you just want to automate. Here are a couple of more illustrations of how to easily construct a model in ODI, the script will also create all of the topology objects. The script uses two methods; createLogicalSchema and createModel. The createLogicalSchema creates the logical schema, data server, physical schema and logical schema to physical schema mapping via a context all from one function call.

The signature of these methods looks like this;


contextCode – the ODI code for the context used to map the logical schema to the physical

technologyCode – the ODI code for the technology

nameForLogicalSchema – the name for the logical schema to create

NameForDataserver – the name for the data server to create

userNameForAuthentication – the username for the connection to the data server

passwordForAuthentication – the password for the connection to the data server

urlForAuthentication – the URL for the connection to the data server

driverForAuthentication – the JDBC driver for the connection to the data server

schemaForAuthentication – the schema to use for the ODI physical schema


logicalSchemaObject – the ODI logical schema object (instance of ODILogicalSchema)

contextCode – the ODI context code for reverse engineering

nameForModel – the name for the model to create

codeForModel – the code for the model to create

So with these two methods or variations of them you can easily construct your topology objects and models. For example the call below creates a new model named ORACLE_MODEL and all of the topology objects that will allow me to go straight to reverse engineering when the script has been run.

lschema = createLogicalSchema("GLOBAL", "ORACLE", "ORACLE_EBS", "ORACLE_HQLINUX_DEV", "SCOTT",

    ObfuscatedString.obfuscate("<password>"), "jdbc:oracle:thin:@localhost:1521:orcl", "oracle.jdbc.OracleDriver", "SCOTT")

createModel(lschema, "GLOBAL", "ORACLE_MODEL", "ORACLE_MODEL")

Here is the source code for the script

import oracle.odi.domain.util.ObfuscatedString;
import oracle.odi.domain.model.OdiModel;
import oracle.odi.domain.topology.OdiLogicalSchema;
import oracle.odi.domain.topology.OdiPhysicalSchema;
import oracle.odi.domain.topology.OdiDataServer;
import oracle.odi.domain.topology.OdiContext;
import oracle.odi.domain.topology.OdiTechnology;
import oracle.odi.domain.topology.OdiContextualSchemaMapping;
import oracle.odi.domain.topology.AbstractOdiDataServer;
import oracle.odi.domain.topology.finder.IOdiContextFinder;
import oracle.odi.domain.topology.finder.IOdiTechnologyFinder;

def createLogicalSchema(contextCode, techCode, schName, dataserverName, userName, password, url, driver, schema) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)

  contextFinder = (IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class);
  context = contextFinder.findByCode(contextCode);

  techFinder = (IOdiTechnologyFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiTechnology.class);
  tech = techFinder.findByCode(techCode);

  lschema = new OdiLogicalSchema(tech, schName)
  dserver = new OdiDataServer(tech, dataserverName)
  con = new AbstractOdiDataServer.JdbcSettings(url, driver)
  pschema = new OdiPhysicalSchema(dserver)
  cschema = new OdiContextualSchemaMapping(context, lschema, pschema)

  return lschema

def createModel(lschema, contextCode, modName, modCode) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)

  contextFinder = (IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class);
  context = contextFinder.findByCode(contextCode);

  mod = new OdiModel(lschema, modName, modCode)
  return mod

lschema = createLogicalSchema("GLOBAL", "ORACLE", "ORACLE_EBS", "ORACLE_HQLINUX_DEV", "SCOTT", ObfuscatedString.obfuscate("<password>"),
"jdbc:oracle:thin:@localhost:1521:orcl", "oracle.jdbc.OracleDriver", "SCOTT")

createModel(lschema, "GLOBAL", "ORACLE_MODEL", "ORACLE_MODEL")

Have fun scripting!

Friday Feb 24, 2012

ODI 11g - Getting Scripting with Groovy

The addition of the groovy interpreter to the ODI designer now let’s you easily script any tasks that you repeatedly perform. The documentation has illustrations here, so using the ODI 11g SDK you can encapsulate common tasks in simple groovy functions.

Groovy can be executed by executing a script, you can create a new one or open an existing groovy script;

You will then see a new groovy window appear in the IDE plus the execute green button is enabled on the toolbar.

I have taken the script defined here and shown below in its more minimal groovy form and parameterized the script in a groovy function ‘createProject’. I can then call createProject with whatever values for the project and folder I wish to create.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.OdiFolder;

def createProject(projectName, projectCode, folderName) {
  txnDef = new DefaultTransactionDefinition();
  tm = odiInstance.getTransactionManager()
  txnStatus = tm.getTransaction(txnDef)
  project = new OdiProject(projectName, projectCode)
  folder = new OdiFolder(project, folderName)

createProject("EDW Staging", "EDW", "Initialization")

So in the UI if I execute as follows;

After executing the script I refresh the Designer tree and see my new project.

Thursday Feb 09, 2012

ODI 11g – Interface Builder

In the previous blogs such as the one here I illustrated how to use the SDK to perform interface creation using various auto mapping options for generating 1:1 interfaces either using positional based matching, like names ignoring case and so on. Here we will see another example (download showing a different aspect using a control file which describes the interface in simple primitives which drives the creation. The example uses a tab delimited text file to control the interface creation, but it could be easily taken and changed to drive from Excel, XML or whatever you wanted to capture the design of the interface.

The interface can be as complete or incomplete as you’d like, so could just contain the objects or could be concise and semantically complete.

The control file is VERY simple and just like ODI requests the minimal amount of information required. The basic format is as follows;

Directive Column 2 Column 3 Column 4 Column 5
source <model> <datastore>    
   can add many        
target <model> <datastore>    
mapping <column> <expression>    
   can add many        
join <expression>      
   can add many        
filter <expression>      
    can repeat many        
lookup <model> <datastore> <alias> <expression>
   can add many        

So for example the control file below can define the sources, target, joins, mapping expressions etc;

source    SCOTT    EMP
source    SCOTT    DEPT
mapping    ENAME    UPPER(EMP.ENAME)
mapping    DEPTNO    ABS(EMP.EMPNO)
filter    EMP.SAL > 1
mapping    COMM    ABS(BONUS.COMM)

When executed, this generates the interface below with the join, filter, lookup and target expressions from the file.

You should be able to join the dots between the control file sample and the interface design above.

So just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceBuilder;

java –classpath <cp> OdinterfaceBuilder jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK DEMO1 <

The interface to be created is passed from the command line. You can intersperse other documentation lines between the control lines so long as the control keywords in first column don’t clash.

Anyway some useful snippets of code for those learning the SDK, or for those wanting to capture the design outside and generate ODI Interfaces. Have fun!

Wednesday Feb 08, 2012

ODI 11g – More accelerator options

A few more options added into the interface accelerator that I blogged about earlier here in initial post and a later one here. Added options for doing position based and case sensitive/insensitive options. These were simple changes added into the auto map class. You can now find the latest updates below;

So just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceAccelerator;

java –classpath <cp> OdinterfaceAccelerator jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK <icontrol.csv

In the automapper I created a couple of options that can drive the accelerator, it supports;

  • positional based match (match columns by position from source to target)
  • exact match case sensitive  (match EMPNO with EMPNO, but not empno with EMPNO)
  • exact match case insensitive (match EMPNO with empno)
  • src/target ends with sensitive/insensitive (match PFX_empno with empno/EMPNO)
  • src/target starts with sensitive/insensitive (match empno_col with empno/EMPNO)

Note, you can also use the “diagrams” in the models to greatly accelerate development if source and targets have the same structure – if not then you have to go through the SDK route above if you want to accelerate.

Tuesday Jul 05, 2011

ODI 11g - Pipelines

There’s a write up of some pushing the envelope on ODIEE 11g capabilities here that I presented parts of last week at ODTUG KScope 11 in Long Beach, California.

The ODTUG Kaleidescope/KScope is certainly an awesome conference with gurus on all fronts willing to talk about their cherished areas. Next year its headed for San Antonio, Texas – Long Beach was fun and it was great to meet all the folks that I did!

Saturday May 21, 2011

ODI - Extending the Interface Accelerator

The post here illustrates how to use the ODI 11g SDK to extend the classes provided to customize your own auto mapping capabilities;

Thursday May 12, 2011

ODI 11g - Getting Groovy with ODI

Here we will see how you can use the groovy interpreter to automate, automate, automate (with no compile,compile,compile). When you get into large enterprise configurations of software, automation is the name of the game. The ODI 11g SDK as I blogged here lets you interact with pretty much the entire ODI set of metadata. The example I posted was a (or for post here) is a java class that needs compiled and then executed. Rather than compiling and executing (and needing to understand much of the build side of java), let’s look at an alternative….

In OWB, there is an interpreter/interactive shell called OMBPlus and language that lets you automate the construction of artifacts and get smart with the tool. With ODI you can leverage the groovy interpreter for example, which lets you interact with java objects. You’ll need to download groovy and install. When groovy is installed you can then either execute the groovy process and process a groovy script as input or use the groovysh shell/interpreter. The groovy scripts can be scripts which use the ODI 11g SDK and interact with ODI.

For example the command below, takes the groovy script interfaceAccelerator.groovy or post here (which is a groovy version of the java class file previously used) as input, and the tabs.txt is piped in the standard input stream.

groovy interfaceAccelerator.groovy < tabs.txt

The groovy script is a mixture of groovy commands and ODI SDK calls to generate the interfaces in ODI.

The CLASSPATH including all the ODI jars is one thing that needs set up prior to executing the groovy script above, I just included all jars in the oracledi.sdk\lib directory.

There was a small change in the transaction handling APIs after production 11g ODI, hence the different versions of the groovy and java scripts.

Lots of possibilities as you can imagine! There are a few cool things we can extend the actual interfaceAceelerator to do...regardless of groovy or java, but just some more useful capabilities, more to come.

Tuesday Nov 16, 2010

ODI 11g – Simple, Flexible, Powerful


SQL as a source, inline SQL, SQL override...want to know more ....? A couple of specific enhancements in the code generation capabilities of ODI 11g, opens the door to an area of code generation which provides great benefit from the ability to have SQL as a source to the construction of even better performing code to better interface designs and accelerators.

So what are they? The two key changes are;

  • support for sub-select to chain, when possible, multiple interfaces into one. With ODI 11g the code generation capabilities of ODI were extended in order to build larger more complex interface designs without the necessity of staging via temporary interfaces. There have already been blog posts on this subject, for example see Uli Bethke's post here.
  • a property in the KM definition to define current command for sub-select. A new property defined on a command in a KM to indicate to the ODI code generator that this command should be used for generating the SQL statement for a sub-select. This one is the key change in addition to the support for sub-select that opens the door. Is the penny dropping yet?

These two together open the door to building operator(or transformation)-like KMs within the bounds of the information that can be defined by a temporary interface and the options on the KM.

Let's see...

Simple Example - The SQL as Source IKM

Let's build a very simple example that is similar to Informatica's SQL override capability or inline views - SQL as source. So to the new breed of code template an IKM with the SQL code generator to be used for sub-query. We define an IKM and have the target technology just now set to Oracle;


Now comes the interesting part, we can define a command to fulfill the SQL generation. This can be as simple as actually just being the SQL code (SQL as source) or something a little more complex such as fulfilling a transformation such as a pivot or table function.

Let's check out the command definition, its very simple - the SQL representing the subquery will be an option on the IKM. Don't worry you won't have to build this KM its on the code samples already, you can download the KM here. (I built it with Oracle as a target technology at first then changed for all using undefined technology). Also it will become blatantly obvious how its used when you see a simple example.


From the above you see 2 things; 'Use current command for Derived-Table sub-select statement' is checked, a bit of a mouthful, basically ODI will use THIS command in the IKM for generating the SQL when a temporary interface is used in an interface and the sub-select check box is selected.

The IKM has an option VIEWQUERY. When the IKM is used in an interface design this is where you will define the SQL as a source.


That's a peek inside the IKM, we can now build temporary interfaces that are essentially inline view designs that will define the view columns (the target datastore columns in the temporary interface) and the SQL query which is to be used to realize the interface. With ODI we can go the extra mile and use the ODI reference APIs to ensure the SQL is portable also across systems so that we don't hard-wire access, schemas etc.

Building the inline view definition

Like any view (in database terms) we define a signature - the columns to be projected are defined on the temporary target datastore on an ODI temporary interface, below you see I have defined a comment (--) for the mapping expression and the indicator is for the target;


The execution unit is assigned the SQL_as_Source IKM, where the SQL query is then defined on the VIEWQUERY option.


Above I have hard-wired the query, as mentioned we can use ODI reference APIs to get the physical name from the datastore in a flexible manner. So the query could be defined using the odiRef.getObjectName which makes the statement much more flexible and provides flexibility via the context to use different physical schemas for example.


That is the SQL as a source or inline view defined. We can reuse this definition many times in different interfaces. Let's see how it is used.

Using the inline view in an interface

The temporary interface (our inline view definition) can be dropped into other interfaces and used like any other interface in ODI, now in 11g as we have seen from other posts we can include the interface as a sub-select. The INLINE_VIEW can be joined  with other tables etc and used like any other interface artifact.


Above I am using the INLINE_VIEW temporary interface and have selected the Use Temporary Interface as Derived Table (Sub-Select), since the tables used by the view are from the same server as the BONUS table.

So what code gets generated?

Simulating the code in ODI 11g we see the SQL statement we defined in our temporary interface nested inside the overall code (this was using the had-wired SQL rather than the odiRef'd version).


This KM lets you then use a temporary interface as your SQL Query or inline view and provides a great mechanism to balance fully fledged design using interfaces and integrating custom arbitrary SQL.

You can download the SQL as Source KM from the code samples here on The example illustrates a capability that we can exploit for other cases - more to come!


Thursday Jan 03, 2008

11g Heterogeneous Agent

[Read More]

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.


« June 2016