Skip Headers

Oracle9i Warehouse Builder User's Guide
Release 9.0.2

Part Number A95931-01
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

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

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:

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:

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:

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:

Figure 6-1 Mapping Editor Toolbar and Menu

Text description of menubar.gif follows.

Text description of the illustration menubar.gif

Figure 6-2 Toolbox

Text description of objectpa.gif follows.

Text description of the illustration objectpa.gif

Figure 6-3 Mapping Editor Canvas Showing Mapping Operators

Text description of mappingo.gif follows.

Text description of the illustration mappingo.gif

Figure 6-4 Operator Property Inspector

Text description of property.gif follows.

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 Mapping

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 createma.gif follows.

Text description of the illustration createma.gif

Warehouse Builder opens the New Mapping Wizard.

  • Click Next.

    The New Mapping Wizard displays the Name page.

    Figure 6-6 New Mapping Wizard Name Page

    Text description of nnw_name.gif follows.

    Text description of the illustration nnw_name.gif

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

  • Click Next.

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

  • 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 emptymap.gif follows.

    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:

    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 addtable.gif follows.

    Text description of the illustration addtable.gif

  • Select an option:

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

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

  • 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 mappingb.gif follows.

    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:

    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.

  • Click Next.

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

  • 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:

    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:

    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 mappinge.gif follows.

    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 automapp.gif follows.

    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:

    Figure 6-12 Match by Name Matching Options

    Text description of matchopt.gif follows.

    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 displaye.gif follows.

    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:

    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 add-rema.gif follows.

    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 add-remo.gif follows.

    Text description of the illustration add-remo.gif

  • Type the new attribute name.

  • Click Add.

  • 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 renameat.gif follows.

    Text description of the illustration renameat.gif

  • Type the new name of the operator.

  • Click OK.


  • Go to previous page Go to next page
    Oracle
    Copyright © 1996, 2001 Oracle Corporation.

    All Rights Reserved.
    Go To Table Of Contents
    Contents
    Go To Index
    Index