OWB - Expert

> Data Integration Tool (ETL/ELT) > Oracle Warehouse Builder

1 - About

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.

Advertising

3 - Snippet

Tcl scripting is case and space sensitive. This piece of code does not compile successfully or execution could fail because of space issues

3.1 - Example Code in Post Processing Tab from an expert

if {$GUI_RETURN_VALUE(DLG1) == "Cancel"} {
set ERROR_TO_DISPLAY "Hit Cancel within Handler"
} elseif {$GUI_RETURN_VALUE(DLG1) == "OK"} {
set ERROR_TO_DISPLAY "Hit OK within Handler"
}

The DLG1 parameter is the name of the dialog Builder.

3.2 - Executing an Expert Outside Oracle Warehouse Builder

In notepad or any text editor type the following (for instance c:/temp/startdialog.tcl):

set connectstring [lindex $argv 0]
OMBCONNECT $connectstring
OMBCC '/HANDSON/EXPERT_MODULE'
OMUSTART EXPERT 'NESTED_EXPERT

In notepad (any text editor) type the following (for instance c\temp\runexpert.bat):

c:
cd <owb home>\owb\bin\win32\
call OMBPlus.bat c:/temp/startdialog.tcl <un/pw>@<host:port:servicename>

call OMBPlus.bat c:/temp/startdialog.tcl……………..– On both, windows and linux/unix platforms forward slash (/) should be used. Alternatively on windows you could you use double backslash (\\) (c:\\temp\\startdialog…….)

Finally, run

c:
cd c\temp\
call runexpert.bat
Advertising

4 - Documentation