Oracle Warehouse Builder, version 9.2 and higher, features
a graphical mapping debugger that enables you to see the data flow through the
mapping in a graphical interface. The mapping debugger is embedded in the Mapping
Editor.
This lesson walks you through a debugging session for an existing
mapping. In daily practice, you will use the debugger more often in order to
debug a new mapping.
This lesson will take approximately 60 minutes to complete.
Viewing Screenshots
Move your mouse over this icon to show
all screenshots. You can also move your mouse over each individual icon to see
only the screenshot associated with it.
Overview
This lesson will teach you how to debug the MAP_CUSTOMER
mapping using the debugger provided in the Mapping Editor.
The definition of test data involves three steps:
identifying source tables, creating a test target table, and generating and
deploying the test package.
This debugging session requires that you first deploy two target objects:
CUSTOMER dimension and UNASSIGNED_CUSTOMERS table.
In the Runtime Repository Connections node, open OBE_RUNTIME_CONNECTION,
using connect_user as the password.
Expand TARGET_LOC location and WH module. Expand Dimensions
and select CUSTOMER. Expand Tables, hold down the Ctrl key
and also select UNASSIGNED_CUSTOMERS. Click the Default Action
key to change the deploy status to Create.
Generate and deploy these two objects.
This debugging session also requires that you first deploy a source object:
CUSTOMERS_VIEW_STG view table. In the TARGET_LOC location,
expand the STG module, expand Views, and select CUSTOMERS_VIEW_STG.
Click the Default Action key to change the deploy status to Create.
Generate and deploy this object.
Close Deployment Manager.
2.
Expand OWB10g_DEMO, expand Databases, expand WH and double-click
MAP_CUSTOMER to open the mapping editor. Maximize the window.
3.
Click the Debug Start button
in the toolbar. Specify connection details to the target schema.
Schema Name: target_schema
Password: target_schema
Hostname: localhost
Port Number: 1521
Service Name obeowb
Click OK.
4.
The debugger will try to locate objects in the target schema based on
the names of the operators. When you run the mapping in debug mode for
the first time Warehouse Builder will not be able to identify links to
all source tables.
Click OK.
5.
The mapping editor reappears with debugging panels in the lower left
and lower right corners. The Test Data tab in the lower left panel
indicates that, for REGIONS and COUNTRIES, the source tables
need to be found. For CUSTOMERS_VIEW_STG, a new debugging table
needs to be created.
REGIONS is highlighted. Click Edit in the lower left panel.
6.
A warning appears, indicating that no source object can be found for
REGIONS.
Click OK.
7.
The Define Test Data window appears. Click the Browse button.
8.
The Choose Entity window appears. Select the HR schema. Warehouse
Builder automatically selects the REGIONS table.
Click OK.
9.
The Define Test Data window reappears, showing test data for the REGIONS
table that you chose from the HR schema.
Click OK.
10.
The mapping editor reappears. Notice that the Test Data tab now
shows a checkmark next to REGIONS, indicating that data has been
found for that test table.
In the Test Data tab, select the COUNTRIES table, click
Edit, and perform the same steps that you performed for REGIONS
in steps 6 through 9.
( As you did in step 8, select the HR schema, and Warehouse Builder
will automatically select the COUNTRIES table.)
11.
When you finish finding data for the COUNTRIES
test table, return to the Test Data tab, select CUSTOMERS_VIEW_STG,
and click Edit to create a new debugging table for CUSTOMERS_VIEW_STG.
12.
The Define Test Data window appears. Instead of browsing for test data
as you just did for REGIONS and COUNTRIES, click the
Create New Table button. This will create a copy of the real view
table, allowing yout to modify the data for debugging purposes.
The table will be named DBG_<operator name>, thereafter, suffixed
with a sequence number if the object name would clash with an existing
object
.
13.
To modify some of the test data, select the User Data Edit Mode
check box in the upper left corner in the Test Data window. A warning
appears about manipulating data.
Click OK.
14.
The Define Test Data window reappears. Change
some of the CUST_FIRST_NAME values. Click Commit. Click OK.
An OWB Note appears, Commit data edits, Click Yes
The mapping editor reappears. Generate the debug package by clicking
the Reinitalize button in the toolbar.
2.
An OWB Note pops up, confirming that the definitions are the ones that
you want to use.
Click OK.
3.
Correlated commit is a feature that was added to the OWB
9.2 release. The feature helps you to manage a multi-target situation
in which commits would happen based on row selects; not based on
a target-by-target selection.
The debugger takes this setting into account and warns that all targets
will have to be processed at the same time.
Click OK.
4.
The mapping editor reappears. The Messages tab in the lower left
panel indicates successful debug initialization.
Now that you have set a watch point,
you can step through the mapping and watch data flow into the selected object.
1.
Click the Step button several times to retrieve rows for the debug
run.
Observe the rows of data in the Step Data tab
in the panel in the bottom right of the mapping editor.
2.
Click the Skip button to run all remaining rows for the operator.
.
Click Step again.
3.
The Map Path Choice dialog pops up. It allows you to choose the
next group (path) in the mapping whose data you wish to step through.
Select EXPR.OUTGRP1 and click OK.
4.
Click Step and see the rows appearing in the OUTGRP1 panel.
5.
Click the Resume button to resume execution, either until a breakpoint
is hit or until the mapping finishes.
In this case, a breakpoint was hit at the CUSTOMER dimension.
The Messages tab on the bottom left panel indicates this.
6.
Click Step a few times and notice that the records get the INSERT
action; some records might get the UPDATE action, depending on
what you did in previous lessons.
7.
Run to the end of the mapping path by clicking the Resume button.
8.
Click the Step button again. An OWB Note pops up, indicating
that the map execution is done.
Remember that Warehouse Builder warned about the Correlated commit
set to true. If Correlated commit had been set to false, you would have
been able to debug another path. However, you can still look at what happened
in the other path. Click OK.
9.
Select the FLTR operator and click Set Watch Point.
Notice that the filter is allowing only those records with an empty ACCOUNT_MGR_ID
to pass.
10.
Quit the debug session by clicking the Debug End button .
Close the mapping editor .
Oracle Warehouse Builder provides a powerful graphical debugger.
By running mappings in debug mode the user can get insight into the behavior
at runtime and potential problems can be pro-actively resolved. Building and
verifying a mapping is now easier than ever!
In this module you should have learned how to:
Define test data for sources and targets.
Set break points and watch points.
Step through a mapping with test data.
Move your mouse over this icon to hide all screenshots
Bookmark
in the toolbar. Specify connection details to the target schema.
.
Close the mapping editor .
