Pluggable Mappings using OWB10gR2

In this posting I'll look at how pluggable mappings are used in Oracle Warehouse Builder 10gR2.

Pluggable Mappings are a good way of creating reusable ETL components that can be dropped into a mapping. A pluggable mapping consists of in and out interfaces, and a set of OWB mapping steps that retrieves a value, updates an object or some other such 'complex' task. Pluggable mappings are used by OWB itself when, for instance, you load data into a dimension object, and you can create libraries of them for use as "super-transformations" within your project. It's a similar concept to procedures in PL/SQL - you just pass across the required parameters, it performs the task and sends out any output parameters.

In this example, as part of the design phase we have identified a common requirement to translate product names and translations into various languages. We could include the logic to do this - lookups to references tables, joins and so forth - in every mapping, or we could take the logic and turn it into a pluggable, reusable mapping with interfaces at the front for the product ID, and at the back for the translated items. You could of course do the same with a database function, but doing it this way keeps the mapping logic within OWB, meaning that an OWB developer can code it rather than needing a PL/SQL guru.

To start the process off, I define two source modules, OE and HR, and a target module, ETL_TGT. I then navigate to the Pluggable Mappings node, and create a new pluggable mapping, PRODUCT_DETAILS.

I then leave the input and output groups at the default settings.

Next I define the input signature. This is is the list of input parameters accepted by the pluggable mapping, and it's this that users will map to when using it on a mapping.

And I then do the same for the output signature.

Now that the input and output signatures are defined, I put the mapping together to implement the pluggable mapping.

And join the tables together, to retrieve the translated details from the Product Descriptions table, and the supplier ID from the Product Descriptions table.

At this point there's no way you can verify the mapping, you have to do that in the context of the mapping you drop it in to. To try it out, I put another mapping together to bring across  inventory information and translate it into a specified language. To do this, I use the Inventories table from the OE schema, do a key lookup on the Warehouses table to retrieve the name of the warehouse, and use my new pluggable mapping to translate the product description.

Then, when I run the mapping, remembering to enter the input parameter ("DK", for Danish) I get the required results.

Going back to the mapping, we can expand the pluggable mapping element to see how it works:

And that's broadly how it works. When you come to build other pluggable mappings, it's worth bearing in mind that there are two ways to create them:

• Directly, using the Pluggable Mapping node in the Project Explorer, and

• Within a mapping, using the pluggable mapping input and output signature operators.

One thing you can't currently do is locate a mapping, "draw a box" around part of the mapping and save it as a pluggable mapping. You have to either create them as a separate exercise, or create them as you're building a bigger mapping, perhaps where you indentify as you're building that the mapping you're building is reusable.