Friday Nov 20, 2015

Using ODI Exchange to Share ODI Objects

In the previous blog post in this series we had a look at the new ODI Exchange functionality available in ODI 12.2.1 and how to use it. In this blog post we will be focusing on how to share your global ODI objects with the rest of the ODI community.

Step 1: Prepare Global ODI Objects

  1. Connect to the ODI Repository containing the desired Global Knowledge Modules or User Functions (Note: Only Global objects are supported)
  2. In the Designer Navigator expand the Global Objects accordion to select the desired object

  3. Export the Global KM or User Function via Context Menu “Export…”

  4. Specify the Export directory and Export name (Note: Export key is NOT supported)

  5. Press OK (and answer Yes to Export Key warning if shown)
  6. Press OK on Export finished dialog
  7. Obtain the GlobalId by examining the export file content.  The first <Object> should contain the exported object definition (class=”com.sunopsis.dwg.dbobj.SnpUfunc” or class=” com.sunopsis.dwg.dbobj.SnpTrt”).  A few more lines down should be one called <Field name=”GlobalId”…>…[CDATA[##-##-##-##]…  Make note of the value of the GlobalId you will need it again!  (Hint: you should probably copy it to the clipboard) 

    Here is an example:
    <?xml version="1.0" encoding="ISO-8859-1"?>
    <Admin …
    <Encryption …
    <Object class="com.sunopsis.dwg.dbobj.SnpUfunc">
      <Field name="ExtVersion" …
      <Field name="FirstDate" …
      <Field name="FirstUser" …
      <Field name="GlobalId" … [CDATA[99f49a0e-096d-407f-b423-a7f0fbb258da]]>
      <Field name="GroupName" …
      <Field name="IndChange" …

Step 2: Prepare Update Bundle

  1. Place the exported Global ODI Objects into an empty directory
  2. Create under that directory a “META-INF” sub-directory

  3. Create a file called “bundle.xml” in the “META-INF” directory as follows:
    • [mandatory] globalId: MUST exactly match the one in the export xml
    • [mandatory] name: the ‘name’ of the object which will be displayed by Check For Updates
    • [mandatory] version: the version number (in the form #.#[.#.#])
    • author: the value to show as “Author”, this information will appear within ODI Studio so make sure to fill it in!
    • authorUrl: the value to show as the “Author Url” (http://... ) , this information will appear within ODI Studio so make sure to fill it in!
    • description: the description to show in the description field, this information will appear within ODI Studio so make sure to fill it in!
    You can also download a sample bundle.xml file from here: link

  4. Utilize a ‘zip’ tool (zip, winzip, 7zip, etc…) to create a single .zip file containing the update contents in the ‘root’ directory and META-INF/bundle.xml
    Ex: in Linux:    zip -9 –r {bundleZipFile}.zip *

Step 3: Upload the bundle zip file to the Oracle Data Integration project on

  1. Go to and create an account if you don't have one yet

  2. Click on Join This Project
  3. Click on Downloads in the Project Features menu on the left hand-side

  4. Click on ODI

  5. Click on Knowledge Modules

  6. Click on 12c

  7. Finally click on Add File / Link for Download, specify the file to upload and put a title/description. If you don’t see 'Add File / Link for Download' you need to log into

  8. Click on Upload to complete the upload of the object on When you’re done click on Done.

When a new object is added to we will be automatically notified and will add it to the ODI Exchange. Thanks for contributing!!

If you want to know more about all the new features included in Oracle Data Integrator 12.2.1 have a look at the New Features document on OTN.

Wednesday Nov 18, 2015

Introducing Oracle Data Integrator (ODI) Exchange

Oracle Data Integrator 12.2.1 was recently released and introduces many exciting new features including the ODI Exchange. In a nutshell, ODI Exchange allows Oracle and our users to easily share global ODI objects (Knowledge Modules and User Functions) with the rest of the community through Update Centers. There are 2 Update Centers: one on which hosts unsupported but very valuable ODI objects shared by our community and one on the Oracle Technology Network (OTN) that will be used in the future to ship supported ODI objects.

Did you know? Our Oracle Data Integration project on already hosts dozens of Knowledge Modules, User Functions and more (SDK samples, Open Tools, Technologies etc.). Now with ODI Exchange most of this content is readily available from ODI Studio!

To get started simply open up ODI Studio 12.2.1, go to Help and click on Check for Updates

Then select the Update Centers you want to connect to:

  • Official Oracle Update Center: Hosted on OTN, this Update Center will be used to ship officially supported ODI objects from the ODI Development team. This Update Center does not contain any objects at the moment
  • Customers and Partners Update Center: Hosted on, this Update Center provides non certified but yet very valuable ODI objects created by customers, partners and Oracle employees

Click Next and then select one or more objects you would like to download.

It is also possible to filter the results using the Search box at the top. We can type ‘big data’ for example to download a Loading Knowledge Module that leverages Oracle Big Data SQL.

Once the objects are selected click Next and ODI will download the files locally. Finally click on Finish to complete the process.

The objects will be imported into your ODI repository and will be available in Designer in the Global Objects accordion.

In the next blog post of this series we will look at how you can share your global ODI objects with the rest of the ODI community!

If you want to know more about all the new features included in Oracle Data Integrator 12.2.1 have a look at the New Features document on OTN.

Friday Nov 06, 2015

Oracle Data Integrator 12.2.1 - Managing Versions in Apache Subversion

The latest Oracle Data Integrator (ODI) release, 12.2.1, came out with brand new lifecycle management features that allows you to use Apache Subversion as the external Version Control System (VCS) for creating and managing ODI objects versions. It does not just stop there but also facilitates the creation of tags, manage branches for parallel development, and create Deployment Archives for promoting ODI objects from Development to Production.

In this article I will talk about various version management operations available in ODI Studio for maintaining, viewing, comparing and restoring versions from Apache Subversion.

How does it work?

ODI Studio remains the sole User Interface for performing any such versioning operations. It directly communicates with the external Version Control System and presents all the needed information within ODI Studio user interface. For example to create a version, an object is exported into XML file which is then checked in into Version Control System. Conversely to restore an object, its XML file is first checked out from Version Control System and then imported back into ODI repository.

Configuring Apache Subversion

In order to use Subversion, we first need to setup its connectivity in ODI Studio in just a couple of steps.

  • Edit Connection - This screen allows to setup subversion URL and user credentials as per the selected authentication types. There are a number of authentication types supported for setting up such connectivity – HTTP, SSH, SSL etc.

  • Configure - This screen allows you select the subversion project and the Trunk or Branch under it for maintaining ODI objects. If you don’t have the subversion project or branch already created then you can create them right from this screen at the time of configuration.

There are a couple of other useful options configured here

  1. Auto version – Set this if you want to make ODI to automatically create version of a versioned object whenever it is saved. By enabling you can make sure that the subversion copy is always in sync with your repository copy of a versioned object. But it may impact your save performance due to underlying export and checkin.
  2. VCS Key – Configure the VCS key that is used to encrypt the sensitive content while exporting the object in to XML file. For example the passwords in a Data Server definition should not be exported in plain text when the Data Server is exported into XML. Such passwords are encrypted and decrypted using the VCS Key configured here.

Managing Versions

The operations for managing versions are available at two levels.

  • At Studio menu level - This menu lists the subversion operations that are not specific to a particular object and can be performed on a group of objects.

  • In the object context menu (Right click menu) - These options allow you to perform various subversion operations on that particular object.

Adding objects to Apache Subversion

Once you decide what objects need to be version controlled, then you can add them to subversion individually or in group.

Adding Single object

 It is performed through object's context menu

There are couple of convenience features available here

  1. All the necessary parent hierarchy is automatically added with child object so that developer need not do it individually. For example when you add a mapping for the first time the corresponding folder and project that contains this mapping are also added automatically.
  2. You can add selected or all children objects along with the parent object. For example when you add a folder to subversion then the packages, procedures, mappings etc present under the folder can also be added along with it in the same operation.

Adding group of objects

The “Add Non-versioned Objects to VCS” option in ODI Studio and object context menu presents you the list of objects that are not yet added to subversion and allows you to add them to subversion in group. At studio menu level it list all such objects in the entire repository whereas at object context menu level it lists all the non-versioned objects under that object and is applicable for only the container objects such as projects, folder etc.

Indicator for Version Controlled Objects

A version controlled object can be easily identified by the subversion indicator presence next to its icon. Notice the indicator next to “Version Control” folder in below screenshot indicating that folder is version controlled whereas the other folder is not yet added to subversion.

Creating Object Versions

New versions of a version controlled object can be created through the context menu option – "Create VCS Version"

When a version is created at the container object level – such as project, folder, model etc – then following operations are also performed along with creating version of the the container object.

  • Versions are created for all version controlled children that are modified since last version. Note: It does not affect any non-versioned child.
  • Any child object deletion, rename or move is also pushed to subversion.

Viewing Version History

You can view version history for an object within the ODI Studio itself. It pulls object's version history from subversion and presents it in a couple of formats. Each of the formats provides different filtering/search options to easily locate a particular version.

  • Tabular format – through context menu option “Version History”
  • Tree format – through context menu option “Version Tree”

Comparing Versions

You can compare a version in object’s version history with another version or with the current copy present in repository. The difference between the two is presented side by side in a special window. There are toolbar buttons provided for easy navigation through the changes. The changes are color coded to easily differentiate the added, deleted or modified properties.

Restoring Objects from Subversion

You can restore an object's version from subversion into ODI repository. There are a couple of different situations in which you want to perform such restore

  • Restoring deleted object – You deleted an object but later realized that it is needed and want to restore it. You can view the list of deleted objects and restore them through the studio menu option Team->Subversion->Restore Deleted Object

  • Restoring an older version – If you messed up the latest copy of the object in the repository and wants to restore an older version then you can do it from objects context menu or from version history dialog.

The “Restore with Merge” check-box allows overwriting or merging the version changes into the existing repository copy. For a container object such merging can be applied to the child objects as well.


ODI 12.2.1 provides seamless integration with Apache Subversion. All the important version management operations in subversion are now directly available from the ODI studio. Developers can create, view, compare and restore versions from Apache Subversion with the same ease as they can do any other ODI operations.

Stay tuned for the upcoming articles covering Branch Management and Release Management using ODI 12.2.1 lifecycle features.

Wednesday Oct 07, 2015

More on Leveraging Oracle Data Integrator (ODI) for Cloud Applications

Take a look at this week’s A-Team Blog post: Need to Integrate your Cloud Applications with On-Premise Systems… What about ODI? This blog is tightly coupled to the recent blog post: A Universal Cloud Applications Adapter for ODI.

First you learned about the simplicity of leveraging Oracle Data Integrator (ODI) with all emerging technologies in the world of cloud computing. Now read about which Cloud JDBC drivers will allow you to expand your data integration initiatives to include PaaS and SaaS connectivity.

For more A-Team reads on ODI, browse through the A-Team Chronicles.

Tuesday Aug 04, 2015

Simplicity in Leveraging Oracle Data Integrator for Cloud Applications

Check out last week’s A-Team Blog post… A Universal Cloud Applications Adapter for ODI

Learn about the simplicity of leveraging Oracle Data Integrator (ODI) with all emerging technologies in the world of cloud computing!

For more A-Team reads on ODI, browse through the A-Team Chronicles.

Tuesday Jun 16, 2015

ODI - Hive DDL Generation for Parquet, ORC, Cassandra and so on

Here you'll see how with some small surgical extensions we can use ODI to generate complex integration models in Hive for modelling all kinds of challenges;

  • integrate data from Cassandra, or any arbitrary SerDe
  • use Hive Parquet or ORC storage formats
  • create partitioned, clustered tables
  • define arbitrarily complex attributes on tables

I'm very interested in hearing what you think of this, and what is needed/useful over and above (RKMs etc) 

When you use this technique to generate your models you can benefit from one of ODI's lesser known but very powerful features - the Common Format Designer (see here for nice write up). With this capability you can build models from other models, generate the DDL and generate the mappings or interfaces to integrate the data from the source model to the target. This gets VERY interesting in the Big Data space since many customers want to get up and running with data they already know and love.

What do you need to get there? The following basic pieces;

  • a Hive custom create table action here.
  • flex fields for external table indicator, table data format, attribute type metadata get groovy script here

 Pretty simple right? If you take the simple example on the Confluence wiki;

  • CREATE TABLE parquet_test (
  •    id INT,
  •    str STRING,
  •    lst ARRAY<STRING>,
  •    strct STRUCT<A:STRING,B:STRING>) 

...and think about how to model this in ODI, you'll think - how do I define the Parquet storage? How can the complex types be defined? That's where the flex fields come in, you can define any of the custom storage properties you wish in the datastore's Data Format flex field, then define the complex type formats.

In ODI the Parquet table above can be defined with the Hive datatypes as  below, we don't capture the complex fields within attributes mp, 1st or stct. The partitioned column 'part' is added in to the regular list of datastore attributes (like Oracle, unlike Hive DDL today);

Using the flex field Data Format we can specify the STORED AS PARQUET info, the table is not external to Hive so we do not specify EXTERNAL in the external data field;

This value can have lots of ODI goodies in it which makes it very versatile - you can use variables, these will be resolved when the generated procedure containing the DDL is executed, plus you can use <% style code substitution. 

The partitioning information is defined for the part attribute, the 'Used for Partitioning' field is checked below;

Let's take stock, we have our table defined as Parquet and we have the partitioning info defined. Now we have to define the complex types. This is done on attributes mp, 1st and stct using the creatively named Metadata field - below I have fully defined the MAP, STRUCT and ARRAY for the respective attributes;

Note the struct example escapes the : as the code is executed via the JDBC driver when executing via the agent and ':' is the way of binding information to a statement execution (you can see the generated DDL further below with the ':' escaped). 

With that we are complete, we can now generate DDL for our model;

This brings up a dialog with options for where to store the procedure with the generated DDL - this has actually done a base compare against the Hive system and allowed me to selected which datastores to create DDL for or which DDL to create, I have checked the parquet_test datastore;

Upon completion, we can see the procedure which was created. We can chose to further edit and customize this if so desired. We can schedule it anytime we want or execute.

Inspecting the Create Hive Table action, the DDL looks exactly as we were after when we first started - the datastore schema / name will be resolved upon execution using ODI's runtime context - which raised another interesting point! We could have used some ODI substitution code <% when defining the storage format for example;

As I mentioned earlier you can take this approach to capture all sorts of table definitions for integrating. If you want to look on the inside of how this is done, check the content of the Hive action in image below (I linked the code of the action above), it should look very familiar if you have seen any KM code and uses the flex fields to inject the relevant information in the DDL;

This is a great example of what you can do with ODI and how to leverage it to work efficiently. 

There is a lot more that can be illustrated here including what the RKM Hive can do or be modified to do. Perhaps creating the datastore and complex type information from JSON, JSON schema, AVRO schema or Parquet is very useful? I'd be very interested to hear your comments and thoughts. So let me know....

Tuesday Jun 09, 2015

Oracle Data Integrator Journalizing Knowledge Module for GoldenGate Integrated Replicat Blog from the A-Team

As always, useful content from the A-Team…

Check out the most recent blog about how to modify the out-of-the-box Journalizing Knowledge Module for GoldenGate to support the Integrated Replicat apply mode.

An Oracle Data Integrator Journalizing Knowledge Module for GoldenGate Integrated Replicat


Tuesday May 12, 2015

ODI 12c - Improving Usability of KM recipes

This post is all about reducing user errors and improving usability surrounding definition of Knowledge Modules and their usage. Knowledge Modules are all about encapsulating recipes - every great cookbook has lots of recipes and some are based on common techniques and ingredients. ODI Knowledge Modules are data integration recipes - they define how to access, transform and store information based on the directions in the KM. There are a few usability improvements in the recent release around both the KM definition and usage of the KM that make for an improved user experience. I've seen many KMs over the years where its many things to many people and there are a bundle of options that expose all facets for every path possible in the KM - the user has to read the description and follow the instructions.

The first improvement I'll mention is the KM (and procedure) option type of 'Choice'. Not exactly rocket science here I know, but an addition that greatly helps usage of a KM that may do more than one thing. Let's take the example of a KM that can make different .....pizzas. In the past you would have an option field which was a string based value where the user would type either margerita or pepperoni to drive a path within the KM implementation, users of the KM would have to know that those were the accepted option values and they'd have to type it in properly (otherwise things would go wrong). So now the options can be specified as the 'Choice' type, see below where in the IKM we capture the recipe type as a choice.

The choices can be defined in the default value field, below the recipe is going to either create margherita pizza or pepperoni- these are the only two choices and the default is margherita;

Then I can define all the rest of the options, let's say the pizza needs flour, oil, salt, yeast and pepperoni needs... pepperoni of course and margherita needs tomatoes and basil - so some of the options are applicable to both types and some are only applicable to the specific one. Prior to this release when the KM is used you would see all of these option values and you'd be reading the description 'only set basil if you are making margherita' and so on. Another feature has been added to improve this area. Below you can see all of the options....

One column was snipped out of the image - the condition expression. This is a groovy expression to determine whether the option is shown. So now we can say only display basil when margherita pizza is the recipe type or only display pepperoni when pepperoni is the recipe type. We see below the options only applicable to the recipe type are displayed - anything common has no condition expression.