Code Templates in OWB11gR2 Part 1 : Introduction and Support for Heterogeneous Databases

Mark Rittman

Sep 21, 2009 • 7 min read

One of the new features introduced with Oracle Warehouse Builder 11gR2 is "Code Templates". 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. For example, you might use the code templates feature to extract, via a bulk extract utility, data from IBM DB/2, combine it with some data on a Teradata database, stage it through an Oracle database whilst maintaining some slowly changing dimensions, and then bulk load it into a data mart sitting on a Microsoft SQL Server database. So conceptually, how does this work?

In Oracle Data Integrator, knowledge modules are divided into a number of categories:

Load knowledge modules, which confusingly are concerned with extracting data from sources
Integration knowledge modules, which are used to integrate (or in other words, load) data into target databases
Journalize knowledge modules, used for implementing native and trigger-based changed data capture
Check knowledge modules, used for data checking and data quality
Service knowledge modules, used for exposing mappings as web services, and
Reverse knowledge modules, used when importing table definitions via reading database metadata

Ct0

When you create an ODI mapping, you create it in two stages. First you define the logical view, which in ODI is fairly simplistic as it always consists of one or more tables being joined and then loaded into a target table (the BI Applications 7.9.5.2 ships some new knowledge modules that provide some extensions to this, but the principle is the same).

Ct1

Then, once you've set up the logical view of your mapping (or "interface", in ODI terminology) you then assign knowledge modules to each of the stages of the mapping, choosing the most appropriate module for the technology and type of data movement that you are doing.

Ct2

Now the advantages of this approach are that firstly, you've got access a library of "best practice" extraction and load routines across a range of database and other technologies, which you can extend yourself using a templating language. Secondly, it allows you to break the mapping into logical and technical elements, which means that someone with knowledge of the underlying data can wire the data together whilst a DBA can then pick the most appropriate knowledge modules to actually move the data. The disadvantage of this though is that, at least in ODI, the mappings you can create are relatively simple, just joins and filters between one or more source tables, which means that you have to break down what in OWB terms would be a single complex mapping into lots of individual interfaces.

Oracle Warehouse Builder, as I'm sure readers of this blog would know, has up until now been Oracle-centric and has generally generated SQL and PL/SQL in order to move data around. Mappings in OWB contain both the logic of the transformation (the expressions, the column mappings and so on) and details of the technical implementation (whether to use MERGE, to use external tables and so on). Some of the more advanced functionality to use, for example, transportable tablespaces or data pump, has been accomplished by special types of database module that need to be executed outside of regular process flows. From OWB 10gR2, there has been some limited ability to extract from, and load in to, non-Oracle sources, but it's been tricky to set up and limited to Windows or Linux platforms (ODBC, through the Generic Connectivity feature in the Oracle database), or expensive (the various Gateway products that you can license for SQL Server, Teradata etc).

The 11gR2 release changes all of this by incorporating ODI's Knowledge Module framework into Warehouse Builder. It does 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 platforms (and Oracle, if you wish)
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 the way you can write Experts in previous releases of OWB.

I'll be looking at each of these different scenarios over the next week or so, but at a high level, how does this look in the new release of OWB?

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. You need to have the ODI Enterprise Edition license to use these, but like the Enterprise ETL functionality in previous versions, there's nothing that warns you of this when you make use of them.

Ct3

Everthing that makes use of code templates in 11gR2, including native connectivity to data sources and changed data capture, requires the licensing of ODI-EE which also includes the licensing that you used to get with the OWB Enterprise ETL license (Data Quality is still licensed separately). It's probably fair to say that, with this code template functionality cropping up in many places in this new release, most customers will probably end up going for the ODI EE license when working with OWB in the future, probably more so than the Enterprise ETL license before it. Certainly it's a more compelling set of features than were offered previously, and with ODI standalone thrown in as well it's not bad value.

As well as the shipping public code templates, you can also import templates in from an ODI installation. In the screenshot below, I've imported all of the code templates in from ODI 10.1.3.5 so that I can subsequently work with Essbase, mySQL and other sources not supported "out of the box".

Ct5

The next place that code templates crop up is in the list of database modules that you can create. As before, there are a number of non-Oracle sources and targets that you can create, including SQL Server, Informix, DB/2 and Teradata.

Ct6

This time though, when you create connections through to the relevant database you can connect natively, via JDBC. You put in your connection details to SQL Server, for example, entering the username, database name, host name and so on (note that you need to download the relevant JDBC drivers from the vendor website, and that in the case of SQL Server it's only versions 6, 2000 and 2005 that are currently supported), and define the module as normal.

Ct7

Once the module is defined, you can import the tables, views and transformations in from the non-Oracle source just as you would with Oracle ones. Internally, OWB keeps a record of what SQL Server datatypes map to the standard OWB ones, which makes it possible to create mappings that span across different database technologies.

Ct8

If you notice the list of database technologies above, it's pretty much the same as earlier versions of OWB and doesn't include some of the more exotic ones that ODI supports, such as mySQL, Essbase, Hyperion Planning or Netezza. What you can do though is define what are referred to as new "platforms" in OWB (something you have to do using ODI scripting, setting out the details, driver, datatype mappings and so forth), and thereafter you can use the ODI knowledge modules that refer to these platforms to carry out data loading and extraction. My understanding is that there are plans within the OWB development team to make this process a bit easier by shipping "experts" that automate this process via a GUI.

As I mentioned in my previous posting on OWB11gR2, another place that code templates pop up is in the new support for changed data capture. Taking my SQL Server module defined above, I can edit the module details, tell it which journalizing code template to use for CDC capture, pick my tables to monitor and then make use of changed data capture in my mappings.

Ct9

It's in the area of mappings that we see the most changes though, and what's been introduced in this new releases does nothing less than completely redefine how mappings can be created in OWB. For details on this new feature, check out the next posting in this series, on "OWB11gR2 and Heterogeneous Code Templates".

Sign up for more like this.