ODI11g in the Enterprise Part 1: Beyond Data Warehouse Table Loading

December 28th, 2012 by

Most developers who’ve used Oracle Data Integrator in the past have used it to load data warehouse tables, typically from an Oracle or other relational source into a set of dimensional Oracle target tables. Some source data might come from flat files or XML files, and in some cases the source databases might be SQL Server, IBM DB2, Sybase or mySQL. Most projects of this type load data in batch, in some cases once or twice a day or more recently, in near real-time using micro-batches or push technology such as JMS Queues. Compared to Oracle Warehouse Builder, ODI 11g is a fairly easy-to-understand, extensible tool with a clear product roadmap, few hidden surprises and a solid set of features for manipulating relational sources and targets.

Oracle Data Integrator 11g

Over past few years though, ODI has been extended in various ways to support loading and extracting from applications such as Hyperion Planning and OLAP servers such as Oracle Essbase, through to more recent innovations such as loaders for Hadoop and Oracle R Enterprise. ODI can play a full part in service-orientated architectures providing bulk data-movement functionality to complement SOA messaging, and through various APIs, SDKs and scripting languages, it can take part in DevOps-style software development methods to support techniques such as continuous integration, build automation agile development.

In addition, as ODI has moved away from pure data warehouse ETL-style development through to being a key part of a wider Fusion Middleware deployment, the code it produces becomes mission critical and runs 24 hours-a-day, providing vital data movement and integration within the enterprise. New features such as JEE agents, load plans and OPMN provide resilience and fault tolerance for ODI data integration routines. but these are new features introduced since the Oracle acquisition and many developers may not be aware of their existence. ODI also is part of a suite of data integration tools including products such as Oracle Goldengate (for heterogenous data replication and changed data capture) and Oracle Enterprise Data Quality (the ex-Datanomic toolset used for profiling and cleansing enterprise datasets), as shown in this Oracle graphic from 2012’s Oracle Openworld:

Oracle's Data Integration Toolset

So, over the next few days I’ll be looking at where ODI is now, and how new features introduced over the 11g timeline make it a first-class development tool within the wider Fusion Middleware toolset.

I’ll add the links as I publish each of the articles, but over the next week here’s the ODI 11g topics that I’ll be covering:

I’m also planning on presenting a session on this topic at the upcoming BIWA Summit 2013 in San Francisco, January 2013, so if you’ve got any thoughts or observations that would be worth me incorporating into my final session, feel free to add them to the comments. For now though, check back tomorrow for the first installment, where we’ll look at what other sources and targets ODI 11g can work with, with a particular look at how ODI11g’s new “big data” features, integrating with technologies such as Hadoop, Hive and MapReduce, works.


  1. Peter Scott Says:

    I am not sure that ODI was always seen to be in the DW-ETL space – certainly when I first used the product (before Oracle acquired Sunopsis) it majored on heterogeneous integration and web-services. Indeed for the early part of its Oracle life it was not marketed in the DW ETL space.

    That said though ask people about data integration and they seem to think about DW ETL… or perhaps it’s just the people that speak to me :-)

    I do agree that the new features you mention certainly boost ODI’s credentials in the Enterprise space

  2. Mark Rittman Says:

    Pete – you’re right of course, ODI (or Sunopsis Data Integrator and Conductor) was a general-purpose DI tool prior to the Oracle acquisition – I guess I was exaggerating for effect.

    Anyway – I love the fact that you and I are debating ODI semantics, and installing big data VMs, over the Christmas break – no-one out-geeks us at Rittman Mead ;-)


  3. David Says:

    smirk;-) I’m trying to keep you company with some posts on the ODI Hive stuff, just so you guys don’t feel alone!


  4. Mark Rittman Says:

    Good stuff David. I’ve covered ODI and Hive/Hadoop in the next post in this series, posted earlier today – I’ll go into a lot more detail in the new year, and I’ve linked to your posts in my one today.

    cheers, Mark

  5. Stewart Bryson Says:

    Mr. Allan is so known in the ODI world that he just has to put “David” and we all know who he is. :-)

  6. Suraj Says:

    Little bloggers like us have also contributed to ODI Hadoop :)

    Great Posts Mark.


  7. Gurcan Orhan Says:

    Very useful and nice article series. I enjoyed this “serie” a lot.
    Thank you.

Website Design & Build: tymedia.co.uk