Code Templates in OWB11gR2 Part 3 : Hybrid Code Template Mappings

Mark Rittman

Sep 22, 2009 • 6 min read

If you've read my previous postings on how OWB11gR2 support access to non-Oracle databases, and allows you to create mappings that use non-Oracle sources and targets, you may we wondering how Oracle data sources fit in to this, and the Oracle traditional "database resident" mappings that you normally create can be used alongside them. In most of the early examples, I used Oracle sources and targets within the code template mappings, but realistically this would be less efficient than just using regular OWB mappings, and probably more hassle to set up. Also, this does all beg the question as to how these code template mappings are used alongside traditional OWB mappings, such as whether they can be scheduled, included in process flows and so on. This is where probably the most important feature of OWB11gR2's code template mappings come in, with the concept of "Hybrid Mappings".

The key to hybrid mappings is in a new type of code template that Oracle ships as part of the public code templates, found within the Globals Navigator in OWB. This new type is called "Oracle Target" and contains a single code template called DEFAULT_ORACLE_TARGET_CT.

Ct33

This code template is a bit special in that it acts as a wrapper around a regular (or "database resident") OWB mapping. It contains just some simple PL/SQL that calls the mapping main method, and can be used in a number of interesting ways.

First of all, you can take a regular OWB mapping, copy it and then past it into the Code Template Mappings area, like this:

Ct34

If you then open up the new code template mapping and display the logical view, it's the same mapping design and layout as before. If you switch to the execution view, all of the operators are in the same execution unit, which has been set to use the Oracle Module code template. If you then go to execute this code template, it will call the database resident mapping PL/SQL and SQL code as normal, through the mapping's main method reference.

Ct35

So it's interesting to understand what's happened here. OWB hasn't "converted" the mapping to an ODI-style mapping, with load code template and integration code templates for source and targets, it's merely wrapped your database-resident mapping in enough wrapper code so that it can be deployed to an agent and run with all the other code template mappings. On a similar subject, even regular code template mappings that you create using OWB11gR2 aren't in themselves directly transferrable to ODI standalone and run from there, as whilst the basic technology is the same there are sufficient differences and enhancements in OWB's approach to make the two types of mapping incompatible. Similarly, you can't just take an ODI interface and import it in to OWB (at least not yet, though this may come in the future through an Expert, for example) or get each products's agents to run the others' mappings.

So, now that we can take whole mappings and convert them to code templates, what about individual mapping elements? You may have noticed that the components palette in the code template editor contains all of the mapping operators that regular OWB mappings use, but obviously some of them (splitters, dimension loads, match-merge etc) will only work for Oracle targets and sources. So how do we use these? This is where the concept of Hybrid Mappings comes in.

Going back to my code template mapping posted yesterday, that takes data out of an SQL Server database, joins it to some data from a file and from an Oracle table, and loads it into a staging table, the execution view for this mapping looks like this:

Ct36

Now I've got a database-resident mapping elsewhere in my project that takes data from the STG_CUSTOMERS table, and loads it into a dimension, like this:

Ct37

Now I could reproduce this functionality in my code template mapping, by adding in the CUSTOMER_DIM table source and using the ICT_ORACLE_SCD integration code template, but I've already got the logic produced and an advantage of using OWB, over say ODI, is that dimension loading is so much simpler. What I can do therefore is copy and paste the constant and the dimension operator from the traditional OWB mapping into the code template mapping that I've been working with, and then join it up to the staging table so that it looks like this:

Ct38

You can then switch to the execution view for this amended mapping. Now this step will require you to change the execution units around, as the Oracle dimension operator needs the staging table to be part of the execution unit, it can't be fed in to it via JDBC, and so the new execution view for this mapping looks like this:

Ct39

Now this is definitely interesting as it means that we can mix and match OWB and ODI functionality in our mappings, allowing us to use ICT, LCT and CCT code templates to extract, say, data of non-Oracle sources and then load it into dimensional structures managed, and defined, using traditional OWB functionality. This takes OWB in my opinion to a point where it's more useful, more functional than ODI - you can create code template mappings, database-resident mappings, or a combination of the two either brought together in process flows, or combined in single code template mappings that make use of Oracle Module code templates.

Now this is all new functionality, and I think it'll take us a while to come up with a design approach that makes optimal use of this new capability, but one interesting thing for me was that conceptually, I didn't have a problem with this concept of regular (database-resident) mappings, code template mappings, and hybrid mappings. At Rittman Mead we were on the 11gR2 beta and I worried at the time that these different means of mapping data would be hard for developers to understand, and that we'd end up with two repositories, two sets of table definitions, two types of mapping operators and so on (giving us a sort of "Frankenstein's Monster" of a tool).

What what the development team at Oracle have managed to come up with though is something really clever - there's only one repository, one set of operators, one set of table definitions, and you can mix and match these in code template mappings and across your project. The only thing I worry about is that there are lots of places to set mapping configuration options and I can see developers struggling to get everything to validate when first working with this code template mappings, especially if they don't have prior experience with ODI and its knowledge module approach.

Moving on, if you looked at the before and after execution views earlier in this posting, you might have noticed how the requirements of the Oracle Module code template meant that I had to move the joining of the source data out of the Oracle ICT code template and on to the agent. (as it was accessing data from SQL Server over JDBC). This sort of thing might crop up when you bring the two mapping types together, so the other way of integrating mappings like this might be to keep them separate, but bring them together into a process flow, like this:

Ct40

Code template mappings can be added into regular OWB process flows in the same way as database-resident ones, and if they share staging tables and interface tables, you can pass data around this way.

Well that's it for OWB new features for me for a while, but if you're interested in seeing all of this in real-life at the forthcoming Oracle Open World, and discussing OWB11gR2 development techniques with myself, Antonio Romero and David Allen from the OWB development team, check out this announcement about an "unconference" session that we are running. Other than that, I expect myself, Venkat and Stewart will be posting more on OWB11gR2 new features as we work through them.

Sign up for more like this.