6
Designing Mappings

After you create and import data object definitions in Warehouse Builder, you can define extraction, transformation, and loading (ETL) operations that move data from sources to targets. In Warehouse Builder, you design these operations in a mapping.

This chapter contains the following topics that describe how to create, edit, and use mappings:

• About Mappings

• Creating a Mapping

• Adding Operators

• Editing Operators

• Connecting Operators

• Setting Operator Properties

• Reconciling Operators and Repository Objects

About Mappings

Mappings describe a series of operations that extract data from sources, transform it, and load it into targets. They provide a visual representation of the flow of the data and the operations performed on the data.

When you design a mapping in Warehouse Builder, you use the Mapping Editor interface. Alternatively, you can create and define mappings using OMB Plus, the scripting interface for Warehouse Builder. For information on how to create and define mappings in OMB Plus, see the Oracle9i Warehouse Builder Scripting Reference.

About Oracle Warehouse Modules

Before you can begin designing mappings, you must define an Oracle warehouse module. Warehouse Builder contains several types of modules. However, the Oracle warehouse module is the only module that stores mapping logic. For more information about Oracle warehouse modules, see "Creating Warehouse Modules".

Procedure for Defining Mappings

When you define a mapping, you create a container that holds the operators defining the ETL logic. To define a mapping, complete the following steps:

1. Creating a Mapping

2. Adding Operators

3. Editing Operators

4. Connecting Operators

5. Setting Operator Properties

6. Configuring Mappings Reference

7. Reconciling Operators and Repository Objects

After you define the mapping you can validate the code for the mapping. For information, see Chapter 12, "Validating Objects". You can also interrelate mappings by creating process flows. For more information, see Chapter 10, "Designing Process Flows".

Creating a Mapping

The first step in defining a mapping is to create a mapping using the Mapping Wizard.

To create a mapping:

1. Navigate to the Mappings node in a project. The Mappings node is located under an Oracle warehouse module which is contained under the Oracle folder under the Databases node.

   Figure 6-1 shows the Mappings node containing maps MAP1, MAP2, MAP3, and MAP4. The Oracle warehouse module in this example is named ORCL_MOD.

Figure 6-1 Mappings Node on the Navigation Tree

Text description of the illustration mapinnav.gif

2. Right-click Mappings and then select Create Mapping.

   Warehouse Builder opens the New Mapping Wizard.

3. Click Next.

   The New Mapping Wizard displays the Name page.

4. Enter a name and description for the new mapping.

5. Click OK.

Warehouse Builder stores the definition for the mapping and inserts its name in the warehouse module navigation tree. Warehouse Builder opens a Mapping Editor for the mapping you created. The Mapping Editor displays the name of the mapping in the title bar.

About the Mapping Editor

The Mapping Editor is the interface for designing, and editing mappings. The Mapping Editor includes a variety of operators that you add and connect to design a mapping. Figure 6-2 shows the Mapping Editor with three connected operators.

Figure 6-2 Mapping Editor Canvas

Text description of the illustration mapopico.gif

The Mapping Editor has the following components:

• Menu Bar: The menu bar provides access to the Mapping Editor commands.

• Toolbar: The toolbar provides access to commonly used commands.

• Toolbox: The toolbox contains operator icons. To include an operator, drag an operator icon on to the Mapping Editor canvas.

• Mapping Editor Canvas: The canvas provides the work space where you can create and modify mappings.

To open the Mapping Editor:

1. From the navigation tree, locate an Oracle warehouse module.

   These modules are located under the Oracle database folder in a project or collection, as shown in Figure 6-1. If your project or collection does not have a warehouse module, create one using the instructions in "Creating Warehouse Modules"

2. Expand the Mappings node.

3. Open the Mapping Editor in one of the following ways:
   • Double-click a mapping.

   • Select a mapping and then from the Object menu, select Editor.

   • Select a mapping and type Ctrl and O.

   • Right-click a mapping, and select Editor...

About Operators

The basic design element for a mapping is the operator. When you design a mapping in Warehouse Builder, you select operators from the Mapping Editor toolbox and drag them onto the canvas.

In Warehouse Builder, there are the following types of operators:

• Source and Target Operators: Source and target operators represent relational database objects and flat file objects in the mapping. For details on using SAP source data, see "Using SAP R/3 Data in Warehouse Builder".

• Data Flow Operators: Data flow operators transform data.

Source and Target Operators

Use source and target operators to represent relational database objects and flat file objects. Table 6-1 lists each source and target operator alphabetically, gives a brief description, and shows the associated icon in the Mapping Editor.

Table 6-1 Source and Target Operators  

Icon	Operator	Description
Text description of the illustration advanced.gif	Mapping Advanced Queue Operator	Represents an Advanced Queue you previously imported.
Text description of the illustration fact.gif	Mapping Cube	Represents a cube that you previously defined.
Text description of the illustration dimensio.gif	Mapping Dimension	Represents a dimension that you previously defined.
Text description of the illustration external.gif	Mapping External Table	Represents an external table that you previously defined or imported.
Text description of the illustration flatfila.gif	Mapping Flat File Operator	Represents a flat file that you previously defined or imported.
Text description of the illustration mview.gif	Mapping Materialized View	Represents a materialized view that you previously defined.
Text description of the illustration table.gif	Mapping Table	Represents a table that you previously defined or imported.
Text description of the illustration view.gif	Mapping View	Represents a view that you previously defined or imported.

Data Flow Operators

Use data flow operators to transform data in a mapping. Table 6-2 lists each data flow operator alphabetically, gives a brief description, and shows the associated icon in the Mapping Editor. For more information on operators, see Chapter 8, "Using Mapping Operators".

Table 6-2 Data Flow Operators  

Icon	Operator	Description
Text description of the illustration aggregat.gif	Aggregator Operator	Performs data aggregations, such as SUM and AVG, and provides an output row set with aggregated data.
Text description of the illustration constant.gif	Constant Operator	Produces a single output group that can contains one or more constant attributes.
Text description of the illustration data_gen.gif	Data Generator Operator	Provides information such as record number, system date, and sequence values.
Text description of the illustration deduplic.gif	Deduplicator Operator	Removes duplicate data in a source by placing a DISTINCT clause in the select code represented by the mapping.
Text description of the illustration expressi.gif	Expression Operator	Enables you to write SQL expressions that define non-procedural algorithms for one output parameter of the operator. The expression text can contain combinations of input parameter names, variable names, and library functions.
Text description of the illustration filter.gif	Filter Operator	Conditionally filters out rows from a row set.
Text description of the illustration joiner.gif	Joiner Operator	Joins multiple row sets from different sources with different cardinalities and produces a single output row set.
Text description of the illustration key_look.gif	Key Lookup Operator	Performs a lookup of data from a lookup object such as a table, view, cube, or dimension.
Text description of the illustration input_pa.gif	Mapping Input Parameter Operator	Passes parameter values into a mapping.
Text description of the illustration output_p.gif	Mapping Output Parameter Operator	Sends values out of a mapping.
Text description of the illustration sequence.gif	Mapping Sequence Operator	Generates sequential numbers that increment for each row.
Text description of the illustration name_add.gif	Name and Address Operator	Identifies and corrects errors and inconsistencies in name and address source data.
Text description of the illustration pivotico.gif	Pivot Operator	Transforms a single row of attributes into multiple rows. Use this operator to transform data that contained across attributes instead of rows.
Text description of the illustration post_map.gif	Post-Mapping Process Operator	Calls a function or procedure after executing a mapping
Text description of the illustration pre_map.gif	Pre-Mapping Process Operator	Calls a function or procedure prior to executing a mapping
Text description of the illustration set.gif	Set Operation Operator	Performs union, union all, intersect, and minus operations in a mapping.
Text description of the illustration sorter.gif	Sorter Operator	Sorts attributes in ascending or descending order.
Text description of the illustration splitter.gif	Splitter Operator	Splits a single input row set into several output row sets using a boolean split condition.
Text description of the illustration tablefun.gif	Table Function Operator	Enables you to develop custom code to manipulate a set of input rows and return a set of output rows of the same or different cardinality that can be queried like a physical table.
Text description of the illustration transf.gif	Transformation Operator	Transforms the attribute value data of rows within a row set using a PL/SQL function or procedure.
Text description of the illustration unpivoti.gif	Unpivot Operator	Converts multiple input rows into one output row. It allows you to extract from a source once and produce one row from a set of source rows that are grouped by attributes in the source data.

Adding Operators

For every source and target operator you add, Warehouse Builder creates and maintains a version of that object in the Warehouse Builder repository and a separate version for the Mapping Editor. For example, when you add a table to a mapping, Warehouse Builder maintains a copy of the table in the repository. Refer to it as the repository table. Refer to the table in the mapping as the mapping table.

In addition to maintaining separate repository objects and mapping objects for sources and targets, Warehouse Builder also maintains separate repository objects for the following data flow operators:

• Key LookUps

• Mapping Input Parameters

• Mapping Output Parameters

• Mapping Sequences

• Transformations

Warehouse Builder maintains separate repository objects so that you can reconcile changing definitions of these objects. For example, as you make changes to a mapping table, you may want to propagate those changes back to the repository table associated with it. Conversely, if you import a new metadata definition for the repository table, you may want to propagate those changes to the mapping table. You can accomplish these tasks by a process known as reconciliation. When you reconcile, you bind one object definition to another. For more information on binding objects, see "Reconciling Operators and Repository Objects".

The concept of binding a mapping operator to a repository object is important because it affects how you add operators to a mapping.

To add an operator to a mapping:

1. Open the Mapping Editor.

2. From the Mapping menu, select Add and select an operator. Alternatively, you can drag an operator icon from the toolbox and drop it onto the Mapping Editor canvas.

   If you select an operator that you can bind to a repository object, the Mapping Editor displays the Add Mapping <operator name> dialog. For details on how to use this dialog, see "Adding Bindable Operators".

   If you select an operator that you cannot bind to a repository object, Warehouse Builder may display a wizard or dialog to assist you in creating the operator. For more information, refer to Chapter 8, "Using Mapping Operators", which provides an alphabetical listing of each operator and how to use them when designing mappings.

3. Follow any prompts Warehouse Builder displays and click OK.

   The Mapping Editor displays the operator maximized on the canvas as shown in Figure 6-3. You can view each attribute name and data type. The operator name appears in the upper left corner. If you want to minimize the icon, click on the arrow in the upper right corner.

Figure 6-3 Mapping Editor Showing a Mapping Table Operator Source

Text description of the illustration mappinga.gif

Adding Bindable Operators

Use this section for information on adding bindable operators to a mapping. Bindable operators include the following operators listed in a columnar table that reads down the columns left to right to conserve space:

Mapping Tables	Mapping Materialized Views
Mapping External Tables	Key LookUps
Mapping Flat Files	Mapping Sequences
Mapping Dimensions	Transformations
Mapping Advanced Queues	Pre Mapping Process
Mapping Cubes	Post Mapping Process
Mapping Views	N/A

When you add an operator that you can bind to a repository object, the Mapping Editor displays the Add Mapping <operator name> dialog. Figure 6-4 shows the dialog for adding a mapping table operator.

Figure 6-4 Add Mapping Table Dialog

Text description of the illustration addtable.gif

Select one of the four options. Depending on the type of operator you select, some of the options may be greyed out.

• Create Unbound Object with No Attributes

• Create New Repository Object and Bind

• Import Object into Repository and Bind

• Select from Existing Repository Object and Bind

Create Unbound Object with No Attributes

Use this option when you want to use the Mapping Editor to define a new object. After you select Create Unbound Object with No Attributes, type a name for the new object. Warehouse Builder displays the operator on the canvas without any attributes. For an example on how to quickly create a staging table using an unbound table operator and the Auto-Mapping dialog, see "Example: Using the Mapping Editor to Create Staging Area Tables".

Create New Repository Object and Bind

Use this option when you want to invoke a wizard to create a new object. First select a target module and then Warehouse Builder launches a wizard that assists you in creating the new operator. After you complete the wizard, Warehouse Builder saves a copy of the operator in the repository. For example. if you select this option when adding a mapping table, Warehouse Builder launches the New Table Wizard, creates the mapping table, creates the repository table, and binds the mapping table to the repository table.

Import Object into Repository and Bind

Use this option when you want to create an operator based on an object that you can import into the repository. Select a module into which you import the object.

If you select a module without a database link defined, Warehouse Builder displays the New Database Link Information dialog. See "Configuring Connections for Database Sources" for information on how to define a new database link.

If you select a module with a defined database link, Warehouse Builder displays the Import Wizard. Enter the appropriate information in the dialog.

Select from Existing Repository Object and Bind

Use this option when you want to add an operator to the mapping based on an object you previously defined or imported into the repository.

Either type the prefix to search for the object or select from the displayed list of objects within the selected module.

To select multiple items, press the Control key as you click each item. To select a group of items located in a series, click the first object in your selection range, press the Shift key, and then click the last object.

You can add operators based on repository objects within the same module as the mapping or from other modules. If you select a repository object from another module, the Mapping Editor creates a connector if one does not already exist. The connector establishes a path for moving data between the mapping location and the location of the repository object. For more information about locations and connectors, see "Defining Locations"

Editing Operators

Each operator has an editor associated with it. Use the operator editor to specify general and structural information for operators, groups, and attributes. In the operator editor you can add, remove, or rename groups and attributes. You can also rename an operator.

To specify loading properties and conditional behaviors, use the properties windows. For information on properties windows, see "Mapping Naming Conventions".

To edit an operator, group, or attribute:

1. Select an operator from the Mapping Editor canvas.

   Or select any group or attribute within an operator.

2. Right-click and select Edit.

   The Mapping Editor displays the operator editor with a General tab, Groups tab, and a tab for each type of group in the operator. Figure 6-5 shows the General tab on the operator editor.

   The General tab displays the operator name and an optional description. You can rename the operator and add a description. Name the operator according to the conventions listed in "Naming Conventions for Operators"

Figure 6-5 General Tab on the Operator Editor

Text description of the illustration opedgene.gif

3. Edit group information on the Groups tab as shown in Figure 6-6.

   Each group has a name, direction, and optional description. You can rename the group but you cannot change the group direction. A group can have one of these directions: Input, Output, Input/Output.

   Depending on the operator, you can add and remove groups from the Groups tab. For example, you add input groups to Joiners and output groups to Splitters.

   The operator editor displays a tab for each type of group displayed on the Groups tab. Each of these tabs displays the attribute name, data type, length, precision, scale, and optional description.

Figure 6-6 Groups Tab on the Operator Editor

Text description of the illustration opedgrou.gif

4. Edit attribute information on the each of the remaining tabs.

   Figure 6-7 shows an Input/Output tab on the Operator Editor. In this example, the operator is a table and therefore has only the Input/Output tab. Other operators can have an Input tab and an Output tab.

Figure 6-7 Input/Output Tab on the Operator Editor

Text description of the illustration opedinou.gif

You can add, remove, and edit attributes. The Mapping Editor greys out properties that you cannot edit. For example, if the data type is NUMBER, you can edit the precision and scale but not the length.

To assign correct values for datatype, length, precision, and scale in an attribute, follow PL/SQL rules. When you reconcile the operator, Warehouse Builder checks the attributes based on SQL rules.

Mapping Naming Conventions

When you name or rename objects in the Mapping Editor, use the following naming conventions.

Naming Conventions for Attributes and Groups

Attribute and group names are logical. Although the attribute names of the object are often the same as the attribute names of the operator, their properties remain independent of the attributes of the operator to which they are bound. This protects any expression or use of an attribute from corruption if it is manipulated within the operator. You can rename groups and attributes independent of their sources.

Naming Conventions for Operators

When you add operators to a mapping, the Mapping Editor displays the physical name or the business name of the operator, depending on which naming mode you selected in the project Preferences window. See "Naming Preferences" for details on how to specify a naming mode.

Operator names display as business names if you select the business naming mode in the project Preferences window. When you add an operator to a mapping while working in the business naming mode, the Mapping Editor generates a default business name for the operator. You can change the business name to any name that meet the following requirements:

• The length of the operator name can be any string of 200 characters. You must use double quotes for names containing spaces.

• The operator name must be unique on its attribute group, attribute and display set level with respect to its parent.

Operator names display as physical names if you select the physical naming mode in the Project Preferences window. When you use the Warehouse Builder OMB Scripting Language, you navigate to operators based on their physical names. Because the physical name is used during scripting, physical names must meet the following requirements:

• The operator name must be unique on its group, attribute and display set level with respect to its parent.

• The operator name must contain no more than 28 characters. Two characters are used by Warehouse Builder.

• The operator name must conform to the syntax rules for basic elements as defined in the Oracle9i SQL Reference.

In addition to physical and business names, operators also have bound names. Bound names are available as a logical and physical property to operators that can be bound. They are used to reference the object during code generation and have the following characteristics:

• Bound names need not be unique.

• Bound names must conform to the general Warehouse Builder physical naming rules.

• Typically, you do not change bound names directly but perform an outbound reconciliation.

• When you rename the logical name for an operator or attribute, Warehouse Builder propagates the new logical name as the bound name when you perform an outbound reconciliation. However, logical names can be up to 200 character while bound names are limited to 30 characters. Therefore, Warehouse Builder uses the first 30 characters of the logical name for the bound name.

Using Display Sets

A display set is a graphical representation of a subset of attributes. Use display sets to limit the number of attributes visible in an operator and simplify the display of a complex mapping.

By default, the operators contain a display set named ALL that shows all attributes contained in the repository object represented by the operator.

Operators also contain a default display set named MAPPED that includes only those attributes in a group that are connected to another operator in a mapping. The MAPPED display set updates automatically whenever attribute connections are changed in the Mapping Editor. Table 6-3 describes the default Display Sets.

Table 6-3 Default Sets  

Display Set	Description
All	Includes all attributes in an operator.
Mapped	Includes only those attributes in an operator that are connected to another operator.
Hierarchies	For each hierarchy in a Dimension, a display set containing all the level attributes in that hierarchy.

See "Adding Attribute Sets" for information on attribute sets. See "Editing Operators" for information on operator attributes and groups.

Defining Display Sets

You can define display sets for any operator in a mapping.

To define a display set:

1. Right-click a group in an operator, and select Display Set.

   The Display Set dialog appears, as shown in Figure 6-8.

Figure 6-8 Display Set Dialog

Text description of the illustration displays.gif

2. Click Add.

3. Type a name in the Name column and press Enter.

   All available attributes for the operator appear in Attributes of the selected attribute set field. The Type column is automatically set to USER_DEFINED.

   You cannot edit or delete a PREDEFINED Attribute Set.

4. In the Include column, select each attribute you want to include in the display set.

   Click Select All to include all attributes and Deselect All to exclude all the attributes.

5. Click OK.

The group for the operator now lists only those attributes contained within the Attribute Set selected for display.

Selecting a Display Set

If a group contains more than one display set, you can select a different display set from a list using the View menu.

To select a Display Set:

1. Right-click a group in an operator.

2. Select Use Display Set and select the display set.

Compressing Mappings

When you add a source or target operator to a mapping, all of the columns from the physical source or target are added as attributes to the operator. Some of these attributes may be unnecessary in the mapping. By compressing a mapping, any source or target mapping attribute that is not connected to any other attribute is deleted from the mapping. This simplifies the display of the mapping and improves performance when importing and exporting MDL files.

After a mapping is compressed, you can only undo the compression by performing an inbound reconciliation. Once you reconcile outbound, the compressed mapping is saved in the repository and cannot be rolled back.

To compress a mapping:

1. Open a mapping in the Mapping Editor.

2. From the Mapping menu, select Compress Mapping.

   A warning message displays stating that unconnected Oracle relational source and target attributes will be deleted, as shown in Figure 6-9.

Figure 6-9 Confirm Compress Map Operation Dialog

Text description of the illustration mapcompr.gif

3. Click Yes to continue. Any source or target mapping attribute that is not connected to any other attribute is deleted from the mapping except for unconnected attributes that are part of an update or delete condition and flat file sources.

Connecting Operators

After you have selected mapping source operators, data flow operators, and target operators, you are ready to connect them. Data flow connections graphically represent how the data flows from a source, through operators, and to a target.

You can connect operators by one of the following methods:

• Connecting Attributes: Connect individual operator attributes to each other.

• Connecting Groups: When you connect groups, you can control which attributes are connected using the Auto-Mapping dialog.

The position of the attribute where you start your mapping and the position where you release the mouse button determines the type of data flow connection you make in the mapping.

Connecting Attributes

You can draw a line from a single output attribute of one operator to a single input attribute of another operator.

To connect attributes:

1. Click and hold down the mouse button while the pointer is positioned over an output attribute.

2. Drag the mouse away from the output attribute and toward the input attribute to which you want data to flow.

   As you drag the mouse, a line appears on the Mapping Editor canvas to indicate a connection, as shown in Figure 6-10.

3. Release the mouse over the input attribute.

Figure 6-10 Connected Operators in a Mapping

Text description of the illustration mappinge.gif

Repeat steps one through three until you have created all the data flow connections appropriate for your situation.

When connecting attributes, keep the following rules in mind:

• You cannot connect to the same input attribute twice.

• You cannot connect attributes within the same operator.

• You cannot connect out of input only attribute.

• You cannot connect into an output only attribute.

• You cannot create mapping lines that contradict an established cardinality. Instead, use a Join operator.

Connecting Groups

When you connect groups, the Mapping Editor assists you by either automatically copying the attributes or prompts you for more information in the Auto-Mapping dialog described in "Using the Auto-Mapping Dialog".

If you connect to a target group with no existing attributes, the Mapping Editor automatically copies the attributes and creates the mapping lines. This is useful for designing mappings such shown in "Example: Using the Mapping Editor to Create Staging Area Tables"

Example: Using the Mapping Editor to Create Staging Area Tables

You can use the Mapping Editor with an unbound table operator to quickly create staging area tables.

The following instructions describe how to create a staging table based on an existing source table. You can also use these instructions to create views, materialized views, flat files, and transformations.

To map a source table to a staging table:

1. In the Mapping Editor, add the source table.

   From the menu bar, select Mappings and Add. Use the Add Mapping Table dialog to select and bind the source table operator in the mapping.

2. Add a new unbound table operator.

   From the menu bar, select Mappings and Add. From the Add Mapping Table dialog, select Create unbound mapping table with no attributes. The mapping should now resemble Figure 6-11 with one source table and one staging area table without attributes.

Figure 6-11 Unbound Staging Table without Attributes and Source Table

Text description of the illustration automaps.gif

3. With the mouse button positioned over the group in the operator for the source table, click and hold down the mouse button.

4. Drag the mouse to the staging area table group.

   Warehouse Builder copies the source attributes to the staging area table and creates the corresponding mapping lines.

5. In the Mapping Editor, select the unbound table you added to the mapping. Right click and select Reconcile Outbound. Warehouse Builder displays the Outbound Reconcile Operator dialog as shown in Figure 6-12.

Figure 6-12 Outbound Reconcile Operator Dialog

Text description of the illustration reconcia.gif

6. Select Create a new table and specify the target module in which to create the table.

   Warehouse Builder creates the new table in the target module you specify.

Using the Auto-Mapping Dialog

If you connect to a target with existing attributes, the Mapping Editor launches the Auto-Mapping dialog as shown in Figure 6-13.

Select one of the following rules for copying and creating mapping lines:

• Copy Source Attributes to Target Group with Existing Attributes

• Match by Position of Source and Target Attributes

• Match by Name of Source and Target Attributes

Figure 6-13 Auto-Mapping Dialog Displaying Attributes to be Mapped

Text description of the illustration automape.gif

After you select one of the three options, select Go. The Auto-Mapping dialog displays a list of the source and target attributes to be mapped as shown in Figure 6-14.

Figure 6-14 Displayed Mappings

Text description of the illustration displaye.gif

You can deselect attributes by clearing the Map checkbox. The Auto-Mapping describes the results of your selections under Comments. See "About Auto-Mapping Dialog Comments"for more information about these comments.

When you select OK, Warehouse Builder copies the source attributes to the target group and creates mapping lines between the source and target group.

Copy Source Attributes to Target Group with Existing Attributes

Use this option to copy source attributes to a target group that already contain attributes. Warehouse Builder creates mapping lines from the source attributes to the new target attributes based on the selections you make in the Auto-Mapping dialog. Warehouse Builder does not perform this operation on target groups that do not accept new input attributes such as dimension and cube target operators.

Match by Position of Source and Target Attributes

Use this option to create mapping lines between existing attributes based on the position of the attributes in their respective groups. Source and target attributes are matched in order, until all attributes for a target are matched. If a source operator contains more attributes than a target, then the remaining source attributes do not map to a target.

Match by Name of Source and Target Attributes

Use this option to create mapping lines between existing attributes with matching names, as shown in Figure 6-15. By selecting from the list of options, you can specify auto-mapping between names that do not match exactly. You can combine these options:

• Ignore case differences: Enables lower-case and upper-case characters to match. For example, the attributes FIRST_NAME and First_Name will match.

• Ignore special characters: Enables you to specify characters to ignore during the matching process. Enter the characters into the text field to the right of this option. For example, if you specify a hyphen and underscore, the attributes FIRST_NAME, FIRST-NAME, and FIRSTNAME will all match.

• Ignore source prefix, Ignore source suffix, Ignore target prefix, Ignore target suffix: Enables you to specify prefixes and suffixes to ignore during matching. For example, if you select Ignore source prefix and enter USER_ into the text field, then the source attribute USER_FIRST_NAME will match the target attribute FIRST_NAME.

Figure 6-15 Match by Name Matching Options

Text description of the illustration matchopt.gif

After you set the matching criteria, click Go.

If you attempt to map a source group with no attributes, or if you are matching by name and there are no matches, an error dialog displays.

If one or more of the attributes can be matched, the Displayed Mappings field displays the matches. You can verify and deselect the mapping lines before they are implemented.

About Auto-Mapping Dialog Comments

In the Displayed Mapping area of the Auto-Mapping dialog, the Comments column contains information about the results from your matching criteria.

When a mapping results in duplicated attributes, the following messages appear:

• Target was already mapped: A target attribute can take part in only one mapping. This target attribute is already used in a mapping.

• Target may not be double-mapped: If name matching finds multiple matches to a target attribute, only one mapping can be created. The first mapping is selected, and all other mappings to that target attribute are not selected. Selecting one attribute deselects any other attribute.

• Source may be double-mapped: A source attribute can be mapped to different target attributes. If name matching finds multiple matches from a source attribute, all of these mappings can be created. You can then deselect the target attributes that you do not want to map to by clicking their check boxes.

When a source group has more attributes than a target group or a target group has more attributes than a source group, the following messages appear:

• Source will not be mapped: No target attributes are available for the source.

• Target will not be mapped: No source attributes are available for the target.

If you open the Auto-Mapping dialog while the Mapping Editor is in read-only mode, an error displays. You must have read-write permission before you can use auto-mapping. See "Supporting Multiple Users" for more information on read-write permissions.

Setting Operator Properties

This section discusses how to set properties for the following operators in a mapping:

• Source and Target Operator Properties

• Attribute Properties

• Flat File Operators Properties

For information on setting other operator types, see the following sections:

• Chapter 21, "Using SAP R/3 Data in Warehouse Builder"

• Chapter 8, "Using Mapping Operators"

Each operator, group, and attribute has a properties window associated with it. Use properties windows to specify loading settings and other conditional settings. You can view and set the following types of properties:

• Operator Properties: Properties that affect the operator as a whole. The properties you can set depend upon the operator type.

• Group Properties: Properties that affect a group of attributes. Most operators do not have properties for their groups. Examples of operators that do have group properties include the splitter operator and the deduplicator. For information on how to set properties for groups, look up the operator by name in Chapter 8, "Using Mapping Operators".

• Attribute Properties: Properties that pertain to attributes in source and target operators. Examples of attribute properties are data type, precision, and scale.

  To add, delete, or rename attributes, use the operator editor. For information on the operator editor, see "Editing Operators"

To open the properties windows:

1. Select an operator in the Mapping Editor and double-click it or right-click and select Operator Properties.

   The Mapping Editor displays the properties window for the object you select. Figure 6-16 shows the properties window for a mapping table operator.

Figure 6-16 Properties Window for a Mapping Table Operator

Text description of the illustration oppropta.gif

2. Click on one of the properties.

   The properties window displays the name of the property you selected in the title bar.

   The Mapping Editor displays a description for the property in a window at the bottom of the properties window. You can expand the window to view the description.

3. For objects that contain many properties, click on the flashlight icon in the upper left corner and enter the property name to search for in Find.

4. To sort the list alphabetically, click on the plus icon in the upper left corner.

   By default, the properties window lists the properties by category.

Source and Target Operator Properties

The properties window contains the following categories of parameters for source and target operators:

• Operator Name: Under the operator name, you can view the Bound Name, and set Primary Source indicator, and the Loading Type.

• Conditional Loading: You can set Target Filter for Update, Delete Target Condition, and Match By Constraint.

• Keys (read-only): You can view the Key Name, Key Type, and Referenced Keys. If the operator functions as a source, the key settings are used in conjunction with the join operator. If the operator functions as a target, the key settings are used in conjunction with the Match By Constraint parameter.

The operator properties window displays as shown in Figure 6-17.

Figure 6-17 Properties Window for Mapping Table Operator

Text description of the illustration matchcon.gif

Bound Name

The name used by the code generator. If an operator is currently bound and reconciled, then this property is read-only. If an operator is not yet bound, you can edit the bound name within the Mapping Editor before you reconcile it to a repository object.

Primary Source

For Oracle Application Embedded Data Warehouse (EDW) users, refer to EDW documentation. For all other users, disregard this parameter.

Loading Types for Oracle Target Operators

Select a loading type for each target operator.

• INSERT: The incoming row sets are inserted into the data target. Insert fails if a row already exists with the same primary or unique key.

• UPDATE: The incoming row sets are used to update existing rows in the data target. If no rows exist for the specified match conditions, no changes are made.

• INSERT/UPDATE: For each incoming row, an insert operation is performed first. If the insert fails, an update operation occurs. If there is no matching records for update, the insert is performed. If you select Insert/Update and the default mode is row based, you must set unique constraints on the target. If you select Insert/Update and the default operating mode is set based, Warehouse Builder generates a merge statement to Oracle9i.

• UPDATE/INSERT: For each incoming row, an update operation is performed first. If you select Update/Insert and the default operating mode for the target is set-based, Warehouse Builder generates a merge statement to Oracle9i.

• DELETE: The incoming row sets are used to determine which of the rows at the target get deleted.

• TRUNCATE/INSERT: The data target is truncated before the incoming row set is inserted into the data target. If you choose this option, the procedure cannot be rolled back even if the execution of the mapping fails.

• DELETE/INSERT: The rows in the data target are deleted before the incoming row set is inserted into the data target.

• CHECK/INSERT: The data target is checked to see if it contains any rows. If not, the incoming row sets are inserted into the data target.

• None: No operation is performed on the data target. This setting is useful for testing. Extraction and transformations run but have no effect on the target.

Loading Types for Flat File Targets

Configure SQL*Loader parameters to define SQL*Loader options for your mapping. The values chosen during configuration directly affect the content of the generated SQL*Loader and the runtime control files. SQL*Loader provides two methods for loading data:

• Conventional Path Load: Executes an SQL INSERT statement to populate tables in an Oracle database.

• Direct Path Load: Eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. Because a direct load does not compete with other users for database resources, it can usually load data at or near disk speed.

  Certain considerations such as restrictions, security, and backup implications are inherent to each method of access to database files. See Oracle9i Database Utilities for more information.

  When designing and implementing a mapping that extracts data from a flat file using SQL*Loader, you can configure different properties affecting the generated SQL*Loader script. Each load operator in a mapping has an operator property called Loading Types. The value contained by this property affects how the SQL*Loader INTO TABLE clause for that load operator is generated. Although SQL*Loader can append, insert, replace, or truncate data, it cannot update any data during its processing. Table 6-4 lists the INTO TABLE clauses associated with each load type and their affect on data in the existing targets.

  Table 6-4 Loading Types and INTO TABLE Relationship  

  Loading Types	INTO TABLE Clause	Affect on Target with Existing Data
  INSERT/UPDATE	APPEND	Adds additional data to target.
  DELETE/INSERT	REPLACE	Removes existing data and replaces with new (DELETE trigger fires).
  TRUNCATE/INSERT	TRUNCATE	Removes existing data and replaces with new (DELETE trigger fires).
  CHECK/INSERT	INSERT	Assumes target table is empty.
  NONE	INSERT	Assumes target table is empty.

Target Filter for Update

If the condition evaluates to true, the row is included in the update loading operation.

Delete Target Condition

If evaluated to true, the row is included in the delete loading operation.

Match By Constraint

When loading target operators with the UPDATE or the DELETE conditions, you can specify matching criteria. You can set matching and loading criteria manually or choose from several built-in options. Use Match By Constraint to indicate whether unique or primary key information on a target overrides the manual matching and loading criteria set on its attributes. When you click on the property Match By Constraint, Warehouse Builder displays a drop down box listing the constraints defined on that operator and the built-in loading options, as shown in Figure 6-18.

Figure 6-18 Match By Constraint Options for Operators

Text description of the illustration matchbya.gif

If you select All Constraints, all manual attribute load settings are overruled and the data is loaded as if the load and match properties of the target attributes were set as displayed in Table 6-5.

Table 6-5 All Constraints Target Load Settings  

Load Setting	Key Attribute	All Other Attributes
Load Column when Updating Row	NO	YES
Match Column when Updating Row	YES	NO
Match Column when Deleting Row	YES	NO

If you select No Constraints, all manual load settings are honored and the data is loaded accordingly.

If you select a constraint previously defined for the operator, all manual attribute load settings are overruled and the data is loaded as if the load and match properties of the target were set as displayed in Table 6-6.

Table 6-6 Target Load Settings for a Selected Constraint  

Load Setting	Selected Key Attributes	All Other Attributes
Load Column when Updating Row	NO	YES
Match Column when Updating Row	YES	NO
Match Column when Deleting Row	YES	NO

If you made changes at the attribute level and you want to default all settings, click Advanced. Warehouse Builder displays a drop down box listing the loading options as shown in Figure 6-19. Warehouse Builder defaults the settings based on the constraint type you select.

Figure 6-19 Advanced Settings for Match By Constraint

Text description of the illustration matchbyc.gif

For example, if you want to reset the match properties for all key attributes, click Advanced, select No Constraints, and click OK. Warehouse Builder overwrites the manual load settings and loads the data based on the settings displayed in Table 6-7.

Table 6-7 Default Load Settings for Advanced No Constraints  

Load Setting	All Key Attributes	All Other Attributes
Load Column when Updating Row	YES	YES
Match Column when Updating Row	NO	NO
Match Column when Deleting Row	NO	NO

Alternatively, if you click Advanced and select All Constraints, Warehouse Builder overwrites the manual load settings and loads the data based on the settings displayed in Table 6-8

Table 6-8 Default Load Settings for Advanced All Constraints  

Load Setting	All Key Attributes	All Other Attributes
Load Column when Updating Row	NO	YES
Match Column when Updating Row	YES	NO
Match Column when Deleting Row	YES	NO

Key Name

Name of the primary, foreign, or unique key.

Key Columns

Local columns that define this key. Each key column is comma-separated if the operator contains more than one key column.

Key Type

Type of key, either primary, foreign, or unique.

Referenced Keys

If the operator contains a foreign key, Referenced Keys displays the primary key or unique key for the referenced object.

Attribute Properties

For each attribute in a source and target operator, parameters are categorized into the following types:

• Attribute Properties: Under the attribute properties, you can view the Bound Name and set the Data Type, Precision, Scale, and Length.

• Loading Properties: The mapping table, mapping dimension, mapping cube, mapping view, and mapping materialized view operators have a Loading Properties category. This category contains the following settings: Load Column When Inserting Row, Load Column When Updating Row, Match Column When Updating Row, Update: Operation, and Match Column When Deleting Row.

Figure 6-20 shows the attribute parameters for mapping tables, mapping dimensions, mapping views, and mapping materialized views.

Figure 6-20 Operator Attribute Properties Window

Text description of the illustration oppropsa.gif

Bound Name

Name used by the code generator to identify this item. By default, it is the same name as the item. This is a read-only setting when the operator is unbound.

Data Type

Data type of the attribute.

Precision

The maximum number of digits this attribute will have if the data type of this attribute is a number or a float. This is a read-only setting.

Scale

The number of digits to the right of the decimal point. This only applies to number attributes.

Length

The maximum length for a CHAR, VARCHAR, or VARCHAR2 attribute.

Load Column When Inserting Row

This setting prevents data from moving to a target even though it is mapped to do so. If you select Yes (default), the data will reach the mapped target.

Load Column When Updating Row

This setting prevents the selected attribute data from moving to a target even though it is mapped to do so. If you select Yes (default), the data reaches the mapped target attribute. If all columns of a unique key are not mapped, then the unique key is not used to construct the match condition. If no columns of a unique key are mapped, Warehouse Builder displays an error. If a column (not a key column) is not mapped, then it is not used in loading.

Match Column When Updating Row

This setting updates a data target row only if there is a match between the source attribute and mapped target attribute. If a match is found, then an update occurs on the row. If you set this property to Yes (default), the attribute is used as a matching attribute. If you use this setting, then all the key columns must be mapped. If there is only one unique key defined on the target entity, use constraints to override this setting.

Update: Operation

You can specify an update operation to be performed when Warehouse Builder locates a matching row and updates the target. An update operation is performed on the target attribute using the data of the source attribute. Table 6-9 lists the update operations you can specify and describes the update operation logic.

Table 6-9 Update Operations  

Operation	Example	Result If Source Value = 5 and Target Value = 10
=	TARGET = SOURCE	TARGET = 5
+=	TARGET = SOURCE + TARGET	TARGET = 15 (5 + 10)
-=	TARGET = TARGET - SOURCE	TARGET = 5 (10 - 5)
=-	TARGET = SOURCE - TARGET	TARGET = negative 5 (5 - 10)
||=	TARGET = TARGET || SOURCE	TARGET = 105 (10 concatenated with 5)
=||	TARGET = SOURCE || TARGET	TARGET = 510 (5 concatenated with 10)

Match Column When Deleting Row

Deletes a data target row only if there is a match between the source attribute and mapped target attribute. If a match is found, then a delete occurs on the row. If you set this property to Yes (default), the attribute is used as a matching attribute. Constraints can override this setting.

Flat File Operators Properties

You can set properties for a flat file operator as either a source or target. You can set Loading Types and the Field Names in the First Row setting. All other settings are read-only and depend upon how you imported the flat file. The operator properties window displays as shown in Figure 6-21.

Figure 6-21 Properties Window for Mapping Flat File Operator

Text description of the illustration oppropsf.gif

Loading Types

Select a loading type from the drop-down list:

• Insert: Creates a new target file. If a target file already exists, then it is replaced with a new target file.

• Update: Creates a new target file. If a target file already exists, then it is appended.

• None: No operation is performed on the data target. This setting is useful for test runs where all transformations and extractions are run but have no effect on the target.

Field Names in the First Row

Set this property to True if you want to write the field names in the first row of the operator or False if you do not.

Reconciling Operators and Repository Objects

For every operator you define in a mapping, there can be a corresponding definition in the Warehouse Builder repository. When you make changes to an operator in a mapping, you may want to reconcile the operator and its corresponding repository definition.

When you reconcile operators, you ensure that the operator matches the repository object it represents. You can reconcile in one of two ways:

• Inbound Reconciliation: This updates or reconciles the operator with the definition of a specified repository object.

• Outbound Reconciliation: This updates a selected repository object to reflect changes you made to the operator in a mapping.

Reconciliation is different from synchronization. Synchronization ensures that you are up-to-date with changes made by other users in a multi-user environment. Reconciliation updates operators with their corresponding repository objects.

Inbound Reconciliation

Inbound reconciliation updates the operator with the definition of a specified repository object. You can also use Inbound Reconcile to bind an unbound operator to a repository object. You can reconcile an operator by name, position, object identifier match, or any combination of these methods.

You can use inbound reconciliation for any of the following reasons:

• Propagate changes: Use inbound reconciliation to propagate changes you made in a repository object to its associated operator. Changes to the repository object can include structural changes, attribute name changes, attribute data type changes.

• Update an operator based on a new repository object: You can associate an operator with a new repository object. For example, if you are migrating mappings from one version of a data warehouse to a newer version and you maintain different object definitions for each version. Or, if you want to provide access to the underlying source data through views rather than directly against the base tables.

• Prototype mappings using tables: When you are satisfied with the transformation logic, you can switch to other object types for production mappings such as views, materialized views, or cubes.

Unless you remove attributes, inbound reconciliation has no impact on the dependent relationship between other operators in the mapping to repository objects. Warehouse Builder preserves those dependencies.

When you perform inbound reconciliation on an external table operator, Warehouse Builder updates the operator based on the repository external table only and not its associated flat file. To update an external table based on its associated flat file, see "Reconciling an External Table Definition with a Record in a File".

To perform inbound reconciliation on an operator:

1. Select an operator on the Mapping Editor canvas.

2. From the Edit menu, select Reconcile Inbound or right-click the header of the operator and select Reconcile Inbound.

   The Inbound Reconcile Operator dialog displays as shown inFigure 6-22.

Figure 6-22 Inbound Reconcile Operator Dialog

Text description of the illustration reconcib.gif

3. From Reconcile with, select the type of repository object to use to update the operator. The types of objects available depends on the operator type. See Table 6-10for a list of available objects.

4. Set the Match Strategies.

   Matching by Object Identifier: Use this strategy if you want to keep your operators consistent with changes to the previously bound repository object. Match by object identifier is not available if you want to reconcile to a different repository object.

   Matching by Bound Name: Use this strategy if you want to maintain equivalence of operator bound names and object physical names. You can use this strategy with a different repository object if there are changes that alter the structure of the operator.

   Matching by Position: Use this strategy for reconciliation with a different repository object if you want to preserve the business names of your operator attributes. This strategy is most effective when the only changes to the repository object are the addition of columns, fields, or parameters at the end of the object.

5. Click OK.

Reconciliation also updates additional logical properties of an operator. For example, for a Mapping Flat File operator, information about the character set and filename are refreshed.

Reconciling Operators based on Repository Objects

Table 6-10 describes the repository objects that you can use to update an operator by performing an inbound reconciliation.

Table 6-10 Operators Reconciled with Repository Objects  

Operator Object	Repository Objects Available for Reconciliation
Mapping Table	Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes
Mapping External Table	Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes
Mapping View	Tables, External Tables, Views, Materialized Views, Files, Dimensions and Cubes
Mapping Materialized View	Tables, External Tables, Views, Materialized Views, Files, Dimensions and Cubes
Mapping Sequence	Sequences only
Mapping Flat File	Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes
Mapping Dimension	Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes
Mapping Cube	Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes
Key Lookup Operator	Tables only
Pre Mapping Process Operator	Transformations only
Post Mapping Process Operator	Transformations only
Mapping Transformation Operator	Transformations only

Outbound Reconciliation

Outbound reconciliation updates a selected repository object to reflect changes in the operator. You can perform outbound reconciliation on tables, views, materialized views, transformations, and flat file operators. You can use outbound reconciliation for any of the following reasons:

• Create new repository objects: Use outbound reconciliation to create a new repository object in a target module. The new repository object must be of the same type. For example, you can use outbound reconciliation to create a new table based on an existing table, but you can not create a new view based on an existing table.

• Propagate changes: Use outbound reconciliation to propagate changes you made in an operator to its associated repository object. When you rename the logical name for an operator or attribute, Warehouse Builder propagates the first 30 characters of the logical name as the bound name.

• Replace repository objects: Use outbound reconciliation to replace an existing repository object.

Outbound reconciliation has no impact on the dependent relationship between other operators in the mapping to repository objects. Table 6-11 lists the operators eligible for Outbound Reconcile:

Table 6-11 Outbound Reconcile Operators  

Mapping Objects	Create Repository Objects	Propagate Changes	Replace Repository Objects	Notes
External Tables	Yes	Yes	Yes	Updates the repository external table only and not the flat file associated with the external table. See "Reconciling an External Table Definition with a Record in a File"
Flat Files	Yes	Yes	No	Creates a new, delimited (comma-separated) flat file for single record type flat files only. Cannot replace an existing file.
Mapping Input Parameters	Yes	Yes	Yes	Copies input attributes and data types as input parameters.
Mapping Output Parameters	Yes	Yes	Yes	Copies output attribute and data types as return specification for the function.
Materialized Views	Yes	Yes	Yes	Copies attributes and data types as columns.
Tables	Yes	Yes	Yes	Copies attributes and data types as columns. Constraint properties are not copied.
Transformations	Yes	Yes	Yes	Not applicable.
Views	Yes	Yes	Yes	Copies attributes and data types as columns.

To perform an outbound reconciliation on an existing operator:

1. Select an operator on the canvas.

2. From the Edit menu, select Reconcile Outbound or right-click the header of the operator and select Reconcile Outbound.

   The Outbound Reconcile dialog displays, as shown in Figure 6-23.

Figure 6-23 Outbound Reconcile Dialog (for Tables)

Text description of the illustration reconcic.gif

3. Select either Create a new <object type> or Reconcile with an existing <object type>.

   If you select Create a new <object type>, Warehouse Builder creates the object in the target module you specify. This option is useful for quickly creating staging tables for a data warehouse. For instructions how to use outbound reconciliation and the Auto-Mapping dialog to create staging tables, see "Example: Using the Mapping Editor to Create Staging Area Tables".

   If you select Reconcile with an existing <object type>, you can make selections for either updating or replacing the object.

   To update the repository object, select the associated operator from the Reconcile with list. For example, to update the S_TABLE repository object, select the S_TABLE operator. The types of changes propagated from the operator to the repository object include changes to an operator business name and physical name and changes to an attribute data type.

   To replace the repository object, select the another operator from the Reconcile with list. For example, to replace the S_TABLE1 repository object, select S_TABLE2 operator. You can only replace a repository object with the same type of repository object. For instance, you can use outbound reconciliation to replace TABLE_A with TABLE_B but not VIEW_B.

4. Set the Match Strategies.

5. Click OK.

Match Strategies

You can specify the following strategies for reconciling an object in a mapping:

• Match by Object Identifier

• Match by Bound Name

• Match by Position

You can specify more than one reconciliation strategy. If you select more than one strategy, then Warehouse Builder performs the matches in the following order:

1. Match by object identifier

2. Match by position

3. Match by name

Match by Object Identifier

Use this strategy if you want operators to be consistent with changes to the repository object and if you want to maintain separate business names for your operator attributes despite changes to physical names in the repository object. Match by object identifier is not available if you want to reconcile with a different type of repository object.

This strategy uses the unique object identifiers to determine the correlation between the operator attributes and those of the selected repository object. Warehouse Builder removes attributes from the operator that do not correspond to an attribute in the repository object. This can happen if an attribute was added to the operator and not reconciled, or if a column was removed from the repository object before reconciliation. When an attribute is removed, any incoming or outgoing mapping lines connected to that attribute are also removed from the canvas. Attributes of the selected repository object that cannot be matched with those of the operator are added as new attributes at the end of the operator. Mapping lines for matched attributes are preserved.

Match by Bound Name

Use this strategy if you want bound names in the operator to be consistent with physical names for the object. If a repository object column was renamed, it is interpreted as if the column were deleted and a new column inserted. The mapping lines for renamed attributes are removed. You can also use this strategy with a different repository object if there are changes in the repository object that would change the structure of the operator.

This strategy uses matching between the bound names of the operator attributes and the physical names of the repository object attributes. Matching is case-sensitive. On inbound reconciliation, attributes of the operator that cannot be matched with those of the repository object are removed. When an attribute is removed, any incoming or outgoing mapping line connected to that attribute is also removed from the canvas. Attributes of the selected repository object that cannot be matched with those of the operator are added as new attributes to the operator. Mapping lines for matched attributes are preserved. Because bound names are read-only after you have bound an operator to a repository object, it is not possible to manipulate them to achieve a different match result during inbound reconciliation.

Match by Position

This strategy matches operator attributes with columns, fields, or parameters of the selected repository object by position. The first attribute of the operator is reconciled with the first attribute of the repository object, the second with the second, and so on. If the operator has more attributes than the repository object, then the excess attributes are removed from the operator. If you remove an attribute, any incoming or outgoing mapping line connected to that attribute is also removed from the canvas. If the selected repository object has more attributes than the operator, then they are added as new attributes to the end of the operator. Mapping lines for existing attributes in the operator are preserved. Use this strategy to reconcile with a different repository object if you want to preserve the business names of your operator attributes. This strategy is most effective when the only changes to the repository object are the addition of extra columns, fields, or parameters at the end of the object.