Thursday Jul 07, 2011

ODI SDK: How to Automate Changes to Interfaces

Use the ODI SDK to list interfaces in the repository and perform changes in the interfaces

[Read More]

ODI SDK: Browsing The Repository to List All Interfaces

How to leverage the ODI SDK to list all projects, folders, subfolders and interfaces.[Read More]

Saturday May 21, 2011

Extending the Interface Accelerator

Carrying on the automate, automate theme, here’s an illustration of some really useful capabilities using the ODI 11g SDK which make it more powerful, carrying on from the post on the simple accelerator that I posted here. In this posting I had a simple control file that mapped some table to some other table that were defined in in the control file. The auto mapping as-in the ODI UI itself matches on exact names of columns.

What if we want to do something a little different and automate? For example say some standard suffixes or prefixes have been added to the column names what can we do? With the previous posting no columns will be mapped. This was because I used the auto mapping provided out of the box (see the following line in the file from the post mentioned;

interactiveHelper.performAction(new InterfaceActionSetTargetDataStore(targetDatastore, new MappingMatchPolicyLazy(), new AutoMappingComputerLazy(), new AutoMappingComputerLazy(), new TargetKeyChooserPrimaryKey()));

Here is where we can refine this by specializing the auto mapping class that ODI provides, I will create one called this class will refine the method performAutoMapping which will construct the mapping for a target column – it can even call the super classes method and reuse the default exact name matching if needed.

interactiveHelper.performAction( new InterfaceActionSetTargetDataStore(targetDatastore, new MappingMatchPolicyLazy(), new MyRegexpAutoMappingComputer( MyRegexpAutoMappingComputer.MatchTypes.TGTENDSWITH), new MyRegexpAutoMappingComputer( MyRegexpAutoMappingComputer.MatchTypes.TGTENDSWITH), new TargetKeyChooserPrimaryKey()));

You can see in the above call I now use a new auto mapping class and pass in the TGTENDSWITH enumeration to do matching (so a source call named EMPNO will match with a target name of STG_EMPNO). The new files are available here;

The class could be enhanced to support more complex stuff, hopefully you see how this works and an insight into how you can extend the SDK provided and add even more value.

Monday Apr 25, 2011

Accelerating development via the Oracle Data Integrator SDK

A nice post and example of ODI 11g SDK use by David Allan can be seen on this blog.

David writes:


Often using ANY tool there are scenarios where there is a lot of grunt work, imagine Microsoft tools like Excel without VB and macros to accelerate and customize those boring repetitive tasks. Data integration and ETL design is exactly the same, the tool needs to expose an SDK to a base platform that you can use to make your life easier. Something to automate the grunt work that is common and very repetitive. The ODI 11g SDK let's you script these kind of repetitive tasks. As an aside the ODI common format designer (see this post here) has a way for migrating like named objects, however using the SDK let's you control much much more.

To illustrate I have created a simple interface construction accelerator that you can download (, the accelerator generates ODI interfaces from a control file that defines the interface name, the source and the target - simple and a nice example for demo purposes. If you look at the java code, it is very basic (no pun intended).  It literally is a dozen lines of code. The image below illustrates the java program interfaceAccelerator using the ODI 11g SDK to take as inputs the configuration of the connection details and a control file specify the source to target datastore mappings.


The code when called has a bunch of command line parameters shown below and the standard input stream is the interface control file, so the command line looks like;

java -classpath <cp> interfaceAccelerator <url> <driver> <schema> <pwd> <workrep> <odiuser> <odiuserpwd> <project> <folder> < <control_file>

the control file provided in the standard input stream needs to be a comma delimited file with the following structure

    • interface_name,source_model,source_table,target_model,target_table
    • ...

for example a sample command line using an Oracle repository could be

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


the interfaces will be created in the folder SDK and the project code is STARTERS. The icontrol.csv file used above was (remember the format is interface_name,source_model,source_table,target_model,target_table, this is just what I happened to use in this simple demo program);


You can created as many interfaces from this driver control file as you desire, the interface generated will map from the source table to the target table and use ODI's auto mapping to perform column level mapping of the source to target table, it will also create default source sets and use the default KM assignment. So you get a pretty useful set of stuff as a basis here.

The interfaces generated whilst executing this accelerator look like the following, the table to table map with all of the like-named columns mapped, the physical flow configured with defaults KMs!


You can take this code and customize to make it fit your needs or send in comments on how to do things. In summary if you are finding you desire ways of tuning your work to make using ODI even more productive, then you should look into the ODI 11g SDK and see if you can automate, automate, automate.



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


« March 2015