Working with Pluggable Mappings

Purpose

This tutorial shows you how to use pluggable mappings.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Examining the Source Tables
	Creating a Standalone Pluggable Mapping
	Reusing the Pluggable Mapping in a Mapping
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

A pluggable mapping is a reusable grouping of mapping operators that works as a single operator. If you are including a common logic in many mappings, it is best to define the common logic as a pluggable mapping. Once defined, this pluggable mapping can then be included in every mapping just as a single mapping operator with input and output groups.

You can reuse a pluggable mapping more than once in the same mapping, or in other mappings. You can include pluggable mappings within other pluggable mappings.

A pluggable mapping has a signature consisting of input and output attributes that enable you to connect it to other operators in a mapping. Using pluggable mappings, you can reuse logic and hence reduce the effort of changing and maintaining logic.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

Have completed the tutorial, Setting Up the Oracle Warehouse Builder 11g Release 2 Tutorial Environment

The Setting Up the Oracle Warehouse Builder 11g Release 2 Environment tutorial provides the setup files to configure and setup an environment for this tutorial.

Have logged into the Design Center as "etl_owner/etl_owner"

Back to Topic List

Examining the Source Tables

in this example, you create a pluggable map that looks up product information for a specific language. For that, you must examine the source tables that contain the appropriate information.

Expand ETL_PROJECT > Databases > Oracle > SRC_OLTP > Tables. You will use the two tables, PRODUCT_DESCRIPTIONS and PRODUCT_INFORMATION, highlighted in the screenshot below.

The table, PRODUCT_DESCRIPTIONS, contains the translated strings. Right-click PRODUCT_DESCRIPTIONS and select Open , to examine it in the editor.

Click the Columns tab to view the table's attributes. Note, TRANSLATED_DESCRIPTION attribute of NVARCHAR2 data type. This column contains the translated string.

Similarly review the PRODUCT_INFORMATION table. This contains all the information about the products.

Now, you will create a pluggable mapping using the two tables.

Back to Topic List

Creating a Standalone Pluggable Mapping

The Pluggable Mappings node in the Projects Navigator panel contains the following two nodes:

Standalone: Contains individual pluggable mappings
Pluggable Mapping Libraries: Contains a set of pluggable mappings with related functionality that you want to group as a library.

You can create pluggable mappings under either of these nodes. In this tutorial, you create a standalone pluggable mapping.

Back to Topic List

1.
Under ETL_PROJECT, expand Pluggable Mappings. Right-click Standalone and select New Pluggable Mapping. The Create Pluggable Mapping wizard starts. Click Next on the Welcome page.

2.
Enter PRD_DETAILS as the name of the pluggable mapping. Click Next.

3.
Note that by default the pluggable mapping has an input group, INGRP1, and an output group, OUTGRP1. You can add or remove groups depending on the requirement.

Note: The signature is a combination of input and output attributes flowing to and from the pluggable mapping. A pluggable mapping must have at least one input or output signature group. Most pluggable mappings are used in the middle of a logic flow and have input as well as output groups.

Click Next.

4.
The input signature is the combination of input attributes that flow into the pluggable mapping. Add attributes to create the input for the product and language lookup.

Enter PRODUCT_ID in the Attribute column. Accept the default data type. Enter LANGUAGE_ID in the Attribute column and change the data type to VARCHAR of size 2. Click Next.

5.
The output signature is the combination of output attributes that flow out of the pluggable mapping. For now, do not add any output attributes. Click Next.

In the Summary page, click Finish.

6.
The pluggable mapping panel, PRD_DETAILS opens. Click Auto Layout to get the full view of the pluggable mapping.

Now, you add the required operators and create a data flow between the operators.

7.
Add the source tables. In the Projects Navigator panel, expand SRC_OLTP > Tables. Drag PRODUCT_DESCRIPTIONS and PRODUCT_INFORMATION table on the canvas.

8.
Add a Joiner operator in the mapping. From the Component Palette, drag a Joiner operator on to the canvas.

9.
Right-click the Joiner operator and select Open Details. In the Joiner editor, change the name to PLG_JOINER. Click Next.

Because there are three input operators, add an input group. Enter INGRP3 in the Group column and make sure the Direction is set to INPUT. Click OK in the editor.

10.
Map the input groups from PRODUCT_DESCRIPTIONS, PRODUCT_INFORMATION, and the INPUT_SIGNATURE operators into the three input groups of the JOINER operator.

Map INPUT_SIGNATURE : INGRP1 to PLG_JOINER: INGRP1.

Map PRODUCT_DESCRIPTIONS : INOUTGRP1 to PLG_JOINER: INGRP2.

Map PRODUCT_INFORMATION : INOUTGRP1 to PLG_JOINER: INGRP3.

11.
Collapse the input groups of the PLG_JOINER operator.

From the OUTGRP1 of the PLG_JOINER operator, map the columns, PRODUCT_ID, SUPPLIER_ID, TRANSLATED_NAME, and TRANSLATED_DESCRIPTION into the OUTGRP1 of the OUTPUT_SIGNATURE.

Note: Hold the [Ctrl] key to multi-select the attributes.

The pluggable mapping definition is now complete. Click Save All to save your work. Close the PRD_DETAILS panel.

Back to Topic List

Reusing the Pluggable Mapping in a Mapping

In this topic, you learn how you can reuse a pluggable mapping in a new mapping.

Expand Databases > Oracle > TRGT_ENTERPRISE_MODEL. Right-click Mappings and select New Mapping.

In the Create Mapping dialog box, enter USE_PM and click OK. The USE_PM Mapping Editor canvas opens.

In the Projects Navigator panel, expand Pluggable Mappings > Standalone. Drag PRD_DETAILS on the canvas. Expand the operator.

Note, that the pluggable mapping appears just like any other operator with input and output groups.

Now, you can go ahead and use it with any operator in the mapping. By encapsulating the reusable logic in a pluggable mapping, the design of this mapping becomes simpler. Maintaining and updating mappings can now be done by updating the pluggable mapping rather than all the mappings with this logic. This makes the process less error prone as well as easy to do.

Back to Topic List

Summary

In this lesson, you learned how to:

Create and reuse pluggable mappings

Back to Topic List

1.	Under ETL_PROJECT, expand Pluggable Mappings. Right-click Standalone and select New Pluggable Mapping. The Create Pluggable Mapping wizard starts. Click Next on the Welcome page.
2.	Enter PRD_DETAILS as the name of the pluggable mapping. Click Next.
3.	Note that by default the pluggable mapping has an input group, INGRP1, and an output group, OUTGRP1. You can add or remove groups depending on the requirement. Note: The signature is a combination of input and output attributes flowing to and from the pluggable mapping. A pluggable mapping must have at least one input or output signature group. Most pluggable mappings are used in the middle of a logic flow and have input as well as output groups. Click Next.
4.	The input signature is the combination of input attributes that flow into the pluggable mapping. Add attributes to create the input for the product and language lookup. Enter PRODUCT_ID in the Attribute column. Accept the default data type. Enter LANGUAGE_ID in the Attribute column and change the data type to VARCHAR of size 2. Click Next.
5.	The output signature is the combination of output attributes that flow out of the pluggable mapping. For now, do not add any output attributes. Click Next. In the Summary page, click Finish.
6.	The pluggable mapping panel, PRD_DETAILS opens. Click Auto Layout to get the full view of the pluggable mapping. Now, you add the required operators and create a data flow between the operators.
7.	Add the source tables. In the Projects Navigator panel, expand SRC_OLTP > Tables. Drag PRODUCT_DESCRIPTIONS and PRODUCT_INFORMATION table on the canvas.
8.	Add a Joiner operator in the mapping. From the Component Palette, drag a Joiner operator on to the canvas.
9.	Right-click the Joiner operator and select Open Details. In the Joiner editor, change the name to PLG_JOINER. Click Next. Because there are three input operators, add an input group. Enter INGRP3 in the Group column and make sure the Direction is set to INPUT. Click OK in the editor.
10.	Map the input groups from PRODUCT_DESCRIPTIONS, PRODUCT_INFORMATION, and the INPUT_SIGNATURE operators into the three input groups of the JOINER operator. Map INPUT_SIGNATURE : INGRP1 to PLG_JOINER: INGRP1. Map PRODUCT_DESCRIPTIONS : INOUTGRP1 to PLG_JOINER: INGRP2. Map PRODUCT_INFORMATION : INOUTGRP1 to PLG_JOINER: INGRP3.
11.	Collapse the input groups of the PLG_JOINER operator. From the OUTGRP1 of the PLG_JOINER operator, map the columns, PRODUCT_ID, SUPPLIER_ID, TRANSLATED_NAME, and TRANSLATED_DESCRIPTION into the OUTGRP1 of the OUTPUT_SIGNATURE. Note: Hold the [Ctrl] key to multi-select the attributes. The pluggable mapping definition is now complete. Click Save All to save your work. Close the PRD_DETAILS panel.