Oracle Warehouse Builder and Data Integrator
Sometimes, when I am working with customers on data warehousing projects I am asked questions about Oracle Warehouse Builder and its future. I know no more on this than what I read in Oracle's reposted a statement of direction from May 2011 and recent internet postings elsewhere which states that OWB 11gR2 will be the final release, although it will be patched to work with the Oracle 12 database when that comes along. To me this means that for existing OWB projects there is no hurry to migrate to ODI - Oracle have signaled in their statement of direction that a future ODI release will help smooth the migration path. However, I think that for new projects ODI should be considered as first choice - unless you only require the basic OWB functionality that is included with the Oracle database's license, and even then I would be tempted to look at the advantages of using the enterprise-quality features you gain with the purchase of ODI.
One question that often comes up is "How is OWB different from ODI, after all they both do E-LT?" I have written a small series of blogs to be published over the next few months that look at this subject from the point of view of an OWB developer moving to ODI.
To start things off here is the first of the series where I am looking at OWB and ODI in high level terms and point out some of the key differences and similarities. I will be considering the two current releases (OWB 11.2 and ODI 11.1.1.5). Later blogs will look in more detail about the actual development of ETL process and how to orchestrate them.
Both ODI and OWB have a similar (I am being very simplistic here) three-component design of: a metadata repository, a development environment where the developer defines the processes and data flows and a runtime component that executes the code and flows. It is the "how" of these things that is different for the two tools.
Both are repository driven, that is the metadata that describes the ELT processes, data structures being accessed and host of other things is held in a database schema. For OWB the repository is pre-installed (the user needs to create a workspace though) in an Oracle 11gR2 database, optionally, the OWB repository can be installed into an other Oracle database if required. ODI's repository is installed using Oracle Fusion Middleware's Repository Creation Utility into a supported (and not necessarily Oracle) database. With ODI, the repository can be shared with other components that use the Fusion Middleware stack such as OBIEE 11g, whether this is desirable would depend on your circumstances and factors such as your organization's software release process and network topology - just because it is possible to have all on one database does not make it desirable.
Cosmetically, there is a lot of similarity between the two development environments, they are both part of the same unified family of Java IDE applications as JDeveloper and SQLDeveloper; the look and feel is similar, for example double-clicking on a tab has the same effect (it toggles the tab's panel between full-sized and windowed). What is different however is the content of the windows and navigators and that is a big topic for later postings.In practice, with OWB the key parts of the IDE are those for the development of MAPPINGS and (optionally) the design of process flows to orchestrate mappings. In the ODI world think INTERFACES for mappings and PACKAGES for process flows. This is simplistic though as ODI also has PROCEDURES (code developed in one of the ODI supported languages) and LOAD PLANS (multiple packages orchestrated to execute in serial or parallel). OWB mappings require the developer to include all of the components needed to facilitate the mapping - we connect source columns to target columns through a logic flow of joiners, filters, expressions, aggregates and a whole palette of other activities. Typically, this would generate a single, but large, SQL statement with much use of in-line views. ODI interfaces are simply about connecting source columns to target columns in a logical relationship (we also create expressions, joins and filters here) and allowing the physical implementation to be supplied by a knowledge module.
In its most common usage mode, OWB deploys its executable code into PL/SQL packages in the target database. Even pure SQL set-based insert code is wrapped into a package that contains the control and audit methods that allow it to execute under the control of the Control Center and the OWB runtime. The code generated by ODI depends on the knowledge modules used and might be native SQL which is executed directly against the target database by the Java agent executing the code. Again this is a big topic and more will follow in later blogs.