Rittman Mead - Delivered Intelligence

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.

Posted in Uncategorized | 5 Comments »