The Oracle Data Integrator 12c Masterstroke

Uli Bethke Oracle Data Integrator (ODI)

Visual data flows and OWB to ODI 12c migration path

In their latest release of Oracle Data Integrator (ODI 12c), Oracle have addressed two main concerns of the analysts and some of their customers and partners. The first one is the unclear migration path from Oracle Warehouse Builder to Oracle Data Integrator. Another frequent concern was that the declarative design approach based on reusable code templates (knowledge modules) was not visual enough and scored badly in this category against other ETL tools.

People were rightly raising concerns that complex queries could not be implemented through the ODI user interface. I had always been wondering how Oracle would address those two issues. In what has to be labeled as a masterstroke of the ODI product and development teams they were able to kill two birds with one stone. And two big birds those are.

Note: As a side note I have always been wondering why ODI's declarative design approach has not really made it on the analyst's list of strengths for ODI. To me this metadata driven design approach that makes extreme reusability possible and significantly reduces development time is the core innovation and ultimate strength of Oracle Data Integrator.

Declarative Flow-Based User Interface

In ODI 12c, Oracle have introduced a feature that they call Declarative Flow-Based User Interface. Don't be distracted by the name. What it means is that we can now create data flows (Interfaces were re-named to Mappings) that combine the two approaches: the visual approach of Mappings and Operators that we already know from Oracle Warehouse Builder and the powerful declarative design approach we love from Oracle Data Integrator.

With this new paradigm you essentially get the best of both worlds: ease of visual data flow development and the power of the declarative design approach. No other vendor can offer anything comparable. When would you combine the two approaches? For simple requirements I would recommend to stick to the traditional ODI approach. However, when you have complex requirements, e.g. you need to pre-aggregate data or filter a recordset based on the application of an analytic function then the new feature comes in handy.

At the moment we don't have all of the Operators available that we know from OWB, e.g. I am missing the Pivot and Unpivot Operators, which means that complex queries requiring these can still only be generated using workarounds such as Views or similar. What would be handy is an Operator SDK that would allow to create GUI representations of database specific SQL dialects, e.g. I love subquery factoring and it would be handy to have an Operator for this or one for recursive queries etc.


ODI 12c Mapping: Visual Design with new Operators

OWB to ODI 12c migration

The introduction of OWB style Mappings should also faciliate the migration from Warehouse Builder to ODI 12c. The migration tool is still in Beta. While it still remains to be seen how well the migration toll will work, ODI 12c for the time being ships with the OdiStartOwbJob tool, which allows to kick off OWB jobs through ODI and store execution results in the ODI Operator log. If you have an immediate requirement to migrate from OWB to ODI contact us for our advice.

Other new features

Another interesting feature in ODI 12c is the ability to run Mappings in parallel. In practice that means that each of the temporary tables gets its own unique name avoiding clashes. In the past you had to apply workarounds.

There have also been enhancement to the Knowledge Module editor. For those of you writing a lot of their own Knowledge Modules this is welcome news.

ODI 12c now also has much tighter integration with Oracle Golden Gate. A welcome introduction for those of you running near real time data integration projetcs (another concern of analysts and some customers).

ODI 12c Gaps

While ODI 11g has brought us the SDK to automate and script common tasks, ODI 12c now brings us visual Mappings giving us new options to create data flows. Oracle Data Integrator remains the leading ETL tool on the market. With ODI 12c it has extended this lead even further. How Gartner don't rate it above DataStage or Powercenter in their Magic Quadrant is a mystery to me.

One or two weaknesses remain. ODI 12c does not yet integrate with  source control systems out of the box. There are also no automated deployment options. While this functionality can be scripted it takes significant effort to do so. I am currently in the process of testing such an in-house developed solution. If you would like our advice how source control integration and automated deployments can be achieved get in touch.

While ODI 11g brought us improvements in the way we schedule and orchestrate data flows I am a strong advocate of dependency driven data flow execution. It is so much simpler and more efficient to hard coding of data orchestration routines. An enterprise solution with several thousand data flows will not work smoothly without a dependency driven scheduler. If you would like to find out more about our solution get in touch. You can also download our presentation ODI Scheduler- Source Control - performance on both source control and dependency driven scheduling.


The ODI 12c Developer Guide:

New Features of ODI 12c:

Download Oracle Data Integrator 12c:

Install tip ODI Studio:

ODI Training. Learn ODI from the experts.

You may also be interested in our ODI training courses. Value-priced. Customised. Onsite-Offsite. Online. Get all the details from our ODI training site

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.