ODI 11g New Mapping and Interface Features – Part 1

June 13th, 2011 by

About a year or so ago I posted an entry on this blog highlighting the new architectural features of Oracle Data Integrator 11g. At the time, I talked about the new user interface, integration with WebLogic Server and Enterprise Manager, and how the Repository Creation Utility could be used to create the master and work repositories. As well as these high-level new features though, there were a number of more detail-level features that could prove useful for developers creating data integration routines with the tool.

One such new feature is the ability to join together two or more data sets using set operators, such as UNION, INTERSECT or MINUS. To take an example, consider a situation where you have some order data sitting in a table that you want to load into a staging area, like this:

Sshot 3

Now imagine that you had some additional orders data, but this time it was sitting in a file, containing the same columns but obviously stored separately to the table data. You could read from the two sources over two separate interfaces, or in ODI 11g you can press the Add/Remove Dataset… button, like this:

Sshot 4

Pressing this button brings up a dialog that lets you give each dataset a name, add a new dataset reference and then select the set operator to combine them, from the list of UNION, UNION ALL, MINUS and INTERSECT.

Sshot 5

Once you’ve added this second dataset reference, ODI adds a tab to the bottom of the Source Datastore canvas, and you can then drag and drop the file datasource into your interface, with the two sources then being combined through your chosen set operator.

Sshot 6

Another new feature is Temporary Interfaces. If you saw my postings on the release of the Oracle BI Applications back in 2009, you’d have seen how Oracle reproduced the maplets feature within Informatica with temporary, or “yellow” interfaces. Temporary interfaces are different from regular ODI interfaces in that they can become a data source for another interface, allowing you to encapsulate mapping functionality and re-use it across wider ETL processes. Temporary interfaces have now made their way into ODI 11g and are a handy way of breaking down a more complex mapping procedure into more manageable chunks.

To take an example, in the example above we combined a table and file source into a single interface source through a set operator, which then provided us with some order line item informaton. Now consider a situation where we want the output of this interface to be available as a data source for another interface, so that we can combine the line-level data with some order-level information such as the order date, salesperson and ship date. To turn the original interface into a temporary interface, ensure that you don’t drag a target datastore into the interface, instead drag and drop the columns you require into the target datastore area, and in the Temporary Target Properties panel at the bottom of the screen, give the output table a name, for example ORDER_DETAILS_TEMP.


Now, when the temporary interface is executed, it creates an populates in this case a table called ORDER_DETAILS_TEMP, and if you include the temporary interface in another mapping, you can join to it and read from it just like any other datastore.


The default behaviour for temporary interfaces is to persist the intermediate results (the output of the temporary interface) in a database table, which in the example above, would make sense as part of the data is coming in from a file. But in the case where the temporary interface is only working with table data, it would be nice if we could render the temporary interface as just a SELECT statement, which we could embed in the calling interface as a sub-query or sub-select.

This is actually now possible with ODI 11g, using a feature called “Derived Select for Temporary Interfaces”. To take an example, consider a situation where you are building up a complex, multi-step interface where first, you aggregate orders by customer, and then you want to add an additional column to this dataset containing the customer order rank. You could start by creating a temporary interface that aggregated orders by customer, and then create a second interface that has the first temporary interface as a data source.

Then, instead of having the first temporary interface persist its output as a temporary table, you instead navigate to the Source Properties panel in the Interface Editor, and select the Use Temporary Interface as Derived Table (Sub-Select) checkbox, like this:


The benefit of going down this route is that you’ll cut down on the disk I/O, and disk space required, by removing the need to stage the intermediate results to a temporary table. The downside is that the SQL used in the main interface is going to be more complex, might take up more memory and might be a bit trickier to debug if there’s an error. But in general, if you’re using a fairly beefy database server to do your ETL, this new feature will make sense more often than not.

So there you go – some new interface and ETL features in the initial release of ODI 11g. But hold on – as Peter Scott mentioned, there’s just been a new release of ODI as well, so we’ll take a look tomorrow at two new features that come with this updated version – Load Plans, and OBIEE Lineage.


  1. Jeevan Kadam Says:

    Dear all,

    I am facing problem during Mapping Interfaces.

    I am integrating SQL server 2005 to Oracle, I have migrated the SQL server 2005 to Oracle(Target system). But at source model I have table & column name in “Lower case” and same table & column name are in “Upper case” at Oracle(Target). So its not mapping the columns, shows disable indicators.

    Is ODI Case-Sencitive in such conditions ?

  2. Jeevan Kadam Says:

    Can any one suggest me on my previous comment..?
    Its urgent !

  3. Michael Rainey Says:

    Hi Jeevan,

    Yes, ODI is case sensitive. Best practice is to create all Datastores and their columns in uppercase. In your example, though, I’m not sure why it would not map, unless you are referring to the Automap feature. Manually mapping a source to target should not be a problem.

    Michael Rainey

  4. Jeevan Kadam Says:

    Hi Michael,

    Thanks for reply..

    As you suggested,I manually transfered that column names to UPPER Case, and its mapping. but the problem is there are thousands of tables lacs of columns exist in database and practically it is not possible to transfer all columns in UPPER case for interface mapping.
    Is there any alternative solution for the same ? or query to covert it to UPPER case at ODI level ?

    Thanks !!

    Jeevan Kadam.

  5. Michael Rainey Says:

    Hi Jeevan,

    Take a look at Note 579751.1 on the Oracle Support website. It’s the repository data model from ODI 10g, but is largely unchanged in ODI 11g and should be able to provide the information you need for making a mass update. Just be careful changing the repository data, no guarantees it will work the same as it would via the UI, and is not recommended.

    You should instead have a look at the ODI SDK. You can create a Groovy script to loop through all of your objects and make the change to uppercase. In my opinion, this would be your best approach – and safest – as the SDK performs the same actions on the ODI metadata as the UI would.

    Michael Rainey

  6. SWETHA Says:

    Compare to actual interface yellow interface peformance is high(to load the data from source to target it will take less time) why? can anybody know the reason why it is faster?

Website Design & Build: tymedia.co.uk