6
Defining Source to Target Mappings

The following three chapters describe how to map data sources to targets. This chapter describes how to create and update mapping definitions. It also describes the Warehouse Builder Mapping Editor. The next chapter describes how to add operators and transformations to a mapping. The following chapter describes how to configure the logical and physical properties of a mapping, how to reconcile mapping operators with repository objects, and how to validate and generate the PL/SQL code used for deployment.

This chapter includes the following topics:

• Understanding Warehouse Builder Mappings

• Defining Mappings

• Editing Mapping Operator Attributes

Understanding Warehouse Builder Mappings

A mapping describes a series of operations that pulls data from sources, transforms it, and loads it into targets. When you create a mapping, you use operators to define the Extraction, Transformation, and Loading (ETL) operations that move data from a source object to a data warehouse target object. Mappings provide a visual representation of the flow of the data from sources to targets and the operations performed on the data. Define mappings using the Mapping Editor, Property Inspectors, Expression Builder, and Code Editor.

About Mapping Operators

All mappings are based on mapping operators. A mapping operator is a logical representation of a physical repository object. A mapping operator contains row sets. A row set is any set of zero or more rows of structured data brought into or emerging from a mapping operator. A row is the basic unit of data in a mapping. The number of rows in a row set is the cardinality of that row set.

A mapping operator defines how input row sets are manipulated to produce output row sets. A mapping operator can alter the cardinality of row sets. Warehouse Builder contains several mapping operators, each with its own purpose for processing row sets.

You define and edit mapping operators using the Mapping Editor and Property Sheets. The Mapping Editor contains a toolbox that visually represents the operators.

The operator types available within the Mapping Editor include:

• Extract Operators (source): Mapping Table, Mapping View, Mapping Materialized View, Mapping Sequence, Mapping Fact, Mapping Dimension, and Mapping Flat File.

• Load Operators (target): Mapping Table, Mapping Materialized View, Mapping Dimension, and Mapping Fact.

• Standard Operators: Aggregator, Pre- and Post-Mapping Processes, Filter, Joiner, Splitter, Sorter, Deduplicator, Set Operation.

• Transformations: Mapping Transformation, Expressions, Constants.

• External Process: Pure*Integrate, Pure*Extract, custom processes.

About Operator Properties

To specify the purpose of an operator, edit the property inspector for the operator. You can set properties at the following levels:

• Operator: Properties that affect the operator and what it does.

• Group: Properties that affect attributes within the group.

• Attribute: Properties that affect only operator or group attributes or their value.

About Attributes and Attribute Groups

Attributes are inputs and outputs for operators. Attributes belong to attribute groups. The group type determines the attribute type. An attribute group type can be an:

• Input

• Output

• Input/Output

Attribute and attribute group names are logical. Although the attribute names of the target object are often the same as the logical attribute names of the operator, their properties remain independent of the attributes of the operator to which they are connected. This protects any expression or use of an attribute from corruption if it is manipulated within the operator. You can rename attribute groups and attributes independent of their sources. The cardinality of attribute groups must match in order to be used in the same input group.

About Display Sets

A display set is a graphical representation of a group of operator attributes. By default, the All display set displays for all attribute groups. If an attribute group contains more than one display set, then you can select a different display set from the list on the View menu. You can define display sets for an attribute group in the mapping editor, or they can be inherited from the attribute sets of a warehouse object that are defined during the source and target definition phase of data warehouse design. See "Creating Attribute Sets" for information on attribute sets.

You can see only one display set at a time for an attribute group. By default, the Mapping canvas shows the display sets with all the attributes in the respective attribute groups. You can edit display sets and define new display sets for an operator in the Mapping Editor. See "Editing Operator Attributes" for more information.

Table 6-1 describes the default attribute sets for Display Sets.

Table 6-1 The Default Attribute Sets  

Attribute Set	Description
All	This is the default attribute set and includes all attributes.
Hierarchies	For each hierarchy in a Dimension, a display set containing all the level attributes in that hierarchy.

About Binding Mapping Operators

Mapping operators are independent from the repository objects they represent. When you bind a mapping operator to a repository object, you create a link between them. This link causes the operator to inherit the attributes and attribute sets of the repository object.

The Mapping Editor generates a default logical name for operators, which you can change at any time. Editing of bound operators is limited to changing the logical name and adding new attributes to the operator. Editing has no effect on the attributes of the physical repository object.

The bound name displays in the Bound Name property and in the tool tip for the mapping operator. The logical names used in the mapping for mapping operators and attributes can be different from the bound names. If you set your project preferences to logical name mode without propagation to physical names or if you define your mapping operator and attribute names in the Mapping Editor, then the logical names will be different. For more information, see "Using Inbound Reconciliation".

If you choose to leave an operator unbound, it generates code that does not affect any repository object. You must bind the mapping operator to a repository object in order for the code to affect the repository object. After you bind a mapping object to a repository object, it cannot be unbound.

Defining Mappings

The first part of this section describes the Warehouse Builder Mapping Editor. The remainder of this section describes how to define a mapping.

About the Mapping Editor

The Mapping Editor is the interface for defining what you want to transform and map. The mapping editor uses an operator for each operation you want to perform. A series of operations defines a mapping.

The Mapping Editor includes:

• Menu Bar: Provides access to the mapping editor commands.

• Toolbar: Provides access to commonly used commands.

Figure 6-1 Mapping Editor Toolbar and Menu

Text description of the illustration menubar.gif

• Toolbox: Contains mapping operator icons. To create a mapping, drag a mapping operator icon to the Mapping Editor canvas.

Figure 6-2 Toolbox

Text description of the illustration objectpa.gif

• Mapping Editor Canvas: Provides the white space where you can add and remove operators, link them, and edit their properties.

Figure 6-3 Mapping Editor Canvas Showing Mapping Operators

Text description of the illustration mappingo.gif

• Operator Property Inspector: Enables you to edit the properties of a mapping operator, attribute, or attribute group.

  When you open the property inspector, you can select another operator, attribute, or attribute group and the property inspector displays the property for the selected object. Only one property inspector can be open at a time.

Figure 6-4 Operator Property Inspector

Text description of the illustration property.gif

If no operator, attribute group, or attribute is selected while a property inspector is open, the property inspector shows an empty list.

Creating a Mmapping

When you define a mapping object, you create a container that holds the mapping operators defining the ETL process. Use the Mapping Editor to define a mapping by:

1. Creating a mapping object in your warehouse module using the New Mapping Wizard.

2. Selecting data sources, data targets, operators, and transformations.

3. Linking the operator attributes.

4. Configuring operator attributes and properties. See "Configuring a Mapping" beginning for more information.

5. Validating the generated code for the mapping. See "Validating and Generating a Mapping" for more information.

To create a mapping object:

1. From the Warehouse Module Editor, right-click Mappings and then select Create Mapping from the pop-up menu.

Figure 6-5 Warehouse Module Editor

Text description of the illustration createma.gif

Warehouse Builder opens the New Mapping Wizard.

2. Click Next.

   The New Mapping Wizard displays the Name page.

Figure 6-6 New Mapping Wizard Name Page

Text description of the illustration nnw_name.gif

3. Enter a name and description for the new mapping. See "Object Names" for information on naming.

4. Click Next.

   The New Mapping Wizard displays the Finish page. Verify the name of your new mapping.

5. Click Finish.

Warehouse Builder stores the definition for the mapping and inserts its name in the warehouse module navigation tree. The Mapping Editor opens.

Figure 6-7 Mapping Editor

Text description of the illustration emptymap.gif

Displaying the Mapping Editor

Use the Mapping Editor to define ETL operations. Open the Mapping Editor from the Warehouse Module Editor.

To display the Mapping Editor, do one of the following:

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

• Double-click a mapping.

• Right-click a mapping, and select Editor... from the pop-up menu.

Selecting Data Operators

You can use a data operator as a data source or data target. Data operators include Mapping Tables, Mapping Dimensions, Mapping Facts, Mapping Views, and Mapping Materialized Views. Follow the same steps when you select any of these operators.

You can connect any data operator to any other mapping operator. A data operator generates a PL/SQL mapping.

To select a table operator:

1. Open the Mapping Editor.

2. Drag a Mapping Table operator from the Toolbox and drop it onto the Mapping Editor canvas.

   The Add Mapping Table dialog displays.

Figure 6-8 Add Mapping Table Dialog

Text description of the illustration addtable.gif

3. Select an option:
   • Create unbound object with no attributes: Enter a name for the new mapping object.

   • Create new repository object and bind: Select the warehouse module where you want the object to reside.

   • Import object into repository and bind: Select the module from which you are importing the object.

     If you make this selection and click OK and have already defined a database link for the module from which you are importing the object, then Warehouse Builder returns the Database Link Information dialog. Enter the appropriate information in the dialog. If you have not defined a database link, then Warehouse Builder displays the New Database Link Information dialog. See "Configuring Connection Information for Database Sources" for information on how to define a new database link.

   • Select from existing repository object and bind: Either type the prefix to search for the object or select from the displayed list of objects within the selected module.

     Note: To select multiple items, hold down 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, hold down the Shift key, and then click the last object.

   If you are selecting a Mapping Fact, a Mapping Dimension, or a Key Lookup operator, then you have two choices:

   • Create a new repository object and bind.

   • Select from existing repository object and bind.

   You do not have the option to import an object if you are selecting a Mapping View or a Mapping Materialized View operator.

4. Click OK.

   A Mapping Table operator displays on the canvas. This operator shows the names and attributes of the table to which it is bound.

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

   

   Text description of the illustration mappingb.gif

   Selecting a Flat File Operator

   You can use a Mapping Flat File operator as a data source or data target. As a data source, the Mapping Flat File operator acts as the row set generator for reading from a flat file.

   To use a flat file as a target, a file source module must exist and it must contain valid flat files. A mapping to a flat file target generates a PL/SQL package that is spooled into a flat file instead of loading data into rows in a table. When you define a Mapping Flat File operator as a target, you are using an existing file as a template for the target flat file. Warehouse Builder comes with a file source module called TARGET_FILES for use as a template. This module contains a comma-delimited file (CSV_FILE) and a fixed-length file (FIXED_LENGTH_FILE), and each file contains ten fields of the CHAR data type.

   A mapping can contain up to 50 flat files as targets, and it can also contain a mix of flat files, relational objects, and transformations. You can connect a Mapping Flat File source operator to any other operator except another Mapping Flat File operator.

   You have the following two options for a Mapping Flat File Operator:

   • Import file into repository and bind.

   • Select from existing repository file and bind.

   Selecting an SAP Source

   The following section describes how to select an SAP operator as a source for a mapping. An SAP operator used as a source can be connected to Mapping Table, Mapping Dimension, and Mapping Fact operators. You can generate ABAP or PL/SQL code for your mappings. If you generate ABAP code, only the Filter and Joiner operators are available.

   To select an SAP source:

   1. From the toolbox, drop the Mapping Table icon onto the Mapping Editor canvas.

      The Add Mapping Table dialog displays.

   2. Choose Select from existing repository tables and bind.

      The field at the bottom of the dialog displays a list of SAP tables whose definitions were previously imported into the SAP source module.

   3. Select your source table name from this list and click OK.

      The editor places a mapping table on the mapping canvas to represent the SAP table.

   When you select an SAP operator as a source, the option to Proceed to the Copy and Map wizard after adding component is enabled. If you check this option and click OK, Warehouse Builder displays the Copy and Map Wizard.

   The Copy and Map wizard enables you to perform three functions: copy the selected source object, create a target object based on the source object definitions, and map the source object to the new target object. The Copy and Map option is only available when you select a single table as a source.

   To use the Copy and Map Wizard:

   1. In the Add Table dialog, select the SAP table you want to use as the source.

   2. Check the option to Proceed to the Copy and Map wizard after adding component.

   3. Click OK.

      The Copy and Map Wizard Welcome page displays.

   4. Click Next.

      The Target Name page displays.

   5. Enter the following information:
      • Physical Name: A unique physical name for the target object you are creating. The name can contain 1 to 30 characters, no spaces are allowed.

        If the repository already contains an object with the same name, a name conflict error message displays. You must then rename the object you are creating.

      • Object Type: You can only create tables that map from SAP source tables. The choice in this field is preselected.

      • Application: The target application that contains the target object.

      • Description: Optional field used to describe the target object you are creating. Up to 2 MB is allowed.

   6. Click Next.

      The Source Columns page displays.

   7. Select the columns you want to copy from the source table to your target table.

      The fields on this page are read-only. By default, this page displays and selects all columns defined within the source table. To deselect a column, uncheck the selection check box next to that column. Click Select All to copy all the columns within the table. Click Deselect All to deselect all the columns within the table. You must select at least one column to copy.

   8. Click Next.

      The Target Columns page displays. You use this page to customize the selected columns or add new ones to your target table. The first two columns within the table display the physical and logical names of the columns. You cannot modify the logical names, you can only modify the physical names of a column. The default physical names are copied from the source physical names.

      Edit the following fields:

      • Target Columns (Physical): Change the physical name of a target column. If you add a new column, you must name it.

      • Position: Change the position of the columns by dragging the row header up or down.

      • Data Type: Change the data type for each column. It is automatically translated to an Oracle data type.

      • Not Null: (optional) A check indicates that the column cannot contain a NULL value.

      Click Add to add a new column within your target object. The Remove option is only enabled for columns you create. If you do not want to copy a column defined in the source object, click Back to return to the Source Columns page and deselect the column.

   9. Click Generate target physical name from source physical name (default) to copy the physical target column names from the physical source column names. Click Generate target physical name from source logical name to copy the physical target column names from the logical source column names. The logical names can exceed the Oracle physical name limit of 30 characters.

      Note:: If you rename a target column, then the new name is preserved regardless of how you choose to generate the target physical name. For example, the source physical name of a table is MANDT and its logical name is MANDT_Client. If you change the default target physical name from MANDT to MANDT_Client_Name, then this name is preserved.

   If the new name is not unique, a name conflict error message displays. Click OK to rename the column automatically or click Cancel to rename it yourself.

   10. Click Next.

       The Summary and Copy page displays. Verify the information on this page.

   11. Click Finish.

       The Mapping Editor displays the source table mapped to the newly created target table. You can locate this bound target table under the TABLES node in the Warehouse Module Editor.

   Selecting a Data Flow Operator

   You can add operators that alter the source data as it flows to the data targets. Warehouse Builder includes data flow operators in the Toolbox. You can create custom operators using the Oracle Transformation Library or Expression Builder. See "Using Expression Builder" for information on creating custom operators.

   To select a data flow operator, drag a data flow operator from the Toolbox and drop it onto the Mapping Editor canvas. After you make your selections, a data flow operator displays on the canvas. Each data flow operator requires a different set of tasks when you add them to a mapping. Chapter 7, "Using Mapping Operators and Transformations" describes the flow operators and how to add them to a mapping.

   Connecting Mapping Operators

   After you have defined mapping source operators, data flow operators, and target operators, you are ready to connect them. You can connect individual operator attributes to each other, or you can connect attribute groups. When you connect groups, you can control which attributes are connected using the Auto-Mapping dialog.

   To connect mapping operators, do one of the following:

   • Drag a line from an output attribute group to an input attribute group of another operator.

   • Drag a line from an output attribute to an input attribute of another operator.

   • Drag a line from the source to the target to copy all selected attributes from the source node and add them to the selected target attribute.

   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 connections you make in the mapping. If the mouse button is released over an invalid target, no data flow connection is established.

   You cannot create the following links:

   • Link an output attribute to an output attribute.

   • Link an input attribute to an input attribute.

   • Link attributes in the same operator.

   • Link to the same input attribute twice.

   Connecting Attributes

   To connect mapping operator attributes, draw lines from output attributes or output attribute groups to input attributes or groups between the operators. These lines are data flow connections. The data flow connections graphically represent how the data flows from a source to a target.

   To connect operators:

   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.

   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.

   Connecting Attribute Groups

   If you connect source attributes to target attribute groups, Warehouse Builder opens the Auto-Mapping dialog. Auto-mapping enables you to specify a rule that Warehouse Builder uses to automatically map source attributes to target attributes. After you specify Auto-Mapping options, Warehouse Builder creates the mapping.

   Figure 6-11 Auto-Mapping Dialog

   

   Text description of the illustration automapp.gif

   The Auto-Mapping dialog displays matching criteria and a panel that displays the mappings. Choose the match criteria by selecting:

   Copy: Copies source attributes to the target group and creates mappings from the source attributes to the new target attributes.

   Note: You cannot copy source attributes to dimension or fact targets.

   Match by Position: Creates mappings 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: Creates mappings between existing attributes with matching names. 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 target prefix, Ignore target suffix: These check boxes and text fields enable 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-12 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 attribute 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 change the mappings before they are implemented.

   Figure 6-13 Displayed Mappings Field

   

   Text description of the illustration displaye.gif

   The check boxes for each attribute enable you to include or exclude attributes in a mapping. The source column lists the source attributes and the target column lists the matching target attributes. The comments column contains information about the results from your matching criteria. For example:

   • When a matching results in more than one target attribute, the following messages appear in the comments column in the Displayed Mapping field:

     Target was already mapped: A target attribute can have only one mapping from one source attribute. The check box is unchecked and appears grayed out to indicate that the mapping will not be created.

     Target may not be double-mapped: If name matching finds multiple matches to a target attribute, only one mapping can be created. The check box for the first mapping is checked, and all other mappings to that target attribute are unchecked. You can select only one check box. 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 in the comments column in the Displayed Mappings field:

     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.

   Click OK to create the mapping.

   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 "About Multi-User Access" for more information on read-write permissions.

   To complete the mapping, you may need to configure its operators. See "Configuring Mapping Table Operators" for information.

   Editing Mapping Operator Attributes

   You can edit operator properties such as attributes, attribute groups, display sets, and names.

   Adding or Removing Operator Attribute Groups

   You can create a new attribute group in the mapping editor by right-clicking the name header and selecting Add/Remove Groups.

   Figure 6-14 Add/Remove Groups Dialog

   

   Text description of the illustration add-rema.gif

   You cannot change the group direction. You add input groups to Joiners and output groups to Splitters. See "Adding Flow Operators to a Mapping" for more information.

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

   Editing Operator Attributes

   For each mapping operator, you can add, remove, and rename attributes. After you have added or changed attributes or attribute groups, you must reconcile the mapping operators with their corresponding repository objects. See "Reconciling Mapping Operators with Repository Objects" for more information.

   Adding or Removing Attributes

   You can add attributes to or remove attributes from an operator on the Mapping Editor canvas.

   To add attributes to an Operator:

   1. In the Mapping Editor, select a mapping operator.

   2. Do one of the following:
      • From the Edit menu, select Add/Remove Attribute.

      • Right-click the operator display set and select Add/Remove Attribute from the pop-up menu.

      • Right-click an attribute group and select Add/Remove Attribute from the pop-up menu.

      The Add/Remove Attributes dialog displays.

   Figure 6-15 Add/Remove Attributes Dialog

   

   Text description of the illustration add-remo.gif

   3. Type the new attribute name.

   4. Click Add.

   5. Click OK.

   To remove attributes from an operator:

   1. Select an attribute group in a mapping operator.

   2. Do one of the following:
      • Select Edit and then Add/Remove Attribute.

      • Right-click the operator display set and then select Add/Remove Attribute from the pop-up menu.

      • Right-click the attribute group of the operator and select Add/Remove Attribute from the pop-up menu.

      The Add/Remove Attribute dialog displays.

   3. Select the attribute you want to remove in the Add/Remove Attributes dialog.

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

   4. Click Delete.

   Renaming Attributes

   You can rename mapping operator attributes.

   To rename an operator, attribute, or attribute group:

   1. Select the appropriate item on the Mapping Editor canvas.

   2. From the Edit menu, select Rename or right-click an attribute and select Rename from the pop-up menu.

      The Rename dialog displays.

   Figure 6-16 Rename Attribute Dialog

   

   Text description of the illustration renameat.gif

   3. Type the new name of the operator.

   4. Click OK.