October 24th, 2013 by Stewart Bryson
Most readers of this blog probably know me for my time spent with OBIEE (especially my work in repository development), but I’m guessing some of you long-time readers know that I grew up working with the Oracle Database. Many and many a year ago, I was a fledging Oracle DBA getting all my answers from the AskTom posts by Tom Kyte and the white papers by Carey Millsap. It wasn’t too long before I got my first chance architecting a data warehouse. I was hooked. Single-row lookups no longer interested me… it had to be millions. Then I started writing ETL… first using Perl (as a wrapper for generating SQL and executing it through DBI), and then later using PL/SQL. Finally, I got my hands on Oracle Warehouse Builder. The waters parted. I was home.
What did I like so much about OWB? It generated awesome code. It used smart Oracle loading techniques such as multi-table inserts, MERGE statements instead of UPDATE statements, partition-exchange loading, etc. It generated the kind of set-based, pure SQL that I had been wrapping in languages such as Perl and PL/SQL for years. What did I not like about OWB? Those times when the code wasn’t optimal, or the GUI simply didn’t allow me to do the things I wanted to do (SQL Analytics anyone?) I ended up writing logic in views, or crafting complex pre- and post- processes in PL/SQL, which ironically gave birth to the Transcend product that Rittman Mead offers today.
Then Oracle purchased Sunopsis and Oracle Data Integrator was born. At first… meh. There was a lot to like about ODI, I’ll grant you that. But it took me a while to get past the “different” user interface, and the confusing Topology. But once I wrapped my head around it… I found the yin to OWB’s yang. I couldn’t believe the power I had discovered in the Knowledge Module framework. ODI still generated set-based SQL code because of the similar EL-T approach as OWB, but I was now able to modify how the code was generated without stepping outside of the tool. Additionally… I started to understand and appreciate the Topology, drawing a comparison to the OBIEE sematic layer with the capability of abstracting the logical from the physical, and building more logic into the model instead of the processes that used it. But we were still left in a bit of a between-state. Oracle had two very good ETL products, but neither one felt complete. One tool gave me the standard “operator” paradigm and accompanying flow-based design. The other gave me the power to move mountains by affecting the generated SQL working directly at the source. If only Oracle could bridge the gap… and deliver one product that ticked all those boxes.
Ladies and gentleman… I give you ODI 12c.
One of the best parts of working for Rittman Mead is that I am constantly under NDA with Oracle and get to see new features, product roadmaps, and generally get an insider’s look at what’s coming in future releases. But I have to tell you… the ODI 12c release is one of the hardest secrets I’ve ever had to keep. Years ago, I was asked by Oracle (mostly by David Allan, I think, in retrospect) to participate in usability studies they were conducting on some of the early wireframes of what eventually became ODI 12c. The first time I saw the new flow-based design… all the metal pieces clicked into place for me. This is what we have been waiting for… and what David referred to when he recalled my demand to “just have a mapper”.
So I know you guys want some technical posts form me on ODI. Don’t worry… they’re coming. I won’t try to duplicate the excellent post by Jerome Francoisse delineating the new features. The first one I’ve already started writing is on JEE agents in 12c… if I can get it done before Michael Rainey or Mark Rittman beat me to it. I also want to spend some time with the new Component KM’s, a feature which I think is on equal footing with the flow-based design. So keep your eyes peeled, watch the blog for new ODI content, and let’s enjoy the victory.