7 Creating Mappings

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.

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

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

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.

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
	Constant operator	Produces a single output group that can contain one or more constant attributes.
	Construct Object operator	Produces object types and collection types.
	Cube operator	Represents a cube that you previously defined.
	Data Generator operator	Provides information such as record number, system date, and sequence values.
	Dimension operator	Represents a dimension that you previously defined.
	Expand Object operator	Expands an object type to obtain the individual attributes that comprise the object type.
	External Table operator	Represents an external table that you previously defined or imported.
	Flat File operator	Represents a flat file that you previously defined or imported.
	Materialized View operator	Represents a materialized view that you previously defined.
	Sequence operator	Generates sequential numbers that increment for each row.
	Table operator	Represents a table that you previously defined or imported.
	Varray Iterator operator	Iterates through the values in the table type.
	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
	Aggregator operator	Performs data aggregations, such as SUM and AVG, and provides an output row set with aggregated data.
	Anydata Cast operator	Converts an object of type Sys.AnyData to either a primary type or to a user-defined type.
	Deduplicator operator	Removes duplicate data in a source by placing a DISTINCT clause in the select code represented by the mapping.
	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.
	Filter operator	Conditionally filters out rows from a row set.
	Joiner operator	Joins multiple row sets from different sources with different cardinalities and produces a single output row set.
	Key Lookup operator	Performs a lookup of data from a lookup object such as a table, view, cube, or dimension.
	Match Merge operator	Data quality operator that identifies matching records and merges them into a single record.
	Name and Address operator	Identifies and corrects errors and inconsistencies in name and address source data.
	Pivot operator	Transforms a single row of attributes into multiple rows. Use this operator to transform data that contained across attributes instead of rows.
	Set Operation operator	Performs union, union all, intersect, and minus operations in a mapping.
	Sorter operator	Sorts attributes in ascending or descending order.
	Splitter operator	Splits a single input row set into several output row sets using a boolean split condition.
	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.
	Transformation operator	Transforms the attribute value data of rows within a row set using a PL/SQL function or procedure.
	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
	Mapping Input Parameter operator	Passes parameter values into a mapping.
	Mapping Output Parameter operator	Sends values out of a mapping.
	Post-Mapping Process operator	Calls a function or procedure after executing a mapping.
	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
	Pluggable Mapping operator	Represents a reusable mapping.
	Pluggable Mapping Input Signature operator	A combination of input attributes that flow into the pluggable mapping.
	Pluggable Mapping Output Signature operator	A combination of output attributes that flow out of the pluggable mapping.

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

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

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.

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

   
   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".

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:

• Copy Source Attributes to Target Group and Match

• Match by Position of Source and Target Attributes

• Match by Name of Source and Target 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

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.

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.

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:

• Using Pluggable Mappings

• About the Mapping Editor

• Adding Operators

• Editing Operators

• Connecting Operators

• Setting Operator, Group, and Attribute Properties

• Synchronizing Operators and Workspace Objects

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

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

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

Description of "Figure 7-9 Advanced Synchronizing Options "

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.

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.

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.

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.

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.

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.

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.

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.

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.