| Oracle® Warehouse Builder API and Scripting Reference 11g Release 1 (11.1) Part Number B31279-01 |
|
|
View PDF |
| Oracle® Warehouse Builder API and Scripting Reference 11g Release 1 (11.1) Part Number B31279-01 |
|
|
View PDF |
If you are a knowledgeable Warehouse Builder user, you can design solutions that simplify routine tasks and implement best practices. You can develop these solutions, called experts, in the Expert Editor.
To develop 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.
See Also:
Oracle Warehouse Builder Scripting Reference for information about the scripting language.
The Tcl Developer Xchange web site at http://www.tcl.tk for information about Tcl.
This chapter contains the following topics:
Experts are solutions that advanced users develop to automate routine or complex tasks using best practices.
For example, a common activity is extracting data from a flat file and loading that data into a table in Oracle Database. To accomplish this activity, users might take the following steps, in which they navigate a variety of user interfaces in Warehouse Builder:
Define a flat file module.
Identify the source file.
Specify the data format.
Define an external table.
Define an Oracle database module and location.
Define a mapping.
Validate, generate, and deploy all objects.
Execute the mapping.
To help users with this activity, you could design an expert that calls all the necessary user interfaces, provides customized instructions, and prompts users for input. In an expert, the steps are defined by tasks and the order of execution is defined by transitions.
Experts are reusable, shareable, and can access all areas of Warehouse Builder including user interfaces and the OMB Plus scripting language. Experts can also call Java programs.
Figure 4-1 shows how an expert might look to a user. Three separate windows are displayed:
Progress Graph: Displays the expert in the same format as it appears on the editing canvas, but the executed transitions and the current task are highlighted. This window serves the same purpose as bread-crumbs, tracking the user's progress through the expert. It can also be a helpful debugging tool. You can set a property on the expert that controls whether the progress graph is displayed or hidden.
Task Assistant: Displays the name of the current task, its goal, and instructions for using it. You provide this information on the property sheets for the task as part of its definition. You can set a property on the expert that controls whether the task assistant is displayed or hidden.
Task Display: Tasks that obtain information from the user display various types of graphical user interfaces. Among these tasks are those that display Warehouse Builder components, such as the Object Editor, and those that provide basic functions, like the file selector shown in the figure. In this example, the user selects a file, and the filename is passed as an input parameter to the next task. You can also store the value in a variable to use later.
The Expert Editor provides the canvas and the palette that you need to create, modify, and deploy experts.
Figure 4-2 shows the Expert Editor with a sample expert. Tasks appear as icons on the canvas. The arrows connecting the tasks are transitions. Transitions identify the order in which the tasks are executed.
The Expert Editor is very similar to the Process Flow Editor. If you have created process flows, you will be able to adapt your knowledge very quickly to developing experts. However, there are important differences as well as important similarities.
Unit of Work: In a process flow, the unit of work is an activity. In an expert, the basic unit of work is a task.
Transitions: Both process flows and experts use transitions to connect the basic units of work. Transitions can be conditional.
Variables: Both process flows and experts enable you to define local variables to pass values from one task to another.
End tasks: Process flows have success, warning, and failure end activities, but experts have a single End task.
Subprocesses: You can design a process flow to open other process flows, and you can design an expert to open other experts. In this use, they are called nested experts.
Code: A process flow generates XML that conforms to the XPDL workflow standard. An expert generates Tcl.
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:
Experts are organized in modules within a project.
Note:
Administrators can develop experts directly in the Global Explorer. Simply follow the steps below in the Global Explorer instead of in the Project Explorer. Administrators can also publish experts, as described in "Publishing Experts".To create an expert:
In the Project Explorer, right-click Experts and select New from the popup menu.
The Create Expert Module dialog box is displayed.
Enter a name and description for the module.
Click Help for additional information.
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 Project Explorer, expand the Experts folder, then expand the module for the expert.
Double-click the expert.
The Expert Editor is displayed.
Tasks represent units of work for the expert. When you design an expert in Warehouse Builder, you select tasks from the Expert Editor palette and drag them onto the canvas. The Expert Editor initially has the Start and the End tasks already positioned 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.
For information about these properties, click Help from the popup menu on the Task Editor title bar.
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.
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.
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 only the first one 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 4-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 4-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. |
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.
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.
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.
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.
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.
or
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 Project Explorer, 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.
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.
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:
Global Explorer: Copy and paste an expert from the Project Explorer to the Global Explorer into a module in the Public Experts folder.
Project Explorer: 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.
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 4-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 Scripting Reference.
Example 4-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.
