Connecting Mapping Operators – the OWB 11GR2 way
By Kevin Yang on Dec 30, 2009
In OWB11GR2 release, we have a new feature in our Mapping Editor that allows users to connect mapping operators quicker and easier. This article will discuss the new feature "Quick Mapper" as well as other tips that will come in handy when you are building your Mapping in the Mapping Editor.
Let's use a simple Mapping for our illustration. This simple mapping loads data from a source Oracle Database table to a target Oracle Database table: SALES -> SALES_TGT
The completed mapping looks like this:
How do we arrive at this Mapping? In the Mapping Editor Canvas, we'll need to connect the attributes of the SALES table operator to those of the SALES_TGT table operator. Let's examine the different ways of making the connection.
1) Connect from Source Attribute to Target Attribute
You can make a connection between two attributes by drawing a line from a single output attribute of one operator to a single input attribute of another operator.
Draw in this article means the steps we can use to make a connection from source to target with Mouse. For example we want to draw a connection from a source attribute to a target attribute, the steps are:
1. Click and hold down the left mouse button while the pointer is positioned over an output attribute of source operator.
2. Drag the mouse away from the output attribute and toward the input attribute of the target operator.
As you drag the mouse, a line appears on the Mapping Editor canvas to indicate a connection.
3. Release the mouse over the input attribute.
4. Repeat Steps 1 through 3 until you create all the required data flow connections.
And you can also select more than one attribute per time by holding down the Ctrl key and selecting attributes. If you select multiple source attributes you can only release the mouse over a group and not over an output attribute. The "Quick Mapper" is displayed.
2) Connect from the Source Attribute Group to the Target Attribute Group
If you connect from one operator group to a target group with no existing attributes, it automatically copies the attributes and connects the attributes.
If you connect from an operator group to a target group containing attributes, the Mapping Connection Dialog Box, which is called "Quick Mapper", is displayed. (We will use "Quick Mapper" to instead of Mapping Connection Dialog Box for convenience and talk more details about it later.)
- You cannot connect to the same Target INPUT or INOUT attribute twice.
- You cannot connect attributes from and to the same operator.
- You cannot connect out of an INPUT-only attribute nor can you connect to an OUTPUT-only attribute.
3. Using "Quick Mapper"
1) Attribute Group to Connect
Use this section to select the source and target groups between which you want to establish connections. It is displayed only if you try to connect a source operator to a target group, a source group to a target operator, or a source operator to a target operator. As a default the groups are pre-selected based on the context the Quick Mapper is brought up.
The Connection Options section enables you to use different criteria to automatically connect all the source attributes to the target attributes.
- Copy Source Attributes to Target Group and Match
- Match By Position of Source and Target Attributes
- Match By Name of Source and Target Attributes
After you select one option that you use to connect attributes in the groups, click Preview to view the mapping between the source and target attributes in the Connections section. Review the mappings and click OK once you are satisfied.
Match By Position of Source and Target Attributes
Use this option to connect existing attributes based on the position of the attributes in their respective groups. If the source operator contains more attributes than the target, then the remaining source attributes are left unconnected.
Use this option to connect attributes with matching names. By selecting from the list of options, you can connect in an advanced way:
- Ignore case differences: Considers the same character in lower-case and upper-case a match. For example, the attributes FIRST_NAME and First_Name match.
- Ignore special characters: Specify characters to ignore during the matching process. For example, if you specify a minus and underscore, the attributes FIRST_NAME, FIRST-NAME, and FIRSTNAME all match.
- Ignore source prefix, Ignore source suffix, Ignore target prefix, Ignore target suffix: Specify prefixes and suffixes to ignore during matching. For example, if you select Ignore source prefix "USER_", then the source attribute USER_FIRST_NAME matches the target attribute FIRST_NAME.
Use this option to establish connections by typing manually in the Connections section. The details will be discussed in the Connections section.
This section displays any informational messages that result from previewing the connection options.
The Connections section displays the connections between the source attributes and the target attributes.
Source Connections Tab
The Source Connections tab enables you to quickly establish connections from the Source Group. Use this tab to specify the source attribute from which each target attribute is connected. For each target attribute, map zero or one source attribute. To connect a particular source attribute to the listed target attribute, for each target attribute, enter the name of the source attribute in the corresponding Source Attribute column.
As you begin typing an attribute name, Warehouse Builder displays a list containing the source attributes whose names begin with the letters you type. If you see the one you want to place here, select it. You can use wild cards such as * and ? for advanced search. You can also sort the columns listed under Target Attribute column. When the attribute name contains the space or comma characters, use double quotes to quote the name of the source attribute.
The Target Connections tab is most like Source Connections Tab. Except it is designed to quickly establish connections to the Target Group. Use this tab to specify the source attributes from which each target attribute is connected. For each source attribute, enter the name of one or more target attributes in the corresponding Target Attribute column.
Finally, after selecting the connection option you would like to use, click Preview to view the mapping between the source and target attributes in the Connections section. Review the mappings and click OK once you are satisfied.
Tips, we can use these connection options in sequence, for example:
1. Select "Match by name of source and target attributes" firstly:
2. Then using "Custom" to specify the un-connected attributes:
3. Click "OK", the connected operator looks like this:
1. Before making connections, you can sort the columns by clicking the header above the attribute group.
2. If there are hundreds attributes in a group and you don't need all of them, please don't put all of them into the input group of the Joiner operator, it will cost lots of additional resource (CPU and memory).