Oracle Data Integrator vs. Oracle Warehouse Builder: What to do, what to do?

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
One of the questions that regularly comes up in the forums, or during presentations on Oracle data integration products, is on the future of Oracle Warehouse Builder and Oracle Data Integrator. People wonder which tool to use for new projects? What should be done with existing implementations? In this article we will first compare the features and functionalities of both products and then I will answer these questions.

Oracle Data Integrator Enterprise Edition

In January 2009, Oracle released a statement of direction for ODI and OWB: “Today Oracle includes Oracle Data Integrator (ODI) and Oracle Warehouse Builder (OWB) as the two components of ODI-EE and will merge them into a single unified data integration technology platform. This strategy fully preserves any existing development investments of all Oracle data integration customers and will provide a seamless, easy upgrade path from the current components to the unified platform and beyond.”
Both data integration tools will be licensed as one product, i.e. you don’t have to buy a separate license for Oracle Data Integrator and the OWB Enterprise ETL features. The Oracle Warehouse Builder core ETL features are installed with the Oracle RDBMS and remain free to use (as long as you are licensed for the RDBMS). From what I hear from Oracle they will also offer a free data integration component going forward even when the two products have merged into a unified platform.

Google Trends

Apparently Google Trends can predict the outbreak of a flu virus more accurately than traditional methods. Why not let it have a go at predicting the future of ODI and OWB. As you can see from the Google Trends chart below, we are dealing with a slow but steady increase in searches for Oracle Data Integrator. On the other hand we are seeing a slow and steady decline in queries for Oracle Warehouse Builder. Not really unexpected.

Declarative Design vs. Traditional ETL

ODI follows a declarative design approach. This is the key differentiator between the two products and the one that gives Oracle Data Integrator a clear advantage over Oracle Warehouse Builder.  Declarative design expresses the logic of a computation without describing its control. What exactly does this mean? In data integration projects loading and integration strategies are very similar, e.g. in a data warehouse project 80%-90% of fact tables follow the same loading strategy: first we disable foreign key constraints and indexes, then we look up surrogate keys and finally we load the data into the fact table. In a traditional ETL approach we would create a mapping module for each individual fact table. This is very time consuming. In ODI we simply define the loading strategy in a template (this is called a knowledge module in ODI) and with the click of a mouse apply it to every fact table load. Knowledge modules are created using a mix of programming languages, such as SQL, PL/SQL, Jython, the ODI substition API, OS commands, or Java. This data integration approach is very quick to implement and extremely flexible. It essentially gives you the flexibilty of manual scripting in combination with the advantages of an ETL tool (process flows, data lineage, scheduling, impact analysis, metadata etc.).
In a project where most data flows follow a similar pattern, the declarative design approach of ODI will be able to cut down the development time for your transformations by a factor of 30-50%. In a project where each transformation is unique you will not benefit too much from the declarative design approach in terms of productivity. However, from my experience this situation is extremely rare.

Data flow in Oracle Warehouse Builder

Data flow in Oracle Data Integrator

Data servers

One of the reasons why Oracle Warehouse Builder never made it into the top spot in the Gartner Magic Quadrant for ETL tools was its limitation on Oracle as the target server. You don’t have this limitation with ODI. In ODI you can use any technology that can be accessed via JDBC as either your data source or your target.

The ELT approach

Both products are ELT tools. They don’t need a separate transformation server but use the power of the target RDBMS server for transformations. This is a key differentiator to traditional products such as Business Objects Data Integrator.

Change data capture (CDC)

Oracle Data Integrator ships with change data capture knowledge modules. It supports near real time data warehousing out of the box.
Even though ODI allows you to quickly set up CDC, you will not get far with your project without a sound understanding of the underlying change data capture technology. In an Oracle world this is Oracle streams. Oracle Warehouse Builder has no interfaces to CDC. Of course, you can use it on top of Oracle CDC as this article by Mark Rittman shows . However, most of the work needs to be done at database level.

Data Modeler

Both products ship with a basic data modeller. The ODI Common Format Designer (CDF) is ideal for prototyping as you can quickly click together your target model from your source model. This then can be forward engineered to your target data server, e.g. a data warehouse. This saves you from creating a separate source to target map document as all the relationships are stored in the repository. Once you have established the relationship between the source and target attributes, ODI automatically generates data flow interfaces for you. Another brilliant feature. The functionality of the CDF for data modelling purposes, however, is rather limited. While you can define basic functionality and logical models (attributes, constraints, table relationships etc.) ODI won’t allow you to define the physical attributes of your tables, e.g. tablespaces, partitions etc. All of this functionality is available in OWB.

OLAP

In OWB you can create multidimensional OLAP cubes directly from a relational star schema. This is done through analytic workspaces and Oracle OLAP. You can’t load directly into Essbase cubes. ODI on the other hand can directly load your data into Essbase cubes. Various knowledge modules are provided out of the box for Essbase. ODI also allows you load into Oracle OLAP cubes.

Scripting Objects

Oracle Warehouse Builder relies on OMB+ and TCL for scripting objects. This is useful for updating a lot of objects in one go, e.g. if you want to set the tablespace for a group of tables. One drawback here is that you can’t create TCL scripts from your objects. It would be a nice feature to create a script from the mappings you create in OWB. ODI relies on XML to import and export objects. In theory you can script your objects via XML and then import into ODI. Unfortunately, no documentation exists on this so this will not be straight forward. One of the new features in Oracle Data Integrator 11 will be a proper API for scripting objects. Looking forward to this.

Misc.

ODI allows you to encrypt your objects. This could be useful for third party vendors of Knowledge Modules. With ODI you also have proper version and source control built into the product. OWB allows you to take snapshots of your objects. However, this feature does not allow for version control in the traditional sense. Personally, I have found the snapshot feature awkward to use.

Available resources

When I started out on ODI in January 2008 there was very little documentation available. Available documents I found poor. This has changed completely. In my opinion the best starting point to learn about ODI is the ODI Best Practices in a Data Warehouse guide. A quick search on LinkedIn revealed that there are currently 1,469 people registered with Oracle Warehouse Builder skills. On the other hand we just have 334 users with ODI skills. On Metalink there are now three times more documents on ODI than on OWB. There is also a lot of ODI related activity on the Oracle forums. While there is more OWB knowledge and skills out there, ODI is catching up fast.

What to do then?

Over the next two years Oracle Data Integrator and Oracle Warehouse Builder will merge into one unified product. In calendar year 2011 we will have a unified platform. The comparison above has shown that out of the two products ODI has more to offer. You can expect that a lot of the functionality for the unified platform will come from ODI.
In my opinion, only tactical data integration projects that solely rely on the Oracle Warehouse Builder core ETL functionality (the stuff you get for free with the RDBMS) should be implemented using Oracle Warehouse Builder. If you are licensed for Oracle Data Integrator EE, you should use Oracle Data Integrator for any new data integration projects you start. While Oracle will offer upgrade paths for both products you can use third party tools to start your migration from OWB to ODI today. Don’t expect that this tool will completely automate a migration. It also seems that migration of process flows is not supported currently.
Any Oracle data integration consultant will need to focus and upskill on ODI functionality going forward. Why not start this exercise with my ODI Knowledge Module article on OTN.