5 Defining ETL Logic

After you create and import data object definitions in Oracle Warehouse Builder, you can design 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:

About Mappings and Operators

Mappings describe a series of operations that extract data from sources, transform it, and load it into targets. Mappings 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.

The basic design element for a mapping is the operator. Use operators to represent sources and targets in the data flow. Also use operators to define how to transform the data from source to target. The operators you select as sources have an impact on how you design the mapping. Based on the operators you select, Warehouse Builder assigns the mapping to one of the following Mapping Generation Languages:

  • PL/SQL

  • SQL*Loader

  • ABAP

Each of these code languages require you to adhere to certain rules when designing a mapping.

This guide illustrates how to define a PL/SQL mapping. To define the other types of mappings, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide. A basic rule for defining a PL/SQL mapping is that PL/SQL mappings can contain any type of source operator other a Flat File operator or a SAP/R3 source.

Summary of Steps for Defining Mappings

To define a mapping, refer to the following sections:

  1. Creating a Mapping

  2. Adding Operators

  3. Editing Operators

  4. Connecting Operators, Groups, and Attributes

  5. Setting Operator, Group, and Attribute Properties

  6. Configuring Mappings Reference in the Warehouse Builder Online Help

  7. When you are satisfied with the mapping design, generate the code by selecting the Generate icon in the toolbar.

Subsequent Steps

After you design a mapping and generate its code, you can next create a process flow or proceed directly with deployment followed by execution.

Use process flows to interrelate mappings. For example, you can design a process flow such that the completion of one mapping triggers an email notification and launches another mapping.

Deploy the mapping, and any associated process flows you created, and then execute the mapping.

Creating a Mapping

To create a mapping:

  1. Go to the Mappings node in the Projects Navigator. This node is located under a warehouse target module, under the Databases folder, under the Oracle folder.

    Figure 5-1 Mappings Node on the Projects Navigator

    This illustration is described in the surrounding text.
    Description of "Figure 5-1 Mappings Node on the Projects Navigator"

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

    Warehouse Builder opens the Create Mapping dialog box.

  3. Enter a name and an optional description for the new mapping.

    Select Help to review the rules on naming and describing mappings.

  4. Click OK.

    Warehouse Builder stores the definition for the mapping and inserts its name in the Projects Navigator. Warehouse Builder opens a mapping editor for the mapping and displays the name of the mapping in the title bar.

To open a previously created mapping:

  1. From the Projects Navigator, locate a warehouse target module under the Databases folder and then under the Oracle Database folder.

  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 File menu, select Open.

    • Select a mapping and press Ctrl + O.

    • Right-click a mapping and select Open.

    Warehouse Builder displays the Mapping Editor.

Types of Operators

As you design a mapping, you select operators from the Mapping Editor palette and drag them onto the canvas.

  • Oracle source and target operators: Use these operators to represent Oracle Database objects such as Oracle tables, views, materialized views.

  • Remote and non-Oracle source and target Operators: The use of these operator have special requirements.

  • Data flow operators: Use data flow operators to transform data.

  • Pre/Post Processing operators: Use these operators to perform processing before or after executing a mapping. The Mapping parameter operator is used to provide values to and from a mapping.

  • Pluggable mapping operators: A pluggable mapping is a reusable grouping of mapping operators that acts as a single operator.

Adding Operators

The steps you take to add an operator to a mapping depend on the type of operator you select. This is because some operators are bound to workspace objects while others are not. As a general rule, when you add a data source or target operator, Warehouse Builder creates and maintains a version of that object in the Warehouse Builder workspace and a separate version for the Mapping Editor. For example, when you add a table operator to a mapping, Warehouse Builder maintains a separate copy of the table in the workspace. The separate versions are said to be bound together. That is, the version in the mapping is bound to the version in the workspace.

To distinguish between the two versions, this section refers to objects in the workspace either generically as workspace objects or specifically as workspace tables, workspace views, and so on. And this section refers to operators in the mapping as table operators, view operators, and so on. Therefore, when you add a dimension to a mapping, refer to the dimension in the mapping as the dimension operator and refer to the dimension in the workspace as the workspace dimension.

Warehouse Builder maintains separate workspace objects for some operators so that you can synchronize changing definitions of these objects. For example, when you re-import a new metadata definition for the workspace table, you may want to propagate those changes to the table operator in the mapping. Conversely, as you make changes to a table operator in a mapping, you may want to propagate those changes back to its associated workspace table. You can accomplish these tasks by a process known as synchronizing. In Warehouse Builder, you can synchronize automatically. Alternatively, synchronize manually from within the Mapping Editor.

To add an operator to a mapping:

  1. Open the Mapping Editor.

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

    If you select an operator that you can bind to a workspace object, the Mapping Editor displays the Add Mapping operator_name dialog box. For information about how to use this dialog box, click Help.

    If you select an operator that you cannot bind to a workspace object, Warehouse Builder may display a wizard or dialog box to assist you in creating the operator.

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

    The Mapping Editor displays the operator maximized on the canvas. The operator name appears in the upper left corner. You can view each attribute name and data type.

    If you want to minimize the operator, click the arrow in the upper right corner and the Mapping Editor displays the operator as an icon on the canvas.

    Figure 5-2 Mapping Editor Showing a Table Operator Source

    This illustration is described in the surrounding text.
    Description of "Figure 5-2 Mapping Editor Showing a Table Operator Source"

Adding Operators that Bind to Workspace Objects

When you add an operator that you can bind to a workspace object, the Mapping Editor displays the Add Mapping operator_name dialog box. Select one of the following options:

Create Unbound Operator with No Attributes

Use this option when you want to use the Mapping Editor to define a new workspace object such as a new staging area table or a new target table.

After you select Create Unbound Operator with No Attributes, type a name for the new object. Warehouse Builder displays the operator on the canvas without any attributes.

You can now add and define attributes for the operator as described in "Editing Operators". Next, to create the new workspace object in a target module, right-click the operator and select Create and Bind.

For an example about how to use this option in a mapping design, see "Example: Using the Mapping Editor to Create Staging Area Tables".

Select from Existing Workspace Object and Bind

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

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 workspace objects within the same module as the mapping or from other modules. If you select a workspace 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 workspace object.

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.

Editing operators is different from assigning loading properties and conditional behaviors. To specify loading properties and conditional behaviors, use the properties windows as described in "Setting Operator, Group, and Attribute Properties".

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 Open Details.

    The Mapping Editor displays the operator editor with the Name Tab, Groups Tab, and Input and Output Tabs for each type of group in the operator.

    Some operators include additional tabs. For example, the Match Merge operator includes tabs for defining Match rules and Merge rules.

  3. Follow the prompts on each tab and click OK when you are finished.

    Select Help if you need additional information for completing a tab.

Connecting Operators, Groups, and Attributes

After you select mapping source operators, operators that transform data, 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 Operators: Define criteria for connecting groups between two operators.

  • Connecting Groups: Define criteria for connecting all the attributes between two groups.

  • Connecting Attributes: Connect individual operator attributes to each other one at a time.

  • Using an Operator Wizard: For operators such as the Pivot operator and Name-Address operator, you can use the wizard to define data flow connections.

Connecting Operators

You can connect one operator to another if there are no existing connection between the operators. Both of the operators that you want to connect must be displayed in their icon form. You can also connect from a group to an operator. Hold down the left-mouse button on the group, drag and then drop on the title of the operator.

To connect one operator to another:

  1. Select the operator from which you want to establish a connection.

  2. Click and hold down the left mouse button while the pointer is positioned over the operator icon.

  3. Drag the mouse away from the operator and toward the operator icon to which you want to establish a connection.

  4. Release the mouse button over the target operator.

    The Mapping Connection dialog box is displayed.

  5. In the Attribute Group to Connect section, select values for the following:

    Source Group: Select the group, from the source operator, which must be connected to the target operator.

    Target Group: Select the group, from the target operator, to which the source group must be mapped.

  6. In the Connection Options section, select the method to be used to connect the source attributes to the target attributes and click Preview.

  7. Click OK to close the Mapping Connection Dialog box.

Connecting Groups

When you connect groups, the Mapping Editor assists you by either automatically copying the attributes or displaying the Mapping Connection Dialog box.

To connect one group to another:

  1. Select the group from which you want to establish a connection.

  2. Click and hold down the left mouse button while the pointer is positioned over the group.

  3. Drag the mouse away from the group and towards the group to which you want to establish a connection.

  4. Release the mouse button over the target group.

    If you connect from an operator group to a target group containing attributes, the Mapping Connection Dialog Box is displayed.

  5. In the Connection Options section, select the method used to connect the source attributes to the target attributes and click Preview.

  6. Click OK to close the Mapping Connection Dialog box.

If you connect from one operator group to a target group with no existing attributes, the Mapping Editor automatically copies the attributes and connects the attributes. 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 a source table.

    From the menu bar, select Mapping, select Add, then select Data Sources/Targets. In the Data Sources/Targets menu, select Table Operator.

  2. Use the Add Table Operator dialog box to select and bind the source table operator in the mapping. From the Add Table Operator dialog box, select Create unbound operator with no attributes.

    Figure 5-3 Unbound Staging Table without Attributes and Source Table

    This illustration is described in the surrounding text.
    Description of "Figure 5-3 Unbound Staging Table without Attributes and Source Table"

  3. With the mouse button positioned over the group in the source operator, 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 connects the two operators.

  5. In the Mapping Editor, select the unbound table you added to the mapping. Right-click and select Create and Bind.

    Figure 5-4 Create and Bind Dialog Box

    This illustration is described in the surrounding text.
    Description of "Figure 5-4 Create and Bind Dialog Box"

  6. In Create in, specify the target module in which to create the table.

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

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.

  3. Release the mouse over the input attribute.

  4. Repeat steps 1 through 3 until you create all the required data flow connections.

    Figure 5-5 Connected Operators in a Mapping

    This illustration is described in the surrounding text.
    Description of "Figure 5-5 Connected Operators in a Mapping"

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 an input only attribute nor can you connect into an output only attribute.

  • You cannot connect operators in such a way as to contradict an established cardinality. Instead, use a Joiner operator.

Setting Operator, Group, and Attribute Properties

When you select an object on the canvas, the editor displays its associated properties in the property inspector along the left side.

Figure 5-6 Property Inspector for a Table Operator

This illustration is described in the surrounding text.
Description of "Figure 5-6 Property Inspector for a Table Operator"

You can view and set the following types of properties:

  • Operator properties: Properties that affect the entire operator. 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.

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

Synchronizing Operators and Workspace Objects

Many of the operators you use in a mapping have corresponding definitions in the Warehouse Builder workspace. This is true of source and target operators such as table and view operators. This is also true of other operators such as sequence and transformation operators whose definitions you may want to use across multiple mappings. As you make changes to these operators, you may want to propagate those changes back to the workspace object.

You have the following choices in deciding the direction in which you propagate changes:

Synchronizing from a Workspace Object to an Operator: After you begin using mappings in a production environment, there may be changes to the sources or targets that impact your ETL designs. Typically, the best way to manage these changes is through the Warehouse Builder Dependency Manager described in the Warehouse Builder Online Help. Use the Dependency Manager to automatically evaluate the impact of changes and to synchronize all effected mappings at one time. Alternatively, in the Mapping Editor, you can manually synchronize objects as described in "Synchronizing from a Workspace Object to an Operator".

Synchronizing from an Operator to a Workspace Object: When you make changes to an operator in a mapping, you may want to propagate those changes to its corresponding workspace definition. For example, the sources you imported and used in a mapping may have complex physical names for its attributes.

Note that synchronizing is different from refreshing. The refresh command ensures that you are up-to-date with changes made by other users in a multiuser environment. Synchronizing matches operators with their corresponding workspace objects.

Synchronizing an Operator

To synchronize, select a single operator and synchronize it with the definition of a specified workspace object.

To synchronize an operator:

  1. Select an operator on the Mapping Editor canvas.

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

    Figure 5-7 Synchronizing an Operator

    This illustration is described in the surrounding text.
    Description of "Figure 5-7 Synchronizing an Operator"

  3. By default, Warehouse Builder selects the option for you to synchronize your selected operator with its associated object in the workspace. You can accept the default or select another workspace object from the list box.

    In this step you also specify either Synchronizing from a Workspace Object to an Operator or select the option for Synchronizing from an Operator to a Workspace Object.

  4. As an optional step, click Advanced to set the Matching Strategies.

    Select Help for instruction on how to use the Matching Strategies.

  5. Click OK.

Synchronizing from a Workspace Object to an Operator

In the Mapping Editor, you can synchronize from a workspace object for any of the following reasons:

  • Manually propagate changes: Propagate changes you made in a workspace object to its associated operator. Changes to the workspace object can include structural changes, attribute name changes, attribute data type changes. To automatically propagate changes in a workspace object across multiple mappings, see in the Warehouse Builder Online Help.

  • Synchronize an operator with a new workspace object: You can associate an operator with a new workspace object if, for example, you migrate mappings from one version of a data warehouse to a later version and maintain different object definitions for each version.

    Figure 5-8 Synchronizing from a Different Workspace Object

    This illustration is described in the surrounding text.
    Description of "Figure 5-8 Synchronizing from a Different Workspace Object"

  • Prototype mappings using tables: When working in the design environment, you could choose to design the ETL logic using tables. However, for production, you may want to the mappings to source other workspace object types such as views, materialized views, or cubes.

Synchronizing Operators Based on Workspace Objects

Table 5-1 lists operators and the types of workspace objects from which you can synchronize.

Table 5-1 Operators Synchronized with Workspace Objects

To: Operator From: Workspace Object Type

Cube

Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes

Dimension

Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes

External Table

Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes

Flat File

Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes

Key Lookup

Tables only

Materialized View

Tables, External Tables, Views, Materialized Views, Files, Dimensions and Cubes

Post Mapping Process

Transformations only

Pre Mapping Process

Transformations only

Sequence

Sequences only

Table

Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes

Transformation

Transformations only

View

Tables, External Tables, Views, Materialized Views, Files, Dimensions and Cubes

Note that when you synchronize from an external table operator, Warehouse Builder updates the operator based on the workspace external table only and not its associated flat file.

Synchronizing from an Operator to a Workspace Object

As you make changes to operators in a mapping, you may want to propagate those changes back to a workspace object. By synchronizing, you can propagate changes from the following operators: tables, views, materialized views, transformations, and flat file operators.

Synchronize from the operator to a workspace object for any of the following reasons:

  • Propagate changes: Propagate changes you made in an operator to its associated workspace object. When you rename the business name for an operator or attribute, Warehouse Builder propagates the first 30 characters of the business name as the bound name.

  • Replace workspace objects: Synchronize to replace an existing workspace object.

Synchronizing from an operator has no impact on the dependent relationship between other operators and the workspace objects.