Oracle ETL Tools

For years the ETL for Oracle Data Warehousing has been OWB (Oracle Warehouse Builder). But in 2007 Oracle introduced another ETL tool called ODI, Oracle Data Integrator (not to confused with BODI, Business Object Data Integrator). ODI was originally from Sunopsis. Sunopsis had an ETL product called Data Conductor, famous for its ELT approach, i.e. load into the target server first, then use the power of the DB engine to transform and upsert into the target tables. This approach suited Oracle so Oracle bought Sunopsis in 2006 and branded Data Conductor as ODI.

It was (and still is) Oracle’s intention to combine OWB and ODI into 1 product. In January 2009 they bundled ODI and OWB into 1 license and called it ODIEE. Still two separate software, but 1 licence. Some of ODI functionalities were added into OWB and it was called OWBEE. Formal statement here. Formal OWB blog here. ODI product page here. Data Integration blog here. OWB product page here.

So today Oracle has 4 ETL products:

• ODI: the one from Sunopsis. This is the future.
• OWB: the old OWB (without the new ODI functions), available with Oracle DB. Aka “Basic ETL”.
• ODIEE: bundled license of OWB & ODI. EE stands for “Enterprise Edition”.
• OWBEE: modified OWB, with the new ODI functions. EE stands for “Enterprise ETL”, not “Enterprise Edition”

In the future they will be merged into 1 software: ODI. For now (2011), companies which are new to Oracle ETL should use ODI, because that is the future. ODI is the strategic product. Companies which already use OWB for a long time should use OWBEE. In the end, the old OWB will be gone, replaced by ODI. But they can’t do that now, because there are many companies using OWB at the moment. There must be a smooth way of migrating them to ODI, and there must be support for OWB for many more years. But there’s no escape that they must migrate all to ODI, just like DTS to SSIS.It will probably take 5 years for the world to change. For companies using OWB, this means a big project, and significant costs.

But there’s no “middle way”. Oracle as a company can’t maintain 2 ETL tools, because of the cost. They must have only 1 tool. ODI is the strategic product. In 5 years time this is ETL product that will survive. This is the product that will be enhanced and developed further. ODI is Oracle’s strategic ETL tool, as per the formal statement here.

The alternative is to move all ODI core features to OWB, which in my opinion can’t be done. There is a fundamental difference in principle of how they work. If OWB is like a house, we can’t just add the core ODI features on top that house. But we have to demolish that house, build a new foundation (the ODI way) then rebuild the house.

In addition to OWB & ODI, Oracle also has GoldenGate, a famous CDC (change data capture) tool that Oracle acquired in July 2009. GoldenGate capture the changes in the source systems by reading the transaction log file and apply the data changes to the target system such as DW or ODS using a native DB interface or ODBC. The component that applies the data to target is called Replicat. GoldenGate 11g Admin Guide is here, architecture diagram is on page 12. The real beauty of it is that GoldenGate can operate between various RDBMS, i.e. DB2, MySQL, Oracle, SQL Server, Sybase and Teradata, integrating data between them in real time (synchronous) or in batch using CDC. Using GoldenGate is not easy, there is a lot of command line interface. Not as easy as (and not as modern in UI) for example, DataMirror, DBMoto and Attunity. But functionality wise, GoldenGate is probably the richest.

One word about the term “Data Integration”. In 2006 all ETL companies seem to be in agreement that the word “ETL” had a bad perception. The word “ETL” only covered a small part of the scope. They all rebranded their product as “Data Integrator”. a) they could add/integrate data quality and data profiling into the product, b) they could get into MDM market with their tool, c) they could get into data integration projects (not only data warehousing projects), d) they can get into operational systems such as ERP, business systems, and ODS projects. I totally agreed. It should be called Integration, not ETL. Look at Business Objects. They called it BODI, Business Objects Data Integrator. SSIS: Integration Services. In 2007 in Boston I listened to Michael Gonzales’ ETL session at the TDWI conference. He said that “ETL is dead, it is now called Data Integration”. I totally agreed. But, in 2010, because everybody in the market (and the press) still call it ETL, the word ETL seems revived again. Hence the title of this post 🙂

If you find something incorrect above, I’d be grateful if you could let me know, either via comments or vrainardi@gmail.com, I will correct them.