6 Creating Experts

To create experts, you should have a working knowledge of Warehouse Builder, the Warehouse Builder scripting language, and Tool Command Language (Tcl), which is an open-source programming language.

How to Create Experts

Before you begin the process of developing an expert, you should compose a plan that answers the following questions

What job do you want the expert to accomplish?

For example, refresh the data in a schema.
What are the steps that you would take in Warehouse Builder to accomplish this job?

For example, identify the locations of the source data files and the target schema, execute a mapping or process flow, and so forth.
How flexible do you want the expert to be? That is, where can users provide input into the expert?

Experts can run without any user input at all, or they can prompt the user for input at every step. For example, an expert can refresh either a particular schema or the user's choice of a schema.

To define an expert, complete the following tasks:

Creating an Expert Object
Adding Tasks to the Expert Canvas
Adding Transitions to the Expert Canvas
Passing Data Values Among Tasks
Validating, Generating, and Starting Experts
Creating a Development Environment
Publishing Experts

Creating an Expert Object

Experts are organized in modules within a project.

Note:

You can develop public experts directly from the Globals Navigator. Simply follow the steps below in the Globals Navigator instead of in the Projects Navigator. Administrators can also publish experts, as described in "Publishing Experts".

To create an expert:

In the Projects Navigator, right-click Experts and select New Expert Module.

The Create Expert Module dialog box is displayed.
Enter a name and description for the module.
Select the Proceed to Wizard box.

The Create Expert dialog box is displayed.
Enter a name and description for the module.

The Expert Editor is displayed. You can begin developing the expert.

To edit an expert module:

In the Projects Navigator, expand the Experts folder, then expand the module for the expert.
Double-click the expert.

The Expert Editor is displayed.

Adding Tasks to the Expert Canvas

Tasks represent units of work for the expert. When you design an expert in Warehouse Builder, you select tasks from the component palette and drag them onto the canvas. The Start and the End tasks are already available on the canvas.

To add a task to an Expert:

Select a task from the palette and drag it onto the canvas.

Or

From the Expert menu, choose Add, then choose a category from the popup menu. Select a task from the category.

The editor displays the task on the canvas.
In the Task Editor, complete the tabs for the various properties of that task.

These properties vary according to the type of task. All tasks have the Goal, Instructions, Pre-processing, and Post-Processing properties. Some tasks such as the Custom Dialog and OMB tasks include additional properties.
Use the Explorer and the Object Inspector to enter values for the parameters of the task.

Tasks have input parameters and output parameters. Refer to "Passing Data Values Among Tasks" for methods of providing values to the input parameters.
To change the name of the task or add a description, right-click the icon on the palette and select Edit Details from the popup menu.

The Edit Task dialog box is displayed. Click Help for more information.

Adding Nested Experts to the Expert Canvas

You can create experts that perform relatively simple tasks, then use them as building blocks in developing more complex experts. When you add an expert to the canvas, it is called a nested expert. The nested expert functions as a single task, with one incoming transition and one or more outgoing transitions.

The existing flow becomes the parent graph, and the nested expert becomes a child graph on the canvas.

To add a nested expert:

From the Expert menu, select Add, then Nested Experts.

Or

Drag-and-drop experts from the Available Objects tab of the Explorer to the canvas.

The Add Nested Experts dialog box is displayed.
Expand the module folders, then select one or more experts from the list. Click OK.

The expert appears as a single icon on the canvas.
Draw transitions from a task to the expert, and from the expert to a task.

To view a nested expert:

The graphic toolbar provides icons for viewing the contents of a nested expert.

To enlarge the expert icon so that you can see the individual tasks, click Expand Child Graph on the toolbar. This view of the nested expert is similar to the Bird's Eye View of the canvas.

To shrink the nested expert back to an icon, click the arrow on the right side of its title bar.
To fully examine the tasks and transitions in the nested expert, click Visit Child Graph on the toolbar. The canvas displays only the nested expert, and its properties are shown in the Explorer, Object Details, and Task Editor windows. You have read-only access to a nested expert.

To shrink the nested icon back to a component in the larger expert, click Return to Parent Graph on the toolbar.

To edit a nested expert:

Open the nested expert in its own Expert Editor and make your changes. These changes are reflected in the parent expert as soon as you save the changes, with one exception: Changes to the expert parameters.

If you make changes to the parameters of the nested expert, you must delete and add the nested expert in the parent.

Adding Transitions to the Expert Canvas

Transitions indicate the sequence for executing the tasks in an expert. You can use conditional transitions to execute a task based on the completion state of the preceding task. By using conditional transitions, you can develop an expert that handles errors gracefully and provides alternative actions when users cancel a task.

A single task can have multiple outgoing transitions, but only one will be executed. If more than one transition evaluates to true, then the first one alone is executed.

To connect two tasks with a transition:

Add one or more tasks to the canvas, following the steps in "Adding Tasks to the Expert Canvas".
Click the Select tool on the toolbar.
Position the pointer over the previous task so that it has an arrow shape. Click and drag the pointer to the next task.

Notice that a plus sign appears under the pointer, then changes to a circle as the pointer approaches a next task.

The editor displays an arrow between the two tasks, assigns a default name to the transition, and displays the transition in the Explorer and in the Object Selector.
If you want execution of the next task to be conditional, then assign a condition to the transition in the Object Details window. Table 6-1 describes the conditions.
After you have connected all the tasks, click the Auto Layout tool to arrange the tasks in the order of execution and spaced evenly across the canvas.

Table 6-1 Types of Conditions for Transitions

Condition	Continuation to the Next Task
Success	Only if the preceding task ends in success.
Error	Only if the preceding task ends in error.
Cancel	Only if the user cancels the preceding task.
Complex Condition	Only if the conditions you specified are true.

Passing Data Values Among Tasks

Tasks have both input parameters and output parameters.

Input parameters affect the execution of the task. You can provide these values, or you can design the expert so that the user provides them.
Output parameters are returned by the task. These values are the result of the work performed by the task.

You can pass data values among tasks either by binding the parameters or by using constants and variables.

Binding Input Parameters

You can bind the input parameters of a task to the output parameter of another task, to a global constant, or to a global variable. Binding is the easiest method of passing values to input parameters.

To bind the parameters:

Create the source and the target tasks.
In the Expert Explorer, select an input parameter for the target task.
In the Object Details window, click the Binding From field and select an output parameter, a variable, or a constant from the popup menu.

Using Constants

You can use the following predefined constants in any task:

OMB_CURRENT_PROJECT: Stores the name of the current project.
OMB_CURRENT_SESSION: Stores the identifier of the current session.
OMB_CURRENT_USER: Stores the user ID of the current user.

These constants appear in the Explorer under the Constants folder.

Using Variables

You can use custom variables to store the values of output parameters. This is particularly useful when using Custom Dialog tasks, because the GUI_RETURN_VALUE output parameter is an array that functions like a hash table. It must be parsed before the individual values can be passed to input parameters. You may also want to use variables when passing a single value to multiple tasks, or when passing a value further down the flow.

To create a variable:

In the Explorer, select the Variables folder.
Click the Create icon at the top of the Explorer.

A variable named VARIABLE appears in the Variables folder.
Select VARIABLE and use the Object Details window to change its name, data type, and other parameters.

Or, you can use Tcl commands to declare variables in the Pre-processing or Post-processing tabs of the Task Editor.

To assign a value to a variable:

Use a Tcl variable assignment statement in the Pre-processing or Post-processing tabs of the Task Editor. The following are some examples:

This example assigns the value of the RETURN_VALUE output parameter to a variable named THIS_OBJECT_NAME.

set THIS_OBJECT_NAME $RETURN_VALUE;

The next example assigns the value of the first component (COMP1) of the GUI_RETURN_VALUE parameter to a variable named THIS_TABLE.

set THIS_TABLE $GUI_RETURN_VALUE(COMP1)

To use a variable:

You can bind an input parameter of a task to a variable. The variables appear in the popup menu in the Object Details window.

You can also use the variables in any Tcl commands on the Pre-processing and Post-processing tabs of any task, and on the Main tab of an OMB task.

Validating, Generating, and Starting Experts

You can validate, generate, and start an expert from the Expert Editor or from the Design Center.

To validate, generate, or start an expert from the Expert Editor:

From the Expert menu, choose the Validate, Generate, or Start command.

Click the Validate, Generate, or Start icon from the toolbar.

The Message tab of the Compilation Results window displays any validation errors and warnings. When you select a message, Warehouse Builder selects the errant task or transition on the canvas, the Explorer, and the Object Details window, so that you can correct the problem.

To validate, generate, or start an expert from the Design Center:

In the Projects Navigator, expand the Experts folder for the project, then expand the module folder.
Right-click the expert, then choose the Validate, Generate, or Start command from the popup menu.

or

Select the expert, then choose the Validate, Generate, or Start command from the Design menu.

Creating a Development Environment

Experts have several settings that affect their behavior while running. You will probably want to set them one way while you are developing an expert, and another way when you or other users are using the expert to accomplish real work.

To set the expert parameters for development:

In the Expert Explorer, select the expert at the very top of the tree.
In the Object Details window, select the following settings:
- Show Progress Graph
- Show Task Assistant
- Show Log Window
- Save All Before Start
- Logging

The two logging parameters (Show Log Window and Logging) display the scripting messages in the Task Assistant when you run the expert. These messages may help you diagnose any problems.

Publishing Experts

The settings that simplify testing and debugging experts are not appropriate when running the expert to accomplish work.

To set the expert parameters for use:

In the Expert Explorer, select the expert at the very top of the tree.
In the Object Details window, deselect the following settings:
- Show Log Window
- Save All Before Start
- Logging
Select the following settings:
- Close Windows on Execution
- Finish Dialog on Completion
- Run Standalone
- Close Assistant on Completion
- Revert to Saved on Error
Enter a name for the expert in the Menu Item Display String field.

To publish an expert:

If you have administrative privileges in Warehouse Builder, you can make experts available to other users in any of these places:

Globals Navigator: Copy and paste an expert from the Projects Navigator to the Global Explorer into a module in the Public Experts folder.
Projects Navigator: Right-click a folder and choose Add/Remove Experts Here.
Design Center Menu: On the Tools menu, choose Add/Remove Experts Here.
Windows Program Menu: Create a batch file that can be run from the Windows Program menu, as described in "Running an Expert From a Batch File".

Experts that appear in the Public Experts folder are available to all users for all projects. Users can scan the contents of the Public Experts folder to find experts relevant to their objectives in Warehouse Builder.

Alternatively, you can make experts more accessible to end users by customizing the menus in the Design Center to include public experts. For example, if you designed an expert for creating a common type of mapping, you can customize the right-click menu for the mapping folder to include this specialized expert.

Running an Expert From a Batch File

You can create a batch file that enables users to run an expert without opening the Warehouse Builder Design Center. Take these steps, which are explained in more detail in the following paragraphs:

Create a Tcl script that starts the expert.
Create a batch file that opens OMB*Plus and passes it the name of the Tcl file.
For Windows platforms, create a shortcut to the batch file on the Start menu or on the desktop.

To create a Tcl script:

Use a text editor to create a script containing the following Warehouse Builder scripting commands:

OMBCONN: Connects a user to a Warehouse Builder repository.
OMBCC: Sets the context to the location of the expert.
OMUSTART EXPERT: Starts the expert.
OMBDISCONNECT: Closes the session.

Example 6-1 connects a user named SCOTT to the GCCREP repository on a host named SCOTT-PC. The expert is named REFRESH_DATA_EXPERT, and is located in SALES_PROJECT in EXPERT_SALES_MODULE.

For detailed information about these commands, refer to the Oracle Warehouse Builder API and Scripting Reference.

Example 6-1 Sample Tcl Script for Starting an Expert

OMBCONN scott/tiger@scott-pc:1521:orcl USE REPOS 'GCCREP'
OMBCC '/SALES_PROJECT/EXPERT_SALES_MODULE'
OMUSTART EXPERT 'REFRESH_DATA_EXPERT'
OMBDISCONNECT

To create a batch file:

Use a text editor to create a batch file that opens OMB*Plus and passes it the Tcl file. Be sure to specify the full path names, as shown in the following example:

c:\oracle\product\BiToolsHome_1\owb\bin\win32\OMBPlus.bat c:\owb_scripts\Update_Sales_Data.tcl

To create a shortcut:

Search Windows Help for instructions for adding a program to the Start menu. Create a shortcut to the batch file.