Oracle Data Integrator (ODI) is the long-term strategic data integration offering from Oracle. Now Warehouse Builder Enterprise ETL 11gR2 (OWB-EE) updates the most widely adopted data warehouse design, ETL and data quality offering for the Oracle database with ODI-based data integration infrastructure. This new offering delivers the benefits of fast, flexible, heterogeneous data integration to today’s Warehouse Builder customers.

Serving the needs of the emerging broader data integration market, Oracle Data Integrator, Enterprise Edition (ODI-EE) is the basis for Oracle’s long-term data integration offering. However, a Oracle’s large Database customer base already has a substantial investment in skills and data warehouse ETL and data quality designs in Warehouse Builder, and is affected by these new emerging data integration requirements. To protect these investments while serving the evolving requirements of that data warehousing customer base, OWB-EE 11gR2 refreshes OWB around the edges with ODI-based heterogeneous source connectivity and Change Data Capture (CDC); real-time mappings for event-based data integration; and basic SOA integration capabilities. Oracle plans to support migration of designs based on OWB-EE to ODI 12g with no significant redesign or loss of functionality.

The most significant of these enhancements in OWB 11gR2 is integration with Oracle Data Integrator — in particular the Knowledge Modules (or “Code Templates”, as OWB refers to them). ODI Knowledge Modules use a templating system and languages such as SQL, PL/SQL, the equivalents for other database platforms plus languages such as Jython to create scripts that leverage the native capabilities of each source and target platform. This new feature allows you to develop mappings that use the “Knowledge Module” feature of Oracle Data Integrator (referred to in OWB11gR2 as “Code Templates”) to create mappings that leverage Oracle and non-Oracle ETL functionality in addition to that provided by OWB.

The 11gR2 release incorporates ODI’s Knowledge Module framework into Warehouse Builder. this in several ways:

• You can now build mappings that make use of code templates to move data around;
• ODI platform technology can be used to connect natively (via JDBC) to non-Oracle as well as Oracle platforms.
• ODI Journalize knowledge modules can be used to implement Changed Data Capture on Oracle and non-Oracle sources;
• ODI and traditional OWB functionality can be mixed and matched, allowing you to create mappings that use ODI knowledge modules together with OWB Oracle-specific functionality such as dimension loading, match-merge, multi-table inserts and so on;
• You can write your own knowledge modules (or code templates, as they are called in OWB) to extend these capabilities to new platforms, new data extraction and loading approaches and so on, a bit like writing Experts in previous releases of OWB.

You may be wondering, “How does this look in the new release of OWB?” (See the screen capture below.) In the Globals Navigator part of the main OWB application, as well as getting public transformations, public experts and so on, you now get public code templates. These are code templates (aka knowledge modules) that are certified to work in OWB and ship with the product.

Working with the Project Explorer view in OWB11gR2, there’s a new entry at the top of the project tree for Code Template mappings, which you can create alongside traditional OWB-style mappings to suit the type of integration you are carrying out.

In the next example (below), a code template mapping called INS_PROGRAMDIM_STG was created under a code template mapping module called CODE_TEMPLATE_MAPS. Code template modules are associated with Agent locations (Agents are the equivalent to Control Center Services in OWB), with a default agent shipping with OWB11gR2 running on an OC4J instance.

To start off then, a simple code template mapping that moves data from the  V_PROGRAMDIM_STG source view into the PROGRAMDIM_STG target table was created. Looking at the mapping canvas, there’s a couple of points to note here:

1. Although we are using code templates, the way we lay out the mapping is exactly the same as with traditional OWB mappings. There’s a bunch of transformation operators (join, splitter, deduplicator, etc.), source and target operators, object properties and so on. Now this is very significant, as it means that existing OWB mappings can just be cut and pasted into the code templates mapping area to transfer them across, and you can add far more complexity and logic into your mappings compared to the simple table joins and filters that you get in ODI.
2. Looking at the Logical View and Execution View tabs at the bottom of the canvas, you will see that we are working with the Logical View, and this looks just like the mapping canvas that you get with regular OWB mappings. When you switch to the Execution View you start to make decisions about code templates, and what are called “Execution Units”.

In the example below we have two execution units, one around the source view and the other around the target table. The source view is extracted using the LCT_SQL_TO_ORACLE load code template and then data is loaded into the Oracle target table using the ICT_ORACLE_INCR_UPDATE integration code template.

Conclusion
The availability of code template mapping technology from ODI in OWB-EE 11.2 brings OWB customers much-needed data integration options within a product where users already have designs and skills. It also aligns OWB with the long-term Oracle strategy for data integration, building on common tools, frameworks and approaches to produce a tool which, while accessible to the existing customer base, addresses their emerging needs and positions them for the operational data warehouse of the future.