Skip Headers
Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)

Part Number B31278-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

7 Creating Mappings

After you create and import data object definitions in 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 that describe how to create, edit, and use mappings:

About Mappings and Operators

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 as described in the Oracle Warehouse Builder API and Scripting Reference.

Based on the ETL logic that you define in a mapping, Warehouse Builder generates the code required to implement your design. Warehouse Builder can generate code for the following languages:

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:

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

Instructions for Defining Mappings

Before You Begin

First verify that your project contains a warehouse target module with a defined location.

Also import any existing data you intend to use as sources or targets in the mapping.

To define a mapping, refer to the following sections:

  1. Creating a Mapping

  2. Adding Operators

    To design a mapping to extract from or load to a flat file, refer to "Instructions for Using Flat File Sources or Targets in a Mapping".

  3. Editing Operators

  4. Connecting Operators

  5. Using Pluggable Mappings

  6. Setting Mapping Properties

  7. Setting Operator, Group, and Attribute Properties

  8. Configuring Mappings Reference in the Warehouse Builder Online Help

  9. For PL/SQL mappings, you can also refer to "Best Practices for Designing PL/SQL Mappings".

  10. Debugging a Mapping

  11. 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 starts another mapping. For more information, see "Designing Process Flows".

Deploy the mapping, and any associated process flows you created, and then execute the mapping as described in "Deploying to Target Schemas and Executing ETL Logic".

Instructions for Using Flat File Sources or Targets in a Mapping

In a mapping you can use flat file operators as either sources or targets but not a mix of both. You can import file definitions from existing flat files and use that data as a source or target in the mapping. Or you can create your own flat file definition in the Mapping Editor to load data into a new flat file target.

Creating a New Flat File Target

To create a new flat file definition for a target, complete the following steps:

  1. If you have not already done so, create a flat file module.

    A flat file module is necessary to enable you to create the physical flat file later in these instructions.

  2. Create the mapping definition as described in "Creating a Mapping".

  3. Drag and drop a flat file operator onto the canvas.

  4. On the Add Flat File Operator dialog box, select the option Create Unbound Operator with No Attributes and assign a name to the new target operator.

  5. Edit the new operator as described in "Editing Operators".

    Thus far, you have defined an operator that represents a flat file but have not created the actual flat file target.

  6. To create the flat file in the database, right-click the operator and select Create and Bind.

    The dialog box prompts you to select a flat file module and enables you to assign a unique name to the flat file. When you click OK, Warehouse Builder displays the new target in the Project Explorer Files node under the module you specified.

  7. Continue to define your mapping as described in "Instructions for Defining Mappings".

Creating a Source or Target Based on an Existing Flat File

To use an existing flat file as a source or target, complete the following steps:

  1. In the Project Explorer, right-click the File node and select New to create a module for the flat files as described in "Creating Flat File Modules" in the Warehouse Builder Online Help.

  2. Right-click the flat file module and select Import to import file definitions as described in "Importing Definitions from Flat Files".

  3. Decide to use the file as either a source or a target.

    If you import a file for use as a target, Warehouse Builder generates PL/SQL code for the mapping. Review the details in "Flat File Target Operators" in the Warehouse Builder Online Help and then skip to step 7.

    If you import a file for use as a source, you must decide whether to maintain the flat structure of the file using SQL* Loader or to represent the data in PL/SQL format through an external table. Continue to the next step.

  4. Refer to "External Table Operators versus Flat File Operators" in the Warehouse Builder Online Help to determine what type of operator to use in your mapping.

    If you select external table operator, continue to the next step.

    If you select flat file operator, skip to step 7.

  5. Create the external table as described in "Creating a New External Table Definition" in the Warehouse Builder Online Help.

  6. In the Project Explorer, right-click the external table and select Configure. On the Data Files node, right-click and select Create.

    Enter the name of the flat file from which the external table inherits data. Enter the file name and the file extension such as myflatfile.dat.

  7. Drag and drop the flat file operator or external table operator onto the canvas.

  8. On the Add Operator dialog box, select the option Select from Existing Repository Object and Bind.

    You can now continue designing your mapping.

Creating a Mapping

To create a mapping:

  1. Navigate to the Mappings node in the Project Explorer. This node is located under a warehouse target module, under the Databases folder, under the Oracle folder.

  2. Right-click Mappings and then select New.

    Warehouse Builder opens the Create Mapping dialog box.

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

    For rules on naming and describing mappings, see "Mapping Naming Conventions".

  4. Click OK.

    Warehouse Builder stores the definition for the mapping and inserts its name in the Project Explorer. 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 Project Explorer, locate a warehouse target module under the Databases folder and then under the Oracle 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 Edit menu, select Open Editor.

    • Select a mapping and press Ctrl + O.

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

    Warehouse Builder displays the Mapping Editor.

About the Mapping Editor

The first time you open the Mapping Editor, it displays with a menu bar, multiple toolbars, multiple windows along the left side, and a canvas on the right.

Figure 7-1 displays the Mapping Editor canvas.

Figure 7-1 Mapping Editor Canvas

This illustration is described in the surrounding text.
Description of "Figure 7-1 Mapping Editor Canvas"

Standard Editor Components

The Mapping Editor has the following standard components common to most editors in Warehouse Builder:

  • Title Bar: At the top of the editor, the title bar displays the name of the mapping and the access privileges you have on the mapping.

  • Menu Bar: Below the title bar, the menu bar provides access to the editor commands. You can access the menu bar by clicking on one of its options or by using hot keys. For example, to access the Mapping menu, press Alt +M.

  • Toolbar: Below the menu bar, the toolbar provides icons for commonly used commands.

  • Canvas: The canvas provides the work space where you design and modify mappings.

  • Indicator Bar: Along the lower edge of the editor you can see mode icons, indicators, and descriptions.

    Figure 7-2 displays the Indicator Bar of the mapping Editor.

Figure 7-2 Indicator Bar on the Mapping Editor

This illustration is described in the surrounding text.
Description of "Figure 7-2 Indicator Bar on the Mapping Editor"

In the left corner are Naming Mode, Rename Mode, Read/Write, and Validation Mode.

In the right corner are the percent zoom indicator and the navigation mode. In the preceding figure, the zoom level is at 100% and the navigation mode is set to Select Mode.

Mapping Editor Windows

You can resize a window by placing your mouse on the border of the window, pressing the mouse button when the double sided arrow appears, and dragging your mouse to indicate the desired size.

You can move a window by placing the mouse on the Title Bar, and dragging the mouse to the desired location.

To show or hide windows, select Window from the menu bar and either activate or deactivate the check mark corresponding to the window.

Explorer

When you first start the editor, Warehouse Builder displays the explorer in the upper left corner. The explorer provides a tree listing of all the activities on the canvas and their parameters. When you select an activity on the canvas, Warehouse Builder navigates to the activity on the explorer.

Properties Inspector

When you first start the editor, Warehouse Builder displays the properties inspector in the lower left corner. The properties inspector displays the properties for the mapping, its operators, and attributes in the operators. Select an object either from the canvas or the explorer and Warehouse Builder displays the properties in the properties inspector.

Palette

When you first start an editor, Warehouse Builder displays the palette along the left side and it contains operators that you can drag and drop onto the canvas.You can relocate the palette anywhere on the editor. You can choose to hide or display the palette by clicking on Operator Palette listed under View in the menu bar.

Bird's Eye View

The Bird's Eye View enables you to move the view of the canvas with a single mouse dragging operation. You can thus reposition your view of the canvas without using the scroll bars.

The Bird's Eye View displays a miniature version of the entire canvas. It contains a blue colored box that represents the portion of the canvas that is currently in focus. In the case of mappings that span more than the canvas size, you can click the blue box and drag it to the portion of the canvas that you want to focus on.

Data Viewer

The Data Viewer enables you to view the data stored in the data object. See "Data Viewer" for more information about the Data Viewer.

Generation

The Generation panel displays the generation and validation results for a data object. This panel is hidden when you first open the editor window. It is displayed the first time you generate or validate a data object. You can to show or hide the Generation panel by selecting Window and then Generation Results from the editor menu.

The Generation window contains two tabs: Script and Message. The Script tab displays the generated scripts to implement the data object selected in the canvas. The Message tab displays the validation messages for the selected data object. Double-click a message to view the complete message text.

Mapping Editor Toolbars

The Mapping Editor provides the following task oriented toolbars: general, graphic, generation, and palette. With the exception of the palette, the editor by default displays the toolbars below the menu bar. You can move, resize, or hide each of the toolbars.

  • General Toolbar: Use this toolbar to call common operations such as save all, exporting diagram, validating, generating, and printing.

  • Diagram Toolbar: Use this toolbar to navigate the canvas and change the magnification of objects on the canvas.

  • Debug Toolbar: Use this toolbar to call commands for debugging the mapping.

  • Palette Toolbar: The palette contains operator icons. To include an operator, drag an operator icon onto the Mapping Editor canvas. As Warehouse Builder includes over 50 operators, you may want to sort and display the operators based on type.

Mapping Editor Display Options

You can control how the editor displays the mappings on the canvas by selecting View from the menu bar and selecting Options. Warehouse Builder displays the Options dialog box that enables you to set display options for the Mapping Editor canvas.

The Options dialog box contains the following options. You can either select or deselect any of these options.

  • Input Connector: Select this option to display an arrow icon on the left of attributes that you can use as input attributes.

  • Key Indicator: Select this option to display a key icon to the left of the attribute that is a foreign key attribute in an operator.

  • Data Type: Select this option to display the data type of attributes in all operators.

  • Output Connector: Select this option to display an arrow icon on the right of attributes that you can use as output attributes.

  • Enable Horizontal Scrolling: Select this option to enable horizontal scrolling for operators.

  • Automatic Layout: Select this option to use an automatic layout for the mapping.

Types of Operators

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

This section introduces the types of operators and refers you to other chapters in this manual for detailed information.

  • Oracle Source/Target Operators: These operators represent Oracle Database objects in the mapping. It also contains Flat File Source and Target operators.

  • Remote and Non-Oracle Source and Target Operators: The use of these operator have special requirements discussed in "Using Remote and non-Oracle Source and Target Operators" in the Warehouse Builder Online Help.

  • Data Flow Operators: Data flow operators transform data.

  • Pre/Post Processing Operators: Calls a function or procedure before or after executing a mapping

  • Pluggable Mapping Operators: These are mappings that function as operators in other mappings.

Oracle Source/Target Operators

Use source and target operators to represent relational database objects and flat file objects.

Table 7-1 lists each source and target operator alphabetically, gives a brief description.

Table 7-1 Source and Target Operators

Icon Operator Description
This illustration is described in the surrounding text.

Constant operator

Produces a single output group that can contain one or more constant attributes.

This illustration is described in the surrounding text.

Construct Object operator

Produces object types and collection types.

This illustration is described in the surrounding text.

Cube operator

Represents a cube that you previously defined.

This illustration is described in the surrounding text.

Data Generator operator

Provides information such as record number, system date, and sequence values.

This illustration is described in the surrounding text.

Dimension operator

Represents a dimension that you previously defined.

This illustration is described in the surrounding text.

Expand Object operator

Expands an object type to obtain the individual attributes that comprise the object type.

This illustration is described in the surrounding text.

External Table operator

Represents an external table that you previously defined or imported.

This illustration is described in the surrounding text.

Flat File operator

Represents a flat file that you previously defined or imported.

This illustration is described in the surrounding text.

Materialized View operator

Represents a materialized view that you previously defined.

This illustration is described in the surrounding text.

Sequence operator

Generates sequential numbers that increment for each row.

This illustration is described in the surrounding text.

Table operator

Represents a table that you previously defined or imported.

This illustration is described in the surrounding text.

Varray Iterator operator

Iterates through the values in the table type.

This illustration is described in the surrounding text.

View operator

Represents a view that you previously defined or imported.


Data Flow Operators

Use data flow operators to transform data in a mapping.

Table 7-2 lists each data flow operator alphabetically, gives a brief description. For more information on these transformation operators, see "Data Flow Operators" in the Warehouse Builder Online Help.

Table 7-2 Data Flow Operators

Icon Operator Description
This illustration is described in the surrounding text.

Aggregator operator

Performs data aggregations, such as SUM and AVG, and provides an output row set with aggregated data.

This illustration is described in the surrounding text.

Anydata Cast operator

Converts an object of type Sys.AnyData to either a primary type or to a user-defined type.

This illustration is described in the surrounding text.

Deduplicator operator

Removes duplicate data in a source by placing a DISTINCT clause in the select code represented by the mapping.

This illustration is described in the surrounding text.

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.

This illustration is described in the surrounding text.

Filter operator

Conditionally filters out rows from a row set.

This illustration is described in the surrounding text.

Joiner operator

Joins multiple row sets from different sources with different cardinalities and produces a single output row set.

This illustration is described in the surrounding text.

Key Lookup operator

Performs a lookup of data from a lookup object such as a table, view, cube, or dimension.

This illustration is described in the surrounding text.

Match Merge operator

Data quality operator that identifies matching records and merges them into a single record.

This illustration is described in the surrounding text.

Name and Address operator

Identifies and corrects errors and inconsistencies in name and address source data.

This illustration is described in the surrounding text.

Pivot operator

Transforms a single row of attributes into multiple rows. Use this operator to transform data that contained across attributes instead of rows.

This illustration is described in the surrounding text.

Set Operation operator

Performs union, union all, intersect, and minus operations in a mapping.

This illustration is described in the surrounding text.

Sorter operator

Sorts attributes in ascending or descending order.

This illustration is described in the surrounding text.

Splitter operator

Splits a single input row set into several output row sets using a boolean split condition.

This illustration is described in the surrounding text.

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.

You can use a table function operator as a target.

This illustration is described in the surrounding text.

Transformation operator

Transforms the attribute value data of rows within a row set using a PL/SQL function or procedure.

This illustration is described in the surrounding text.

Unpivot operator

Converts multiple input rows into one output row. It enables 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.


Pre/Post Processing Operators

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

Table 7-3 lists the Pre/Post Process operators and the Mapping Parameter operators.

Table 7-3 Pre/Post Processing Operators

Icon Operator Description
This illustration is described in the surrounding text.

Mapping Input Parameter operator

Passes parameter values into a mapping.

This illustration is described in the surrounding text.

Mapping Output Parameter operator

Sends values out of a mapping.

This illustration is described in the surrounding text.

Post-Mapping Process operator

Calls a function or procedure after executing a mapping.

This illustration is described in the surrounding text.

Pre-Mapping Process operator

Calls a function or procedure prior to executing a mapping.


Pluggable Mapping Operators

A pluggable mapping is a reusable grouping of mapping operators that behaves as a single operator.

Table 7-4 lists the Pluggable Mappings operators.

Table 7-4 Pluggable Mapping Operators

Icon Operator Description
This illustration is described in the surrounding text.

Pluggable Mapping operator

Represents a reusable mapping.

This illustration is described in the surrounding text.

Pluggable Mapping Input Signature operator

A combination of input attributes that flow into the pluggable mapping.

This illustration is described in the surrounding text.

Pluggable Mapping Output Signature operator

A combination of output attributes that flow out of the pluggable mapping.


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 chapter refers to objects in the workspace either generically as workspace objects or specifically as workspace tables, workspace views, and so on. And this chapter 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 reimport 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 as described in "Managing Metadata Dependencies" in the Warehouse Builder Online Help. Alternatively, synchronize manually from within the Mapping Editor as described in "Synchronizing Operators and Workspace Objects".

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 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 details on how to use this dialog box, see "Add Operator Dialog Box".

    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.

Adding Operators that Bind to Workspace Objects

You can bind the following operators to associated objects in the workspace using the Add Operator Dialog Box:

  • Cube operators

  • Dimension operators

  • External Table operators

  • Flat File operators

  • Materialized View operators

  • Pre Mapping Process operators

  • Post Mapping Process operators

  • Sequence operators

  • Table operators

  • Transformation operators

  • View operators

Add Operator Dialog Box

When you add an operator that you can bind to a workspace object, the Mapping Editor displays the Add <operator name> Operator 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 on how to use this option in a mapping design, see "Example: Using the Mapping Editor to Create Staging Area Tables".

Select from Existing Repository 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.

Name Tab

The Name 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 "Mapping Naming Conventions".

Groups Tab

Edit group information on the Groups tab.

Each group has a name, direction, and optional description. You can rename groups for most operators but cannot change group direction for any of the operators. 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.

Input and Output Tabs

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, seconds precision, and optional description.

Edit attribute information on the each of the remaining tabs.

Figure 7-3 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 7-3 Input/Output Tab on the Operator Editor

This illustration is described in the surrounding text.
Description of "Figure 7-3 Input/Output Tab on the Operator Editor"

You can add, remove, and edit attributes. The Mapping Editor grays 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 data type, length, precision, and scale in an attribute, follow PL/SQL rules. When you synchronize the operator, Warehouse Builder checks the attributes based on SQL rules.

Mapping Naming Conventions

The rules for naming objects in the Mapping Editor depend on the naming mode you select in "Naming Preferences". Warehouse Builder maintains a business and a physical name for each object in the workspace. The business name is its descriptive business name. The physical name is the name Warehouse Builder uses when generating code.

When you name objects while working in one naming mode, Warehouse Builder creates a default name for the other mode. Therefore, when working in the business name mode, if you assign a mapping a name that includes mixed cases, special characters and spaces, Warehouse Builder creates a default physical name for you. For example, if you save a mapping with the business name My Mapping (refer to doc#12345), the default physical name is MY_MAPPING_REFER_TO_DOC#12345.

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

Naming and Describing Mappings

In the physical naming mode, a mapping name can be from 1 to 30 alphanumeric characters and blank spaces are not allowed. In the business naming mode, the limit is 2000 characters and blank spaces and special characters are allowed. In both naming modes, the name should be unique across the project.

Note for scheduling mappings: If you intend to schedule the execution of the mapping, there is an additional consideration. For any ETL object you want to schedule, the limit is 25 characters for physical names and 1995 characters for business names. Follow this additional restriction to enable Warehouse Builder to append to the mapping name the suffix _job and other internal characters required for deployment and execution.

After you create the mapping definition, you can view its physical and business name on the mapping properties sheet. Right-click the mapping from the Design Center, select Properties, and view the names on the General tab.

Edit the description of the mapping as necessary. The description can be between 2 and 2,000 alphanumeric character and can contain blank spaces.

Naming Conventions for Attributes and Groups

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

Naming Conventions for Operators

Business names for operator must meet the following requirements:

  • The length of the operator name can be any string of 200 characters.

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

Physical names must meet the following requirements:

  • All objects other than operators can contain a maximum of 30 characters. However, the limit is 28 for operators since Warehouse Builder reserves two characters for use when navigating through the OMB Scripting Language.

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

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

In addition to physical and business names, some operators also have bound names. Every operator associated with a workspace object has a bound name. During code generation, Warehouse Builder uses the bound name to reference the operator to its workspace object. Bound names 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 indirectly by synchronizing from an operator to the workspace.

  • When you rename the business name for an operator or attribute, Warehouse Builder propagates the new business name as the bound name when you synchronize. However, business 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 business 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, operators contain three predefined display sets, ALL, MAPPED, and UNMAPPED. Table 7-5 describes the default display sets.

Table 7-5 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.

UNMAPPED

Includes only those attributes that are not connected to other attributes.


Defining Display Sets

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

To define a display set:

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

    The Display Set dialog box is displayed as shown in Figure 7-4.

    Figure 7-4 Display Set Dialog Box

    This illustration is described in the surrounding text.
    Description of "Figure 7-4 Display Set Dialog Box"

  2. Click the row below UNMAPPED and enter a name and description for the new display set.

  3. All available attributes for the operator appear in Attributes of selected display set. 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. Click Select Display Set and select the desired display set.

Connecting Operators

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 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 one through three until you create all the required data flow connections.

    Figure 7-5 displays a mapping with attributes connected.

    Figure 7-5 Connected Operators in a Mapping

    This illustration is described in the surrounding text.
    Description of "Figure 7-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.

Connecting Groups

When you connect groups, the Mapping Editor assists you by either automatically copying the attributes or prompts you for more information as described in "Using the Connect Operators 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.

    The mapping should now resemble Figure 7-6 with one source table and one staging area table without attributes.

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

    This illustration is described in the surrounding text.
    Description of "Figure 7-6 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.

    Warehouse Builder displays the Create And Bind dialog box.

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

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

Using the Connect Operators Dialog Box

If you connect from one operator to a target operator with existing attributes, the Mapping Editor starts the Connect Operators dialog box.

Select one of the following criteria for copying and connecting attributes:

After you select one of the three options, select Go. The Connect Operators dialog box displays a list of connected attributes.

Figure 7-7 displays the Connected attributes section.

Figure 7-7 Connected Attributes

This illustration is described in the surrounding text.
Description of "Figure 7-7 Connected Attributes"

You can deselect attributes by clearing the Map check box. View the results of your selections under Comments.

When you select OK, Warehouse Builder copies the source attributes to the target group and connects the attributes.

Copy Source Attributes to Target Group and Match

Use this option to copy source attributes to a target group that already contains attributes. Warehouse Builder connects from the source attributes to the new target attributes based on the selections you make in the Connect Operators dialog box. 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 connect existing attributes based on the position of the attributes in their respective groups. The Mapping Editor connects all attributes in order until all attributes for the target are matched. If the source operator contains more attributes than the target, then the remaining source attributes are left unconnected.

Match by Name of Source and Target Attributes

Use this option to connect attributes with matching names. By selecting from the list of options, you connect between names that do not match exactly. You can combine the following options:

  • Ignore case differences: Considers the same character in lower-case and upper-case a match. For example, the attributes FIRST_NAME and First_Name match.

  • Ignore special characters: Specify characters to ignore during the matching process. For example, if you specify a hyphen and underscore, the attributes FIRST_NAME, FIRST-NAME, and FIRSTNAME all match.

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

After you set the matching criteria, click Go.

The Displayed Mappings field displays the possible connections between attributes which you can verify and deselect before implementing.

Using Pluggable Mappings

You can reuse the data flow of a mapping by creating a pluggable mapping around the portion of the flow you want to reuse. A pluggable mapping is a reusable grouping of mapping operators that works as a single operator. It is similar to the concept of a function in a programming language and is a graphical way to define a function.

Note:

The use of pluggable mappings requires the Warehouse Builder Enterprise ETL Option.

Once defined, a pluggable mapping appears as a single mapping operator, nested inside a mapping. You can reuse a pluggable mapping more than once in the same mapping, or in other mappings. You can include pluggable mappings within other pluggable mappings.

Like any operator, a pluggable mapping has a signature consisting of input and output attributes that enable you to connect it to other operators in various mappings. The signature is similar to the input and output requirements of a function in a programming language.

A pluggable mapping can be either reusable or embedded:

Creating a Pluggable Mapping

Pluggable mappings are usually predefined and used when required.You can create pluggable mappings either from within a mapping by using the mapping editor, or from the navigation tree by using the wizard. The wizard is the faster way to create a pluggable mapping because it makes some default choices and guides you through fewer choices. You can make additional choices later in the Pluggable Mapping Editor. The editor presents you with all the settings in a series of tabs.

The Pluggable Mappings node in the navigation tree contains two nodes, Standalone and Pluggable Mapping Folders. You can create pluggable mappings from either of these nodes.

Standalone Pluggable Mapping

To create a standalone pluggable mapping:

  1. Expand the Pluggable Mappings node in the Project Explorer.

  2. Right-click Standalone, and select New.

  3. This opens the Create Pluggable Mapping wizard, which guides you through the process of creating a new pluggable mapping. Click Help for information on the values to be entered on each page of the wizard.

Once you create a new pluggable mapping, Warehouse Builder opens the pluggable mapping editor and displays the name of the pluggable mapping on the title bar. The pluggable mapping editor is similar to the mapping editor, and you can add the desired operators from the palette to create a mapping.

A pluggable mapping is considered as an operator by the Warehouse Builder. You can insert it into any mapping. In the mapping editor, drag and drop Pluggable Mapping from the palette onto the canvas. This opens the Add Pluggable Mapping dialog box. You can select the desired pluggable mapping and add it to the mapping.

Pluggable Mapping Folders

A folder is a grouping mechanism for pluggable mappings. You can keep your pluggable mappings private, or you can place them into folders (libraries) and then publish them so that others can access them for their design work. To create a new folder to store pluggable mappings:

  1. Expand the Pluggable Mappings node in the Project Explorer.

  2. Right-click Pluggable Mapping Folders, and select New. This opens the Create Pluggable Mapping Folder dialog box.

  3. Enter a name for the folder and provide a description (optional).

  4. Click OK to save the folder and exit the wizard.

The folder appears on the Project Explorer. The Pluggable Mapping Folders node gives you the option of creating a pluggable mapping either at the time of creating a folder or after creating the folder. You can also move a pluggable mapping to any folder on the tree.

At the time of creating the Pluggable Mapping folder, if you select the Proceed to Pluggable Mapping Wizard option, the Create Pluggable Mapping Wizard opens and you can create a new pluggable mapping.

If you do not select the option, only the pluggable mapping folder gets created. To create a pluggable mapping under this folder:

  1. Under the Pluggable Mappings Folders node, right-click the folder and select New.

  2. This opens the Create Pluggable Mapping wizard, which guides you through the process of creating a new pluggable mapping.

Signature Groups

The signature is a combination of input and output attributes flowing to and from the pluggable mapping. Signature groups are a mechanism for grouping the input and output attributes.

A pluggable mapping must have at least one input or output signature group. Most pluggable mappings are used in the middle of a logic flow and have input as well as output groups.

  • To create an additional signature group, click Add. To overwrite the default name assigned to the group, type over its name in the Group column. Enter its orientation as an input or output group in the Direction column. Enter an optional description of the group in the Description column.

  • To remove a signature group, select the group you want to remove and click Remove.

Click Next to continue with the wizard.

Input Signature

The input signature is the combination of input attributes that flow into the pluggable mapping. Define the input attributes for each input signature group you created.

If you defined multiple input signature groups, select the group to which you want to add attributes from the Group list box. Then click Add to add attributes. You can overwrite the default name given to each attribute by typing over the name in the Attribute column. You can change the data type of each attribute by clicking on its default data type and selecting a new data type from the resulting drop list. You can assign the length, precision, scale, and seconds precision by clicking the corresponding field and using the up and down arrows or typing in a number. Note that some of these fields are disabled depending on the data type you specify.

You can remove an attribute by selecting the attribute and clicking Remove.

Click Next to continue with the wizard.

Output Signature

The output signature is the combination of output attributes that flow out of the pluggable mapping. Define the output attributes for each output signature group you created.

If you defined multiple output signature groups, select the group to which you want to add attributes from the Group list box. Then click Add to add attributes. You can overwrite the default name given to each attribute by typing over the name in the Attribute column. You can change the data type of each attribute by clicking on its default data type and selecting a new data type from the resulting drop list. You can assign the length, precision, and scale by clicking the corresponding field and using the up and down arrows or typing in a number. Note that some of these fields are disabled depending on the data type you specify.

You can remove an attribute by selecting the attribute and clicking Remove.

Click Next to continue with the wizard.

You can also add an Input Signature or an Output Signature from the palette of the pluggable mapping editor. Note that a pluggable mapping can have only one Input Signature and Output Signature. Also, pluggable mapping Input and Output signatures can only be added within pluggable mappings. They cannot be added to normal mappings.

Pluggable Mapping Editor

The pluggable mapping editor is similar to the mapping editor. Use the main panel to select and edit the operators that constitute your pluggable mapping. For more information on using this editor to design pluggable mappings, consult these topics:

Setting Mapping Properties

When you select white space on the mapping canvas, the editor displays the mapping properties in the property inspector along the left side. You can set the following property for the mapping:

Target Load Order

If your mapping includes only one target or is a SQL*Loader or ABAP mapping, target load ordering does not apply. Accept the default settings and continue with your mapping design.

When you design a PL/SQL mapping with multiple targets, Warehouse Builder calculates a default ordering for loading the targets. If you define foreign key relationships between targets, Warehouse Builder creates a default order that loads the parent and then the child. If you do not create foreign key relationships or if a target table has a recursive relationship, Warehouse Builder assigns a random ordering as the default.

You can override the default load ordering by setting the Target Load Order property. If you make a mistake when reordering the targets, you can restore the default ordering by selecting the Reset to Default option.

To specify the loading order for multiple targets:

  1. Click whitespace in the mapping canvas to view the mapping properties in the Mapping Properties panel in the upper left corner.

  2. Go to the Map Targets Load Order property and click the Ellipsis button on the right of this property.

    Warehouse Builder displays the Targets Load Order dialog box which shows TARGET2 loading before TARGET1.

    Figure 7-8 displays the Target Load Order dialog box.

    Figure 7-8 Target Load Order Dialog Box

    Description of Figure 7-8 follows
    Description of "Figure 7-8 Target Load Order Dialog Box"

  3. To change the loading order, select a target and use the shuttle buttons on the right to move the target up or down on the list.

Reset to Default

Use the Reset to Default button to instruct Warehouse Builder to recalculate the target loading order. You may want to recalculate if you made an error reordering the targets or if you assigned an order and later change the mapping design such that the original order became invalid.

Setting Operator, Group, and Attribute Properties

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

You can view and set the following types of properties:

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 "Managing Metadata Dependencies" 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.

You can synchronize in the following method:

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 an operator, complete the following steps:

  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.

    The Synchronize Operator dialog box displays.

  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 whether to perform inbound or outbound synchronization. Inbound synchronization synchronizes the data object with the mapping operator. Outbound synchronization synchronizes the mapping operator with the data object.

  4. As an optional step, set the Matching Strategies and Synchronize strategy.

  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 "Managing Metadata Dependencies" 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 newer version and maintain different object definitions for each version.

  • 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 7-6 lists operators and the types of workspace objects from which you can synchronize.

Table 7-6 Operators Synchronized with Workspace Objects

To: Operator From: Workspace Object Type

Cube Operator

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

Dimension Operator

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

External Table Operator

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

Flat File Operator

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

Key Lookup Operator

Tables only

Materialized View Operator

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

Post Mapping Process Operator

Transformations only

Pre Mapping Process Operator

Transformations only

Sequence Operator

Sequences only

Table Operator

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

Transformation Operator

Transformations only

View Operator

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. To update an operator such as external table based on its associated flat file, see "Synchronizing an External Table Definition with a Record in a Flat File" in the Warehouse Builder Online Help.

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 object. Table 7-7 lists the operators from which you can synchronize.

Table 7-7 Outbound Synchronize Operators

Mapping Objects Create Workspace Objects Propagate Changes Replace Workspace Objects Notes

External Tables

Yes

Yes

Yes

Updates the workspace external table only and not the flat file associated with the external table. See "Synchronizing an External Table Definition with a Record in a Flat File" in the Warehouse Builder Online Help.

Flat Files

Yes

Yes

No

Creates a new, comma-delimited 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.


Advanced Options for Synchronizing

Use the Synchronization Plan dialog box to view and edit the details of how Warehouse Builder synchronizes your selected objects. After you select from the Matching Strategies, click Refresh Plan to view the actions Warehouse Builder takes.

In the context of synchronizing, source refers to the object from which to inherit differences and target refers to the object to be changed.

For example, in Figure 7-9, the flat file PAYROLL_WEST is the source and the flat file operator PAYROLL is the target. Therefore, Warehouse Builder creates new attributes for the PAYROLL operator to correspond to fields in the flat file PAYROLL_WEST.

Figure 7-9 Advanced Synchronizing Options

This illustration is described in the surrounding text.
Description of "Figure 7-9 Advanced Synchronizing Options "

Matching Strategies

Set the matching strategies to determine how Warehouse Builder compares an operator to a workspace object. If synchronization introduces changes such as adding or deleting attributes in an operator, Warehouse Builder refreshes the Mapping Editor. If synchronizing removes an operator attribute, data flow connections to or from the attribute are also removed. If synchronizing adds an operator attribute, the Mapping Editor displays the new attributes at the end of the operator. Data flow connections between matched attributes are preserved. If you rename an attribute in the source object, it is interprets it as if the attribute were deleted and a new attribute added.

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

Match by Object Identifier

This strategy compares the unique object identifiers of an operator attributes with those of a workspace object. Match by object identifier is not available for synchronizing an operator and workspace object of different types such as a view operator and a workspace table.

Use this strategy if you want the target object to be consistent with changes to the source object and if you want to maintain separate business names despite changes to physical names in the target object.

Warehouse Builder removes attributes from the source object that do not correspond to attributes in the target object. This can occur when an attribute is added to the source or removed from the workspace object without properly synchronizing the change.

Match by Bound Name

This strategy matches the bound names of the operator attributes to the physical names of the workspace object attributes. Matching is case-sensitive.

Use this strategy if you want bound names to be consistent with physical names in the workspace object. You can also use this strategy with a different workspace object if there are changes in the workspace object that would change the structure of the operator.

Warehouse Builder removes attributes of the operator that cannot be matched with those of the workspace object. Attributes of the selected workspace object that cannot be matched with those of the operator are added as new attributes to the operator. Because bound names are read-only after you have bound an operator to a workspace object, you cannot manipulate the bound names to achieve a different match result.

Match by Position

This strategy matches operator attributes with columns, fields, or parameters of the selected workspace object by position. The first attribute of the operator is synchronized with the first attribute of the workspace object, the second with the second, and so on.

Use this strategy to synchronize an operator with a different workspace object and you want to preserve the business names in the operator attributes. This strategy is most effective when the only changes to the workspace object are the addition of extra columns, fields, or parameters at the end of the object.

If the target object has more attributes than the source object, then Warehouse Builder removes the excess attributes. If the source object has more attributes than target object, Warehouse Builder adds as new attributes.

Using DML Error Logging

Error logging enables the processing of DML statements to continue despite errors being encountered during the statement execution. The details of the error such as the error code and the associated error message are stored in an error table. After the DML operation completes, you can check the error table to correct rows with errors. DML error logging is supported for SQL statements such as INSERT, UPDATE, MERGE, and multi-table insert. It is useful in long-running, bulk DML statements.

Warehouse Builder provides error logging for the tables, views, and materialized views used in set-based PL/SQL mappings. To enable error logging, you set the Shadow table name property of the table, view, or materialized view. DML error logging is supported only for target schemas created on Oracle Database 10g Release 2 or later versions.

About Error Tables

Error tables store error details. You can define error tables for tables, views, and materialized views only.

Error tables are used for the following purposes:

  • DML error logging (including physical errors)

  • Capturing logical errors when data rules are applied to tables, views, or materialized views

An error table is generated and deployed along with the base table, view, or materialized view. When you drop a data object, the shadow table associated with it is automatically dropped.

Error Tables and DML Error Logging

When DML error logging is enabled for a data object by setting the Shadow table name property for the object, the error table contains the following:

  • DML error columns, as described in Table 7-8

  • all columns from the data object with which the shadow table is associated

Table 7-8 DML Error Columns in Error Tables

Column Name Description

ORA_ERR_NUMBER$

Oracle error number

ORA_ERR_MESG$

Oracle error message text

ORA_ERR_ROWID$

Rowid of the row in error (for update and delete)

ORA_ERR_OPTYPE$

Type of operation: insert (I), update (U), delete (D)

ORA_ERR_TAG$

Step or detail audit ID from the runtime audit data. This is the STEP_ID column in the runtime view ALL_RT_AUDIT_STEP_RUNS.


For scalar data types in the source data object, if no data rules are applied to the data object, the columns in the error table are of data type VARCHAR2(4000). This allows physical data errors such as ORA-12899: value too large for column, to be captured. If data rules are applied, the columns in the error table are of the same data type as the source columns.

For example, the table TEST has the two columns C1, of data type NUMBER, and C2, of data type VARCHAR2(10). The error table generated for TEST will contain the DML error columns, C1, and C2. If no data rules are applied to TEST, the data type for both C1 and C2 will be VARCHAR2(4000). If data rules are applied to TEST, C1 will be NUMBER and C2 will be of data type VARCHAR2(10).

Error Tables and Data Rules

When one or more data rules are defined for a data object, the error table for this data object contains the following:

  • Columns from the data object

    These columns are of the same data type and precision as the ones in the data object.

  • DML error columns, as described in Table 7-8

  • Data rule columns

    The data rule columns store details such as the operator that caused the data rule violation, the cause of the error, severity, and the audit run details.

Using Error Tables for DML Error Logging and Data Rules

When you define data rules on a data object for which DML error logging is enabled, the error table generated by Warehouse Builder contains the columns from the data object, the data rules columns, and the DML error columns. The data type and precision of the columns from the data object are the same as the ones in the base data object. This could result in the failed inserts into the error table when errors occur during DML operations. For example, some errors, such as value too small, may cause error table insert failure.

Thus, if you want to perform DML error logging for a data object that has data rules applied, it is recommended that you create your own error tables. Ensure that the error table that you create contains the columns required for data rules and the DML error logging columns.

Enabling DML Error Logging

DML error logging is generated for set-based PL/SQL mappings if the following conditions are satisfied:

  • the Error table name property is set for the Table, View, or Materialized View operator.

  • the PL/SQL Generated Mode configuration property of the module that contains the mapping is set to Oracle 10gR2, Oracle 11gR1, or Default.

    If the value is set to Default, ensure that location associated with this module has the Version property set to 10.2 or 11.1.

To enable error logging for a data object:

  1. In the Project Explorer, right-click the data object for which DML error logging should be enabled, and select Open Editor.

    The Data Object Editor for the data object is displayed.

  2. On the canvas, select the data object.

  3. In the Properties panel, specify a value for the Shadow table name property.

If you do not specify a shadow table name for a data object, DML error logging is not enabled for that object. However, when a data object has data rules associated with it, if you do not specify a error table name for the object, Warehouse Builder creates an error table using a default name. For example, if the name of the table for which you specified data rules is EMP, the error table is called EMP_ERR.

When you use a data object in a mapping, the Error Table Name property for this data object is derived from the Shadow table name property of the data object.

Note:

If you modify the error table name for a data object (using the Shadow table name property), you must synchronize all the operators bound to this data object.

DML Error Logging and ETL

The execution of mappings that contain data objects for which DML error logging is enabled fails if any of the following conditions occur:

  • the number of errors generated exceeds the specified maximum number of errors for the mapping

    The default set for this value is 50. You can modify this value by setting the Maximum number of errors configuration property of the mapping. In the Project Explorer, right-click the mapping and select Configure. In the Maximum number of errors property, specify the maximum number of errors that can generated before the mapping execution is terminated.

  • errors occur due to functionality that is not supported.

    See "DML Error Logging Limitations".

You can truncate the error table and delete error details generated during a previous load. This helps in housekeeping of the error tables. To truncate an error table before the map is executed, select the Truncate Error Table property of the operator bound to the data object that has DML error logging enabled.

The properties Roll up Errors and Select only errors from this property are not used for DML error logging.

The Error table name and Truncate error table properties of Table, View, or Materialized View operators are not used for row-based code.

DML Error Logging Limitations

DML error logging has certain limitations. DML error logging is not supported for non-scalar datatypes. In addition, each DML statement has specific limitations, which are listed in documentation related to that statement.

See Also:

Oracle Database SQL Language Reference for limitations on DML error logging for each DML statement

Depending on your error logging needs you can configure the table operator in a mapping to use the APPEND or NOAPPEND hint. For example, direct-path insert does not support error logging for unique key violations. To log unique key violations, use the NOAPPEND hint.

If you have an error table defined for a data object, you cannot upgrade the data object using the Upgrade option in the Control Center Manager. If you modify the Shadow table name property after the data object is deployed, you must first drop the data object and then redeploy it. If this data object was used in a mapping, ensure that you synchronize the mapping operator with the data object, drop the data object, redeploy the data object and the mapping.

Debugging a Mapping

You can use the Mapping Editor to debug complex data flows you design in mappings. Once you begin a debug session and connect to a valid target schema, the debugging functions appear on the toolbar and under Debug on the Mapping Editor main menu. You can run a debugging session using a defined set of test data and follow the flow of data as it is extracted, transformed, and loaded to ensure that the designed data flow behaves as expected. If you find problems, you can correct them and restart the debug session to ensure that the problems have been fixed before proceeding to deployment.

Before you Begin

Ensure that you are connected to a Control Center and that the Control Center is running.

Starting a Debug Session

To start a debug session, from the Mapping Editor, select Debug and then Start, or you can click Debug Start on the toolbar. The Mapping Editor switches to debug mode with the debug panels appearing in the bottom and the side of the editor. and the debugger connects to the appropriate Control Center for the project. The debug-generated code is deployed to the target schema specified by the location of the module that contains the map being debugged.

Note:

When the connection cannot be made, an error message is display and you have an option to edit the connection information and retry.

After the connection has been established, a message displays to indicate that you may want to define test data. When you have previously defined test data, then you are asked if you want to continue with initialization.

To debug a mapping, each source or target operator must be bound to a database object and test data must be defined for the database object. By default, the debugger uses the same source and target data that is currently defined for the non-debug deployment of the map.

The Debug Panels of the Mapping Editor

When the Mapping Editor is opened in Debug mode it has new panels Debug Info Panel and Debug Data Panel.

Debug Info Panel

When the Mapping Editor is in Debug mode, the left middle panel is the Debug Info panel which contains the following tabs:

  • Messages: Displays all debugger operation messages. These messages let you know the status of the debug session. This includes any error messages that occur while running the mapping in debug mode.

  • Breakpoints: Displays a list of all breakpoints that you have set in the mapping. You can use the check boxes to activate and de-activate breakpoints. For more information, see "Setting Breakpoints".

  • Test Data: Displays a list of all data objects used in the mapping. The list also indicates which data objects have test data defined.

Debug Data Panel

When the Mapping Editor is in Debug mode, the Debug Data panel is the right bottom panel. The Debug Data Panel includes Step Data and watch point tabs that contain input and output information for the operators being debugged. The Step Data tab contains information about the current step in the debug session. Additional tabs can be added for each watch you set. These watch tabs allow you to keep track and view data that has passed or will pass through an operator regardless of the currently active operator in the debug session. Operators that have more than one input group or more than one output group display an additional list that enables you to select a specific group.

If an operator has more than one input or output group then the debugger will have a list in the upper right corner, above the input or output groups. Use this list to select the group you are interested in. This applies both to the step data and to a watch.

Defining Test Data

Every source or target operator in the mapping is listed on the Test Data tab in the left bottom panel. It also contains the object type, the source, and a check mark that indicates that the database object has already been bound to the source or target operator.

The object type listed on the tab is determined by whether or not the column names in the data source you select (for example, a table) matches the columns in the mapping operators. There are two possible types:

  • Direct Access. When there is an exact match then the type is listed as Direct Access.

  • Deployed as View. When you choose a data source with columns that do not match the mapping operator columns, you can choose how you want the columns mapped. This object would then be deployed as a view when you run the mapping and the type will be listed as Deployed as View.

Click Edit to add or change the binding of an operator as well as the test data in the bound database objects. Before you can run the mapping in debug mode, each listed source or target operator must be bound and have a check mark. The need to have test data defined and available in the bound database object depends on what aspect of the data flow you are interested in focusing on when running the debug session. Typically, you will need test data for all source operators. Test data for target operators is usually necessary if you want to debug loading scenarios that involve updates or target constraints.

To define or edit test data:

  1. From the Test Data tab in the Mapping Editor, select an operator from the list and click Edit. The Define Test Data dialog box is displayed.

  2. In the Define Test Data dialog box, specify the characteristics of the test data that you want Warehouse Builder to use when it debugs. There are many characteristics that you can specify. For example, you can specify that the test data be from a new or existing database object or that you can or cannot manually edit the test data. Click Help on the Define Test Data dialog box for more information.

Creating New Tables to Use as Test Data

When you create a new table using the Define Test Data dialog box, the name of the table is the name of the data operator prefixed by DBG_. (Note that, if this name all ready exists in the target, then Warehouse Builder adds a sequence number as a suffix to guarantee a unique object name.) Warehouse Builder creates the table in the target schema that you specified when you started the debug run. The debugger does not automatically drop that table, consequently you can always reuse it for other sessions. Constraints are not carried over for the new table.

When you create a new table, Oracle Warehouse Builder creates the new table in the connected runtime schema. The new table has an automatically generated name and the value of the Debug Binding name changes to reflect the new table name. The new table has columns defined for it that exactly match the names and data types of the mapping source or target attributes. In addition, any data that is displayed in the grid at the time the table is created are copied into the newly created table.

Editing the Test Data

You can edit test data at anytime using the Define Test Data dialog box. If you change the binding of the operator to another database object, you must re-initialize the debug session to implement the change before running the mapping again in debug mode.

Note:

The data loaded in the target definitions will be implicitly committed. If you do not want the target objects updated, then you should create copies of target objects by clicking Create New Table.

Setting Breakpoints

If you are interested in how a specific operator is processing data, you can set a breakpoint on that operator which will cause a break in the debug session. This enables you to proceed quickly to a specific operator in the data flow without having to go through all the operators step by step. When the debug session gets to the breakpoint, you can run data through the operator step by step to ensure it is functioning as expected. Breakpoint settings are not stored when you close the mapping.

To set or remove a breakpoint:

  1. From the Mapping Editor, click an operator and then select Debug and then Set Breakpoint. You can also click the Breakpoint button on the toolbar to toggle the breakpoint on and off for the currently highlighted operator.

    If you are setting the breakpoint, the name of the operator set as a breakpoint appears in the list on the Breakpoints tab on the left bottom panel. If you are removing the breakpoint the name is removed. Use the Clear button on the Breakpoint tab to remove breakpoints.

  2. Uncheck or check the breakpoints on the Breakpoint tab to disable or enable them.

Setting Watches

The Step Data tab on the right bottom panel always shows the data for the current operator. If you want to keep track of data that has passed through any other operator irrespective of the active operator, you can set a watch.

Use watches to track data that has passed through an operator or in the case of sources and targets, the data that currently resides in the bound database objects. You can also set watch points on operators after the debug run has already passed the operator and look back to see how the data was processed by an operator in the data flow.

To set a watch:

From the Mapping Editor, click an operator and then select Debug and then Set Watch. You can also click the Set Watch button on the toolbar to toggle the watch on and off. The name of the operator will appear as an additional tab on the right bottom panel bottom containing the input and or output groups for the operator.

To remove a watch:

To remove a watch, again select the operator and use the watch button on the toolbar, use set watch from the debug menu or use toggle debug watch from the right mouse button menu.

Running the Mapping

After you have defined the test data connections for each of the data operators, you can initially generate the debug code by selecting Re-initialize from the Debug menu, or by clicking Re-initialize on the toolbar. Warehouse Builder generates the debug code and deploys the package to the target schema you specified.

You can choose to run the debug session in one of the following modes:

  • Continue processing until the next breakpoint or until the debug run finishes by using the Resume button on the toolbar or the associated menu item.

  • Process row by row using the Step button on the toolbar or use the associated menu item.

  • Process all remaining rows for the current operator by using the Skip button on the toolbar or the associated menu item.

  • Reset the debug run and go back to the beginning by using the Reset button or the associated item from the debug menu.

Selecting the First Source and Path to Debug

A mapping may have more than one source and more than one path to debug:

  • When a mapping has more than one source then Warehouse Builder prompt you to designate the source with which to begin. For example, when two tables are mapped to a joiner, you will have to select the first source table you want to use when debugging.

  • There may be multiple paths that the debugger can walk through after it has finished one path. For example, this is the case when you use a splitter. Having finished one path the debugger asks you whether you would like to complete the other paths as well.

The mapping finishes if all target operators have been processed or if the maximum number of errors as configured for the mapping has been reached. The debug connection and test data definitions are stored when you commit changes to the Warehouse Builder workspace. Breakpoint and watch settings are not stored and must be re-set each time you open a mapping.

As the debugger runs it generates debug messages whenever applicable. You can follow the data flow through the operators. The active operator is indicated by a red dashed box surrounding the operator.

Debugging Mappings with Correlated Commit

How a mapping is debugged varies depending on whether the mapping has the Correlated Commit parameter set to ON or OFF:

  • When you begin a debug session for a mapping that has the Correlated Commit parameter set to ON, the mapping is not debugged using paths. Instead, all paths are executed and all targets are loaded during the initial stepping through the mapping regardless of what path is chosen. Also, if one of the targets has a constraint violation for the step, then none of the targets are loaded for that step.

  • When you begin a debug session for a mapping that has the Correlated Commit parameter set to OFF, the mapping is debugged using one path at a time. All other paths are left unexecuted and all other targets are not loaded unless the you reach the end of the original path and then chooses to go back and execute another path in the mapping.

For example: You have a mapping that has a source S1, connected to a splitter that goes to two targets T1 and T2:

  • If Correlated Commit is OFF, then the mapping is debugged starting with S1. You can then choose either the path going to T1 or the path going to T2. If you choose the path to T1, the data going to T1 is processed and displayed, and the target T1 is loaded. After T1 is completely loaded, you are given the option to go back and execute the other path and load target T2.

  • If Correlated Commit is ON, then the mapping is also debugged staring with S1 and you are given the option of choosing a path however in this case, the path you choose only determines the path that gets displayed in the mapping editor as you step through the data. All paths are executed simultaneously. This is also how a mapping using Correlated Commit gets executed when the deployable code is run.

Setting a Starting Point

You can select an operator as a starting point, even if it is not a source. To set an operator as a starting point, start a debug session, then select the operator and click Set as Starting Point or choose the Set as Starting Point menu item.

When an operator is set as a starting point, Warehouse Builder combines all the upstream operators and sources into a single query, which is used as a source, and the operator is automatically used as the first source when stepping through the map. The operators that are upstream of the starting point operator are not steppable, and do not have displayable data, even if a watch point is set.

A good use of "set as starting point" would be if the map had three source tables that were all connected to a single joiner. Assuming that each source table contains a large number of rows, too many rows to efficiently step through in the debugger (say more than 50000 rows). In this case, it would be a good idea to set the joiner operator as a starting point, and limit the row count for the one of the source tables to a more manageable number of rows (say 500) by using the Test Data Editor. In this case it would be best to limit the row count of the source table that is effectively driving the joiner (that is, the source with which all the other sources are joined in the join condition).

Debugging Pluggable Submap Operators

You can also debug a map which contains one or more pluggable submap operators. This could include a user-defined pluggable submap operator from the pluggable folder, or a system-defined submap operator. When the debug session is started, the map will go through debug initialization and start stepping at the first executable operator, just as usual.

If during the course of stepping through the operator, the debugger reaches a pluggable operator, then that operator is highlighted as the current step operator just like any other operator. If you click Step at this point, then the debugger steps through all of the operators contained by the pluggable without changing the graphical context of the map to show the implementation of the pluggable map. If you click Step Into, then the graphical context of the map changes to the pluggable map implementation, and the current step operator is set to the first executable source operator inside the pluggable map. The first executable source operator for the pluggable is one of the operators connected from the input signature operator.

You can now step through the pluggable map just as you would any other type of map. When the pluggable operator contains targets, the debugger loads theses just as it does for a top-level map. When the final executable operator is done executing, then the next time you click Step, the context changes back to the top level map and begins execution at the next executable operator following the pluggable that was just executed. When the pluggable has no output connections, and it is the final executable operator in the top-level map, then stepping is done.

You can set breakpoints and watch points on operators inside of a pluggable submap. Additionally, during normal editing, you can change the graphical context as you do in normal editing, by clicking Visit Child Graph and Return to Parent Graph.

Re-Initializing a Debug Session

When you have made changes to the mapping, or have bound source or target operators to different database objects, then you must re-initialize the debug session to continue debugging the mapping with the new changes. To re-initialize, click the re-initialize button on the toolbar or select the re-initialize menu item in the debug menu. Re-initializing both regenerates and re-deploys the debug code. After re-initialization, the mapping debug session starts from the beginning.

Scalability

Scalability when debugging a mapping applies both to the amount of data that is passed as well as to the number of columns displayed in the Step Data panel. The Define Test Data dialog box provides a row limit that you can use to limit the amount of data that flows through the mapping. Also, you can define your own data set by creating your own table and manipulating the records manually.

To restrict the number of columns displayed on the step data window or on a watch tab you can use display sets. By default every operator has a display set ALL and a display set MAPPED to display only the mapped attributes. You can manually add display sets on sources by using the mapping editor directly. Select the use display set option under the right mouse button on an input or output group to select the display set.