In the Project Explorer panel of the Design Center, expand OWB_DEMO. Expand Databases > Oracle > SALES_WH > Mappings. Double-click LOAD_CHANNELS.

The Mapping Editor launches the LOAD_CHANNELS mapping.

You design a mapping in Warehouse Builder using the Mapping Editor interface. As you design a mapping, you select operators from the Mapping Editor Palette panel and place them on the canvas.

Tip: If you are not able to see all the objects similar to the following screenshot, then in the Mapping Editor, click Auto-Layout on the toolbar to bring the mapping to its default size. Maximize the operators on the canvas by clicking the maximize icon .

In the Mapping Editor, you see the following operators on the canvas:

a) A Table operator named CHANNELS_IN

b) A Constant operator named TOTALS

c) A Dimension operator named CHANNELS_OUT

d) Connection lines between attributes

In the LOAD_CHANNELS mapping, click the table operator CHANNELS_IN header. This now becomes the currently selected object. Right-click the table operator CHANNELS_IN header and select Open Details. This launches the Operator Editor in tab format.

Each tab helps in performing tasks associated with the operator. For example, the Name tab enables you to specify a name and optional description for the operator.

Click Cancel to close Table Editor.

In the LOAD_CHANNELS mapping, ensure that the CHANNELS_IN table operator is selected. (Click the table operator CHANNELS_IN header.) To determine the operator's bound name, scroll down to the Bound Name properties in the Table Operator Properties panel. This operator is bound to the CHANNELS table in the XSALES schema.

Tip: Alternatively, move your mouse over the header of the operator. If the operator is currently bound, you will see the complete details in the following format: Operator <operator name>: bound to /<project name>/module name/operator bound name (for example, Operator CHANNELS_IN: bound to /OWB_DEMO/XSALES/CHANNELS).

Note: Bound name is the physical name of the object that is connected to an object operator in a mapping. Bound names are used to reference the object during code generation.

In the LOAD_CHANNELS mapping, the dimension operator is named CHANNELS_OUT. The operator is bound to the CHANNELS dimension in the SALES_WH target schema.

In the LOAD_CHANNELS mapping, the constant operator is named TOTALS. The Constant operator enables you to define constant values. Constants can be used anywhere in a mapping. The Constant operator produces a single output group that can contain one or more constant attributes. To add a Constant operator on the canvas, you should drag a constant operator from the Palette.

Tip: An example of using a constant operator is to load the value of the current system date into a table operator.

To add the attributes to the TOTALS constant object, right-click the constant operator TOTALS header and select Open Details. Click the Output Attributes tab. Clicking Add will add attributes to the Totals constant object. As you see in the screenshot two attributes have been added.

Click Cancel to close the Constant editor.

In the LOAD_CHANNELS mapping, select the TOT attribute by selecting the TOT attribute in the TOTALS constant operator. On the left in the Mapping Editor, in the Attribute Properties panel, inspect the Expression property. In the Attribute Properties panel, click the field next to the Expression property. Click the button on the right. The Expression Builder is launched. The value of the TOT attribute is "Channels Total." Close the Expression Editor.

In the LOAD_CHANNELS mapping, you see connection lines from:

• Attributes in the table operator CHANNELS_IN to attributes in the dimension operator CHANNELS_OUT
• Attributes in the constant operator TOTALS to attributes in the dimension operator CHANNELS_OUT

In this example, you connect operators by connecting individual operator attributes to each other. Connection lines graphically represent how the data flows from a source, through operators, and to a target.

Close the Mapping Editor.

In the Project Explorer, expand OWB_DEMO. Expand Databases > Oracle > EXPENSE_WH > Mappings. Note that you see a REL_TIME_MAP mapping, which was automatically created by OWB when you created the time dimension REL_TIME using the Time Wizard.

Right-click Mappings and select New.

The Create Mapping window appears.

In the Create Mapping window, enter REL_CATEGORY_MAP as name of the mapping. Click OK.

In the Project Explorer panel, you see an entry for the REL_CATEGORY_MAP mapping under the EXPENSE_WH module. The Mapping Editor launches the REL_CATEGORY_MAP mapping.

From the Palette, drag the Dimension operator to the canvas. The Add Dimension Operator window appears.

In the Add Dimension Operator window, navigate to the EXPENSE_WH module and select the REL_CATEGORY dimension. Click OK.

The REL_CATEGORY dimension object is dropped on the canvas.

Alternatively, you can add existing sources and targets to a mapping through the Explorer panel, which is on the left side of the Mapping Editor. In the Explorer panel, ensure that Available objects tab is selected. Expand Oracle > EXPENSE_WH > External Tables. Drag EXPENSE_CATEGORIES to the canvas.

Now there are two objects on the canvas, the EXPENSE_CATEGORIES external table and the REL_CATEGORY dimension.

Tip: In the Mapping Editor, click Auto-Layout on the toolbar to change the mapping to its default size. By dragging, move the EXPENSE_CATEGORIES external table to the left of the REL_CATEGORY dimension. Maximize the EXPENSE_CATEGORIES external table by clicking the maximize icon . Similarly, maximize the REL_CATEGORY dimension.

Connect the source (EXPENSE_CATEGORIES external table) to the target (REL_CATEGORY dimension) as follows:

• Drag a connection line from EXPENSE_CATEGORIES.OUTGRP1.EXPENSE_TYPE to REL_CATEGORY.TYPE.NAME

• Drag a connection line from EXPENSE_CATEGORIES.OUTGRP1.EXPENSE_TYPE to REL_CATEGORY.TYPE.DESCRIPTION

• Drag a connection line from EXPENSE_CATEGORIES.OUTGRP1.EXPENSE_CATEGORY to REL_CATEGORY.CATEGORY.NAME

• Drag a connection line from EXPENSE_CATEGORIES.OUTGRP1.EXPENSE_CATEGORY to REL_CATEGORY.CATEGORY.DESCRIPTION

• Drag a connection line from EXPENSE_CATEGORIES.OUTGRP1.EXPENSE_CATEGORY to REL_CATEGORY.TYPE.CATEGORY_NAME.

Tip: Connection lines graphically represent how the data flows from a source, through operators, and to a target.

You have just completed designing the mapping. Now get ready to generate the code for the mapping. In the Mapping Editor, from the menu, select Mapping > Generate. You have now generated the code that loads the dimension. Based on the ETL logic that you design in a mapping, Warehouse Builder generates the code required to implement your design.

The Generation Results window is displayed. The Generation Results window displays the code that has been generated for you.

Close the Mapping Editor.

In the Project Explorer, expand OWB_DEMO. Expand Databases > Oracle > EXPENSE_WH > Mappings. Right-click the Mappings node and select New.

The Create Mapping window appears.

In the Create Mapping window, enter REL_EXPENSE_MAP as the name of the mapping. Click OK.

In the Project Explorer you see an entry for the REL_EXPENSE_MAP mapping under the EXPENSE_WH module. The Mapping Editor is launched.

In the Explorer panel, on the left of the Mapping Editor, ensure that the Available objects tab is selected. Expand Oracle > EXPENSE_WH > External Tables. Drag the EXPENSE_DATA external table to the canvas.

The EXPENSE_DATA external table object is on the canvas.

In the Explorer panel, ensure that the Available objects tab is selected. Expand Oracle > EXPENSE_WH > Cubes. Drag the REL_EXPENSE cube to the canvas.

Now, there are two objects on the canvas, the EXPENSE_DATA external table operator and the REL_EXPENSE cube operator.

Tip: In the Mapping Editor, click Auto-Layout on the toolbar to bring the mapping to its default size.

Ensure that the EXPENSE_DATA external table operator is to the left of the REL_EXPENSE cube operator. Maximize the EXPENSE_DATA external table by clicking the maximize icon. In the same way, maximize the REL_EXPENSE cube operator.

In the Mapping Editor, from the Palette panel, drag the Expression operator to the canvas.

In this example, the Expression operator is used to transform the EXPENSE_DATE attribute in the EXPENSE_DATA external table from a date data type to a number data type. The target attribute in the cube is a number data type, therefore, this data type conversion is required to match the data type.

Drag a connection line from EXPENSE_DATA.OUTGRP1.EXPENSE_DATE to EXPRESSION.INGRP1.

Right-click the expression operator EXPRESSION header and select Open Details.

In the Expression Editor, click the Output Attributes tab. Click Add.

Rename the OUTPUT1 attribute to EXPENSE_DATE. Ensure that the data type is NUMBER. Click OK.

In the Mapping Editor, click the expression operator EXPRESSION header, and select OUTGRP1.EXPENSE_DATE. In the Attribute Properties panel, click the field next to the Expression property. Click the button on the right.

The Expression Builder is launched.

In the Expression Builder, enter the following statement in Expression for EXPRESSION:

TO_NUMBER(TO_CHAR(INGRP1.EXPENSE_DATE , 'YYYYMMDD'), '99999999')

Click OK.

Connect the source operator to the target operator as follows:

• Drag a connection line from EXPRESSION.OUTGRP1.EXPENSE_DATE to REL_EXPENSE.REL_EXPENSE.REL_TIME_DAY_CODE

• Drag a connection line from EXPENSE_DATA.OUTGRP1.EXPENSE_TYPE to REL_EXPENSE.REL_EXPENSE.REL_CATEGORY_NAME

• Drag a connection line from EXPENSE_DATA.OUTGRP1.REIMBURSABLE_AMOUNT to REL_EXPENSE.REL_EXPENSE.EXPENSE

You have just completed designing the mapping. Now you can generate the code for the mapping.

In the Mapping Editor, from the menu, select Mapping > Generate. Warehouse Builder generates the
code required to implement your design. The Generation Results window appears. The Generation Results window displays the code that has been generated for you.

Tip: To view these generated results at a later time, select Generation Results from the Window menu.

From the toolbar, click Save All to commit your work. Close the Mapping Editor.

If not already logged in, log in to the Design Center with username/password as rep_owner/rep_owner.

In the Global Explorer panel, expand the Security node. Right-click the Users node and select New.

Note: You must save or revert your changes before creating a new user. To save previous changes, select Save All from the Design menu or click on the toolbar.

The Register Warehouse Builder Users Wizard is launched. Click Next on the Welcome page.

On the Select DB user to register page, you can select from the available list of database users or create a new one that will be automatically registered as Warehouse Builder target user.

Select owf_mgr from the Available DB Users list and click > to move it to Selected Users list.

On the Check user as target schema page, de-select the Used as target schema option. You need not make OWF user, a target user. Click Next.

On the Summary page, examine the details and click Finish. The Fix Database Default Role for Users dialog box diaplays.

In the Fix Database Default Role for Users dialog box, enter sys or <your SYS password> in SYSDBA Password field in the Fix Now section.

Click OK. Notice that owf_mgr user gets added inside the Security > Users node, in the Global Explorer panel.

Before you can start designing the process flow, you need to create a process flow module that includes a process flow package.

Note: Process Flow Modules include Process Flow Packages that include Process Flows. The Process Flow Package is a grouping mechanism that determines which process flows you can interrelate. At run time, you can launch one process flow that launches other Process Flows that exist in the same process flow package.

You create a Process Flow Module named MY_PF_MODULE, Process Flow Package named PK, and a Process Flow MY_PROC. The Process Flow Module acts as a container by which you can validate, generate, and deploy a group of Process Flows.

In the Project Explorer, expand the OWB_DEMO project, and then expand the Process Flows node. Right-click Process Flow Modules and select New from the menu.

The Create Module Wizard is launched. Click Next on the Welcome page.

On the Name and Description page, enter MY_PF_MODULE as the name of the process flow module. Click Next.

Now you create an Oracle Workflow location for the process flow module, MY_PF_MODULE.

Note: You define an Oracle Workflow location to specify where you want to deploy your process flows. The Oracle Workflow location points to a workflow schema (OWF_MGR) that runs in the target database. Warehouse Builder process flows comply with the XML Process Definition Language (XPDL). When you generate a process flow, Warehouse Builder generates an XML file in the XPDL format. You can plug the generated XML file into any workflow engine that follows the XPDL standard.

On the Connection Information page, observe that Warehouse Builder creates a location with a default name, MY_PF_MODULE_LOCATION1. Click Edit to specify the details of the Workflow location.

In the Edit Oracle Workflow Location window, change the name of the location to WH_OWF_LOCATION. Specify the connection details to the Oracle Workflow schema. This course uses owf_mgr as the schema name and owf_mgr as the password. If your Oracle Workflow schema has a different name and password, substitute accordingly.

Password: owf_mgr
Host: localhost
Port: 1521
Service: orcl
Schema: owf_mgr
Version: 2.6.3

Note: If you are using Oracle Workflow 2.6.4, select the appropriate version.

Click Test Connection. If the connection is successful, click OK. If the connection is not successful, check the connection details and try again.

Click OK. Click Next. On the Summary page, review the details and click Finish.

In the Create Process Flow Package window, specify PK as the name and click OK.

The Create Process Flow window is launched. Specify LOAD_EXPENSE_WH as the name of the process flow.

Warehouse Builder launches the Process Editor and displays the process flow with a Start activity and an End_Success activity. You now learn how to design the process flow with activities and transitions.

In the Process Editor, from the Palette panel, drag the Fork activity to the canvas. You can use the Fork activity to launch multiple, concurrent activities after the completion of an activity. You can also drag the Fork activity from the Selected Objects tab in the Explorer panel.

In the Process Editor, in the Explorer panel, click the Available Objects tab. From the EXPENSE_WH target module, drag REL_CATEGORY_MAP mapping to the canvas.

In the Explorer panel, ensure that the Available Objects tab is selected. Drag the REL_TIME_MAP mapping to the canvas.

On the canvas, you see two mapping activities, REL_TIME_MAP and REL_CATEGORY_MAP. Rearrange the mapping activities vertically just to the right of the START activity.

Note: On dropping the mapping activities on the canvas, if you get "Unable to determine if the map is a PLSQL or SQLLDR map........" error, then in the Design Center, right-click the appropriate mapping and select Configure. In the Configuration Properties window, for Language property, select PL/SQL.

From the Palette panel, drag an AND activity to the canvas, to the right of the REL_TIME_MAP and REL_CATEGORY_MAP mapping activity.

In the Explorer panel, ensure that the Available Objects tab is selected. Drag the REL_EXPENSE_MAP mapping activity to the canvas.

Rearrange the REL_EXPENSE_MAP to the right of the AND Activity.

From the Palette panel, drag an END with ERRORS activity to the canvas. Drag it to the right of the REL_EXPENSE_MAP cube mapping and above the END_SUCCESS activity.

From the Palette panel, drag an END with WARNINGS activity to the canvas. Drag it to the right of the REL_EXPENSE_MAP mapping activity and below the END_SUCCESS activity. The process appears as shown in the screenshot.

Now you learn how to connect the activities.

Position your cursor on the Start activity. Ensure the cursor changes to ( ). Drag a line (these lines will from here on be referred to as transitions) from Start activity to the Fork activity.

Note: Use transitions to indicate the sequence and conditions in which activities occur in the process flow. You can use transitions to execute an activity based on the completion state of the preceding activity.

To drag a transition, the mouse pointer must change to ().

• Drag a transition from FORK activity to the mapping activity REL_CATEGORY_MAP.

• Drag a transition from FORK activity to the mapping activity REL_TIME_MAP.

• Drag a transition from mapping activity REL_CATEGORY_MAP to the AND1 activity.

• Drag a transition from mapping activity REL_TIME_MAP to the AND1 activity.

• Drag a transition from AND1 activity to the mapping activity REL_EXPENSE_MAP.

• Drag a transition from mapping activity REL_EXPENSE_MAP to END_ERROR activity.

• Drag a transition from mapping activity REL_EXPENSE_MAP to END_SUCCESS activity.

• Drag a transition from mapping activity REL_EXPENSE_MAP to END_WARNING activity.

After having linked all the activities with transitions, the process flow will look as shown in the screenshot.

Transitions that you learned and added in the above steps are unconditional transitions. By default, transitions are unconditional. The process flow continues after the preceding activity completes, regardless of the ending state of the previous activity.

Note: You may get some invalid characters, such as <0> and <1>, as you drag transitions from one activity to another. You can ignore them as it does not cause any issues during deployment or execution.

You can use transitions to execute an activity based on the completion state of the preceding activity. Now, you learn to apply conditions to transitions.

Select the transition from the REL_EXPENSE_MAP mapping activity to the END_SUCCESS activity. In the Object Details panel, the transition is named Transition_6 as seen in the screenshot. The transition name you see in your working environment can be different from what you see in the screenshot. The name difference does not matter.

Ensure that the transition (the line) between the REL_EXPENSE_MAP mapping activity and END_SUCCESS is selected.

In the Object Details panel, click in the field next to the Condition property. Click the button on the right. The Transition Condition Editor is displayed. In the Transition Condition Editor, select the Enumerated Condition option button. From the drop-down list, select SUCCESS. Click OK.

Select the transition between the REL_EXPENSE_MAP mapping activity to the END_ERROR activity. The transition named Transition_6 is highlighted.

In the Object Details panel, click the field next to property Condition. Click the button. The Transition Condition Editor is displayed. In the Transition Condition Editor, select the Enumerated Condition option button. From the drop-down list, select ERROR. Click OK.

This indicates that the process flow continues if the preceding activity (REL_EXPENSE_MAP) ends in an error.

Select the transition between the REL_EXPENSE_MAP mapping activity to the END_WARNING activity.

In the Object Details panel, click in the field next to the Condition property. Click the button. In the Transition Condition Editor, select the Enumerated Condition option button. From the drop-down list, select WARNING. Click OK.

This indicates that the process flow continues if the preceding activity (REL_EXPENSE_MAP) ends with warnings.

This completes the design of this process flow. Generate the LOAD_EXPENSE_WH process flow by selecting Generate from the Process Flow menu. The code generated from a process flow definition is in industry standard XML Process Definition Language (XPDL).

From Process Flow menu, select Close to exit the process editor.

In the Design Center, click Save All on the toolbar to commit your work.

These steps are read-only, not to be performed.

Assumptions: The REL_CATEGORY dimension has been deployed to the target schema. The EXPENSE_CATEGORIES external table is not deployed. You will debug the REL_CATEGORY_MAP mapping.

Expand OWB_DEMO, expand Databases, expand EXPENSE_WH, and double-click REL_CATEGORY_MAP.

The REL_CATEGORY_MAP mapping opens in the Mapping Editor. Maximize the window.

Warehouse Builder provides debugging capabilities for data flows within the Mapping Editor. Debugging functions are available on the toolbar and the Debug menu.

Start the debug session by clicking Debug Start ( ) on the toolbar.

The Mapping Editor switches to debug mode with the debug panels appearing at the bottom of the editor.

The debugger tries 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 and gives the following error message:

Every source or target operator in the mapping is listed on the Test Data tab in the Debug Info panel. It contains the object name and a check mark that indicates that the database object has already been bound to the source or target operator. Before you can run the mapping in debug mode, each listed source or target operator must be bound and have a check mark. Typically, you need test data for all source operators.

Clicking the Edit button launches the Bind Debug Test Data page, which enables you to add or change the binding of an operator, as well as the test data in the bound database objects.

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 the Re-initialize button on the toolbar.

In the Messages tab, in the Debug Info panel, the Successful Debug initialization indicates that test data is available for all operators.

If you are interested in how a specific operator is processing data, you can set a breakpoint on that operator. Set breakpoint from the debug toolbar. This will cause a break in the debug session.

Notice that the Breakpoints tab appears in the Debug Info panel in the lower-left corner of the Mapping Editor. Breakpoints can be set during the mapping debug run and can be activated and deactivated as the mapping runs.

Using the Step icon on the toolbar, to step through the mapping and watch data flow into the selected object.

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 Debug Data panel at the bottom right of the Mapping Editor.

Design Mappings

Design Process Flows that deploys and executes the data loads together