Skip Headers
Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)

B31278-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

20 Designing Process Flows

After you design mappings that define the operations for moving data from sources to targets, you can create and define process flows. A Process Flow allows activities to be linked together and to describe constraints between the activities. Constraints can be conditional branches, loops, parallel flows or serial dependencies. Activities can be mappings, transforms or external commands such as email, FTP commands, and operating system executables.

You can use process flows to manage dependencies between mappings. To schedule mappings, process flows, and other executable objects, see "Process for Defining and Using Schedules".

This chapter contains the following topics:

About Process Flows

A process flow describes dependencies between Warehouse Builder mappings and external activities such as email, FTP, and operating system commands.

Each process flow begins with a Start activity and concludes with an End activity for each stream in the flow. A Process Flow is considered as a type of activity, so a Process Flow can start other process flows.

Figure 20-1 shows an example of a process flow that starts a mapping MAP1. If the mapping completes successfully, then Warehouse Builder sends an email notification EMAIL_SUCCEED and starts another process flow SUBPROC1. If the mapping fails, then Warehouse Builder sends an email EMAIL_FAIL and ends the process flow.

Figure 20-1 Sample Process Flow

This illustration is described in the surrounding text.
Description of "Figure 20-1 Sample Process Flow"

When you design a process flow in Warehouse Builder, you use an interface known as the Process Flow Editor. Alternatively, you can create and define process flows using the Warehouse Builder scripting language, OMB Scripting Language, as described in the Oracle Warehouse Builder API and Scripting Reference.

About Process Flow Modules and Packages

Process flow modules allow you to group process flow packages. Process flow packages, in turn, allow you to group process flows. Together, the process flow modules and packages provide two levels to manage and deploy process flows. You can validate, generate, and deploy process flows at either the module or the package level.

You can design a process flow that starts other process flows as long as they are in the same module. You can copy process flows from one package to another package in the same or a different module and you can copy packages to a different module. To do so, use the Copy and Paste commands available under Edit on the Design Center main menu.

For example, Figure 20-1 shows a process flow PROC1 that includes process flow SUBPROC1. For PROC1 to run successfully, SUBPROC1 and PROC1 can be in the same or separate packages but must be contained within the same module.

Deploying Process Flows to Workflow Engines

Warehouse Builder process flows comply with the XML Process Definition Language (XPDL) standard set forth by the Workflow Management Coalition (WfMC). When you generate a process flow, Warehouse Builder generates an XML file in the XPDL format. The generated XPDL can be used to integrate with any workflow engine that supports the WfMC standard.

Warehouse Builder provides integration with Oracle Workflow. From the Warehouse Builder Control Center, you can deploy process flow packages or modules to Oracle Workflow.

Instructions for Defining Process Flows

Before You Begin

To enable deployment of process flows, install Oracle Workflow as described in "Enabling Integration with Oracle Workflow" in the Oracle Warehouse Builder Installation and Administration Guide.

To define a process flow, refer to the following sections:

  1. Creating Process Flow Modules

  2. Creating Process Flow Packages

  3. Creating Process Flows

  4. Creating and Using Activity Templates

  5. Adding Activities

  6. Connecting Activities

  7. Using Activities in Process Flows

  8. Using Parameters and Variables

  9. Configuring Process Flows Reference

  10. Validating and Generating Process Flows

  11. Scheduling Process Flows (optional)

    When you are satisfied that the process flow runs as expected, you can schedule the process flow to run on a single day or multiple days as described in "Process for Defining and Using Schedules".

  12. Deploying Process Flows as described in "The Deployment and Execution Process".

Creating Process Flow Modules

Before working with process flows, create a process flow module. The module is a container by which you can validate, generate, and deploy a group of process flows. Process flow modules include process flow packages which include process flows.

To create a process flow module:

  1. Right-click the Process Flow Modules node in the Project Explorer and select New.

    Warehouse Builder displays the Welcome page for the Create Module Wizard.

  2. Click Next.

    On the Name and Description page, type a module name that is unique within the project. Enter an optional text description.

  3. Click Next.

    The wizard displays the Connection Information page.

    You can accept the default location that the wizard creates for you based on the module name. Alternatively, select an existing location from the list. Click Edit to type in the connection information and test the connection.

  4. Click Next.

    The wizard displays the Finish page. Verify the name and deployment location of the new process flow module.

    When you click Finish, Warehouse Builder stores the definition for the module, inserts its name in the Project Explorer, and prompts you to create a process flow package.

Creating Process Flow Packages

After you create a Process Flow module, you can create a process flow package. The process flow package is an additional grouping mechanism from which you can deploy process flows.

To create a process flow package:

  1. Right-click a process flow module in the Project Explorer and click New.

    Warehouse Builder displays the Create Process Flow Package dialog box.

  2. Type a name and optional description for the process flow package.

    If you intend to integrate with Oracle Workflow, please note that Oracle Workflow restricts package names to 8 bytes.

  3. Click OK.

    Warehouse Builder prompts you to create a process flow.

Creating Process Flows

After you create a module and package for process flows, you can create a process flow.

To create a process flow:

  1. Right-click a process flow package in the Project Explorer and click New.

    Warehouse Builder displays the Create Process Flow dialog box.

  2. Type a name and optional description for the process flow.

    Note:

    If you intend to schedule a process flow, there is an additional consideration. For any ETL object you want to schedule, the limit is 25 characters for physical names and 1995 characters for business names. Follow this additional restriction to enable Warehouse Builder to append to the process flow name the suffix _job and other internal characters required for deployment and running the process flow.
  3. Click OK.

    Warehouse Builder runs the Process Flow Editor and displays the process flow with a Start activity and an End_Success activity.

  4. You can now model the process flow with activities and transitions.

  5. Continue with the steps listed in "Instructions for Defining Process Flows".

About the Process Flow Editor

After you create a process flow module and package, use the Process Flow Editor to design and edit process flows. The Process Flow Editor includes a variety of activities that you can add and then connect with transitions to design a flow.

Activities represents units of work in a process flow. These units of work can involve components internal or external to Warehouse Builder. Transitions indicate the sequence and conditions to carry out the activities.

Standard Editor Components

The Process Flow Editor has the following standard components common to most editors in Warehouse Builder:

  • Title Bar: At the top of the Process Flow Editor, the title bar displays the name of the process flow.

  • Menu Bar: Below the title bar, the menu bar provides access to the Process Flow Editor commands.

  • Toolbar: Below the menu bar, the toolbar provides icons for commonly used commands.

  • Canvas: The canvas provides the work space where you design and modify process flows. When you first create a new process, the Process Flow panel is displayed with a Start activity and an End activity.

  • Palette: When you first start the Process Flow Editor, Warehouse Builder displays the palette along the left side. The Process Flow Editor contains activity icons that you can drag and drop on to the canvas. You can relocate the palette anywhere on the editor. You can choose to hide or display the palette by clicking the collapse icon on the palette.

  • Indicator Bar: On the lower panel, under the Bird's Eye View panel and Canvas panel, you can see mode icons, indicators, and descriptions.

    In the left corner are Naming Mode, Rename Mode, Read/Write, and Validation Mode.

    In the right corner are the percent zoom indicator and the navigation mode. In the preceding figure, the zoom level is at 100% and the navigation mode is set to Select Mode.

Process Flow Editor Windows

You can resize windows by placing your mouse on the border of the window, pressing the mouse button when the double sided arrow appears, and dragging your mouse to indicate the desired size.

You can move a window by placing the mouse pointer on the title bar of the window and then dragging the window to the required position.

To show or hide windows, select Window from the menu bar and either activate or deactivate the check mark corresponding to the window.

Explorer

When you first start the editor, Warehouse Builder displays an Explorer panel for the editor in the upper left corner. The explorer provides a tree listing of all the activities on the canvas and their parameters. When you select an activity on the canvas, Warehouse Builder navigates to the activity on the explorer.

Object Details

When you first start the editor, Warehouse Builder displays the Object Details panel on the left side. This panel displays the properties for all activities and their parameters. Select an activity either from the canvas or the explorer and Warehouse Builder displays its properties. If you select an activity parameter in the Explorer, then the object details window displays the properties for that parameter. You can edit properties displayed with a white background but not those with a gray background.

Palette

When you first start an editor, Warehouse Builder displays the palette along the left side and it contains activity icons that you can drag and drop onto the canvas.You can relocate the palette anywhere on the editor. You can choose to hide or display the palette by clicking on Operator Palette listed under View in the menu bar.

Bird's Eye View

Use the Bird's Eye View panel to navigate large and complex process flows.

Opening the Process Flow Editor

To open the Process Flow Editor:

  1. From the Process Flows node in the Project Explorer, select a process flow module. If no process flow modules are listed, then create a process flow module as described in "Creating Process Flow Modules".

  2. Select a process flow package from a process flow module. If no process flow packages are listed, then create a process flow package as described in "Creating Process Flow Packages".

  3. Select a process flow from the Project Explorer. If no process flows are listed in the process flow package, then right-click the process flow package and select Create Process Flow.

    Warehouse Builder prompts you to name the process flow and then starts the editor for you.

  4. To open an existing process flow, double-click the process flow in the Project Explorer.

    Alternatively, select a process flow and then from the Edit menu, select Open Editor. You can also, select a process flow and press Ctrl+O. You can also, right-click a process flow, and select Open Editor.

    Warehouse Builder displays the Process Flow Editor in the Select mode.

Navigating the Process Flow Editor

The Process Flow Editor includes a variety of tools to assist you in navigating, selecting, and viewing objects on the canvas. Commands you will use frequently when designing Process Flows include the following:

Select mode

This illustration is described in the surrounding text.
Description of the illustration select_mode.gif

Use the select mode to select objects on the canvas. When you select an activity, the editor displays a blue border around the activity. You can edit, move, or delete the activity.

You can edit the activity using the object details window in conjunction with the Available Objects tab in the editor explorer window. When you select a transition, the editor changes the arrow from black to blue. Edit the transition in the object details.

To activate the Select mode, click the icon in the toolbar or select Edit and Select Mode from the menu.

Navigation Edge

This illustration is described in the surrounding text.
Description of the illustration create_transition.gif

Navigation Edge assists you in navigating complex designs on the canvas. Select the icon from the toolbar and then select an activity on the canvas. When you release the mouse button, Warehouse Builder navigates to the next activity in the flow and moves the focus to that activity. To navigate backward through a flow, select the navigation edge icon and then select the last activity in the flow.

For navigating and displaying complex designs in the editor, you may find the following tools useful:

  • Pan

  • Interactive Zoom

  • Zoom In

  • Zoom Out

  • Fit in Window

  • Auto Layout

  • Center

  • Expand Child Graph

  • Visit Child Graph

  • Return to Parent Graph

Adding Activities to Process Flows

You can add activities in a process flow using the Explorer tree in the Warehouse Builder.

About Activities

Activities represent units of work for the process flow such as starting a mapping or verifying the existence of a file on a drive or directory. When you design a process flow in Warehouse Builder, you select activities from the editor palette, drag them onto the canvas, and set their parameters. Warehouse Builder includes the following types of activities:

  • Oracle Warehouse Builder Specific Activities: These activities enable you to start Warehouse Builder objects such as mappings, transformations, or other process flows. The process flow runs the object and provides a commit statement.

  • Utility Activities: These activities enable you to perform services such as sending emails and transferring files.

  • Control Activities: These activities enable you to control the progress and direction of the process flow. For instance, use the Fork activity to run multiple activities concurrently.

For the utility and control type activities, you can reuse their parameters by defining activity templates as described in "Creating and Using Activity Templates". For email, for example, use an email template to specify the SMTP server name and port number, the list of addresses, and the priority. Then you can reuse that template when you add email activities to a process flow.

For a description of each activity, see "Using Activities in Process Flows".

Adding Activities

To add an activity to a process flow:

  1. View the activities listed in the palette located along the left side of the editor.

    By default, the palette lists all activities. To find a particular activity, use the list box on the palette to narrow the displayed list to one of the following types of activities: Oracle Warehouse Builder Specific activities, Utility activities, and Control activities.

  2. Select an activity from the palette and drag it onto the canvas.

    The editor displays the activity on the canvas with the name highlighted in blue.

  3. To accept the default name, press Enter. To change the name, type in the new name.

    The editor lists the activity on the explorer pane located at the left side of the editor and in the object details pane along the left side.

  4. In Object Details pane, enter the parameters for the activity.

    These parameters vary according to the type of activity. For each parameter, Warehouse Builder defines a read-only Name, Direction, and Data Type. And for each parameter, you can specify values for Binding, Literal, Value, and Description.

    For example, Figure 20-2 shows the parameters for a notification activity which includes DEFAULT_RESPONSE, EXPANDED_ROLES, HTML_BODY, PERFORMER, PRIORITY, RESPONSE_PROCESSOR, RESPONSE_TYPE, SUBJECT, TEXT_BODY, and TIMEOUT.

    Figure 20-2 The Parameters for a Notification Activity

    This illustration is described in the surrounding text.
    Description of "Figure 20-2 The Parameters for a Notification Activity"

Parameters for Activities

Each parameter has the following properties:

Name

This is a name property of the activity parameter. For information about a specific parameter, look up the activity by name under "Using Activities in Process Flows".

Direction

The direction property is read-only for parameters that are not created by the user. A direction of IN indicates that the parameter is an input parameter for the activity.

Data Type

The data type property is read-only for parameters that are not created by the user. Warehouse Builder assigns the appropriate data type for all default parameters.

Binding

Use the binding property to pass in parameters from outside the process flow for parameters that are not created by the user. If you assign a parameter in Binding, then it overrides any text you assign to Value.

Literal

If you type in a value for the parameter in the field Value, then indicate whether the value is a literal or an expression. The literal data types follow the PL/SQL literal value specification except for calendar data types. These data types are represented in a standard format as the process flow interacts with data sources from different locations.

Table 20-1 provides the Literal Value Type, Format, and Example.

Table 20-1 Example of Literal Value Types

Literal Value Type Format Example

DATE

YYYY-MM-DD

2006-03-21

DATE

YYYY-MM-DD HH24:MI:SS

2006-03-21 15:45:00

TIMESTAMP

YYYY-MM-DD HH24:MI:SS.FF9

2006-03-21 15:45:00.000000000

TIMESTAMP_TZ

YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM

2006-03-21 15:45:00.000000000 +01:00

YMINTERVAL

[+-]YYYYYYYYY-MM

+000000001-01

DMINVERVAL

[+-]DDDDDDDDD HH24:MI.SS.FF9

+000000001 01:01:01.000000001


Value

This is the value of the parameter. For some parameters, Warehouse Builder enables you to select from a list of values. For other parameters, Warehouse Builder assigns default values which you can override by typing in a new value or using the field Binding. In the absence of a list of possible values or a default value, you must type in a value.

Description

You can type an optional description for each property.

Creating and Using Activity Templates

In designing process flows you may want to reuse existing activities. For example, each time a mapping fails in a process flow, you may want to send an email to the same group of administrators. You create a template for the email activity once and then use and edit the activity in many process flows.

To create an activity template:

  1. In the Project Explorer, navigate to the Activity Templates node under the Process Flows node.

  2. To create a folder for containing templates, right-click the Activity Templates node and select New.

  3. Assign a name for the folder.

    Consider creating a folder for each type of template you plan to create. For instance, you could create separate folders to contain email and ftp templates.

  4. The Create Activity Template Wizard is displayed.

    Note:

    If the wizard does not appear automatically, then right-click a folder and select New.

    Follow the prompts in the Create Activity Template Wizard to complete the Name and Description Page, the Parameters Page, and the wizard summary page.

  5. See "Using Activity Templates" for instructions about how to use the template in a process flow.

Name and Description Page

The rules for naming objects in the Activity Template depend on the naming mode you select in "Naming Preferences". Warehouse Builder maintains a business and a physical name for each object in the workspace. The business name is its descriptive business name. The physical name is the name Warehouse Builder uses when generating code.

When you name objects while working in one naming mode, Warehouse Builder creates a default name for the other mode. So, when working in the business name mode, if you assign an activity template name that includes mixed cases, special characters, and spaces, then Warehouse Builder creates a default physical name for the objects.

Assign a name and select the type of activity template you want to create. Also, write an optional description for the template.

Naming Activities

In the physical naming mode, an Activity name can be from 1 to 30 alphanumeric characters and blank spaces are not allowed. In the business naming mode, the limit is 2000 characters and blank spaces and special characters are allowed. In both naming modes, the name should be unique across the project.

Describing Activities

The description can be between 2 and 2000 alphanumeric characters and can contain blank spaces. Specifying a description for an activity template is optional.

Activity Templates

The following activity templates are available from the list.

  • Assign

  • Email

  • FTP

  • File Exists

  • Manual

  • Notification

  • Set Status

  • Sqlplus

  • User Defined

  • Wait

Parameters Page

The wizard displays parameters based on the type of activity you previously selected in the Activity Templates.

Enter default values for the activity. When you use the activity template in a process flow, you can retain or edit the default values.

In Figure 20-3, for example, you could edit the default values for the email subject and message body to contain the name of the mapping.

Figure 20-3 Parameters Page for Email Activity Template

This illustration is described in the surrounding text.
Description of "Figure 20-3 Parameters Page for Email Activity Template"

Using Activity Templates

Complete the following steps to use an activity template:

  1. In the Project Explorer, navigate to the process flow module under the Process Flows node.

  2. To open the Process Flow Editor, right-click the Process Flow module and select Open Editor.

  3. In the Process Flow Editor, click the Available Objects tab in the Explorer panel and expand Activity Templates.

    Figure 20-4 displays the Explorer window with the activity template expanded.

    Figure 20-4 Explorer Panel with an Activity Template Selected

    Description of Figure 20-4 follows
    Description of "Figure 20-4 Explorer Panel with an Activity Template Selected"

  4. Drag and drop the activity template onto the canvas.

    Activity templates in a process flow acts like regular activities.

  5. To edit the activity, be sure to click the Selected Objects tab in the Explorer window and then edit the activity in the Object Details panel.

    Figure 20-5 displays the Explorer panel with the BCC_ADDRESS parameter of the EMAIL activity selected.

    Figure 20-5 Editing an Activity Template

    Description of Figure 20-5 follows
    Description of "Figure 20-5 Editing an Activity Template"

About Transitions

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

Description of trans_not_specified.gif follows
Description of the illustration trans_not_specified.gif

When you add a transition to the canvas, by default, the transition has no condition applied to it. The process flow continues once the preceding activity completes, regardless of the ending state of the previous activity.

A transition with no condition applied to it has different semantics depending on the source activity type. If the activity type is FORK, then it may have multiple unconditional transitions in which each transition begins a new flow in the process flow. If the source activity type is not FORK, then there may be only one unconditional transition and it is used when no other conditional transition is activated, for example, the final ELSE condition in an IF...THEN...ELSIF...ELSE...END PL/SQL statement.

Rules for Valid Transitions

For a transition to be valid, it must conform to the following rules:

  • All activities, apart from START and END, must have at least one incoming transition.

  • Only the AND and OR activities can have more than one incoming transition.

  • Only a FORK activity can have more than one unconditional outgoing transition.

  • A FORK activity can have only unconditional outgoing transitions.

  • An activity that has an enumerated set of outcomes must have either an outgoing transition for each possible outcome or an unconditional outgoing transition.

  • An activity can have zero or more outgoing complex expression transitions.

  • An activity, with an outgoing complex expression transition, must have an unconditional outgoing transition.

  • An END_LOOP transition must have only one unconditional transition to its associated FOR_LOOP or WHILE_LOOP activity.

  • The transition taken by the exit outcome of a FOR_LOOP or WHILE_LOOP must not connect to an activity that could be carried on as a result of the "loop."

Connecting Activities

To create dependencies using transitions:

  1. When working in the select mode, place your mouse pointer along the right border of the activity icon along its center line.

    The editor displays the cursor as a small horizontal arrow, indicating that you can now use the mouse button to connect activities.

  2. Press the left mouse button and scroll towards the next activity. As you begin to scroll, the cursor appears as an arrow with a plus sign under it. Continue to scroll towards the next activity until the plus sign under the cursor arrow changes to a circle. Release the mouse button to connect the two activities.

    The editor displays an arrow between the two activities, assigns a default name to the transition, and displays the transition in the explorer and object selector windows.

  3. In the object selector window, view or edit the following attributes:

    Name: The editor assigns a default name which you can change.

    Description: You can type an optional description for the transition.

    Condition: Transitions that you initially draw on the canvas are unconditional by default. To override the default and apply conditions, click the button in the Condition as described in "Defining Transition Conditions". If you select a condition, then the editor displays the associated icon imposed onto the transition line on the canvas.

    Source: This property is read-only and indicates the first activity in the connection.

    Target: This property is read-only and indicates the second activity in the connection.

Configuring Activities

Some activities such as Sqlplus require additional configuration. These configuration details for a given activity are listed in "Using Activities in Process Flows".

Using Parameters and Variables

Process flows and activities support the PL/SQL parameter passing concept, allowing data to be passed and reused through parameterization. This is accomplished through data stores, which are implemented as either parameters or variables. Process flow allows the data to be passed between data stores.

  • Parameters allow passing of data between a process flow and its activities or subprocesses.

  • Variables allow the storage of transient data, which is then maintained for the lifetime of running the process flow. Variables are used to pass data between activities.

Figure 20-6 shows the direction in which the data is passed.

Figure 20-6 Relationship between the scope and the direction in which the data is passed

Description of Figure 20-6 follows
Description of "Figure 20-6 Relationship between the scope and the direction in which the data is passed"

Process flows follow the following rules for allowing the data to be passed between data stores:

  1. Process flow variables can be initialized from flow parameters, but the reverse is not allowed.

  2. Activity parameters can pass data bidirectionally between process flow variables and process flow parameters.

  3. Transition expressions can be evaluated against their source activity parameters, process flow parameters, and process flow variables.

  4. A data store cannot be accessed from another data store within the same scope.

Using Namespace

The namespace allows a data store of an inner scope to hide the data store of an outer scope, similar to PL/SQL. By qualifying the data store name with the process flow name or activity, you can reference the hidden data store name. For example:

My_PROC.VAR1

The namespace does not allow referencing of data from another data store within the same scope.

Using Bindings

A data store may be bound to another data store in an outer scope, which supports the passing of data in both directions.

Process flow bindings follow the same semantics as PL/SQL with the following rules:

  1. All the data is passed within the process flow by value.

  2. Variables can be initialized through a binding. They cannot return a value.

  3. An INOUT parameter can be bound to an IN parameter in an outer scope. The output value, which is passed by value, is audited and then discarded.

A variable may not pass data out to a Process Flow parameter. So, this is accomplished by the use of an Assign operator, which can be bound to the variable and the parameter.

Expressions

Oracle Warehouse Builder supports the use of PL/SQL expressions for the derivation of parameter values and the use of 'complex expression' transitions.

The expression must produce a correctly typed value for data store. Automatic conversion from VARCHAR is supported. When the expression is associated with a transition a BOOLEAN result is expected.

During evaluation, an expression will have access to the outer scope which encloses it. So, an expression for an activity parameter will be able to use process flow variables and process flow parameters in its evaluation.

The PL/SQL expression is run in the context of the Control Center user who requested the process of the activity. However, in the case where the Oracle Workflow schema is hosted in a remote database instance, the effective user of the generated database link will be used instead. A different Control Center user may be selected by configuring the process flow and specifying an 'Evaluation Location.' So the expression may reference any PL/SQL function that is accessible to the Control Center user.

Global Expression Values

Warehouse Builder also makes additional data values available to the expression from the current activity and the owning process flow.

Table 20-2 lists these global expression values.

Table 20-2 Global Expression Values

Identifier Type Description

NUMBER_OF_ERRORS

NUMBER

Number of errors reported on completion of activity execution

NUMBER_OF_WARNINGS

NUMBER

Number of warnings reported on completion of activity execution

RETURN_RESULT

VARCHAR2(64)

Textual representation of result. For example, 'SUCCESS,' 'WARNING,' 'ERROR'

RETURN_RESULT_NUMBER

NUMBER

Enumeration of RESULT_RESULT1 = SUCCESS2 = WARNING3 = ERROR

RETURN_CODE

NUMBER

Integer 0-255, specific to activity, synonymous with an Operating System return code

PARENT_AUDIT_ID

NUMBER

The audit ID of the calling Process Flow

AUDIT_ID

NUMBER

The audit ID of the activity


Table 20-3 lists the additional constants provided.

Identifier Type Description
SUCCESS NUMBER SUCCESS enumerated value
WARNING NUMBER WARNING enumerated value
ERROR NUMBER ERROR enumerated value

Defining Transition Conditions

Use the Transition Editor to specify one of the enumerated conditions or write an expression for a complex condition. The enumerated conditions include success, warning, and error. These are displayed on the canvas as shown in Table 20-3.

Table 20-3 Types of Conditions for Transitions

Icon Transition Description
This illustration is described in the surrounding text.

Success

The process flow continues only if the preceding activity ends in success.

This illustration is described in the surrounding text.

Warning

The process flow continues only if the preceding activity ends with warnings.

This illustration is described in the surrounding text.

Error

The process flow continues only if the preceding activity ends in error.

This illustration is described in the surrounding text.

Warning

The process flow continues only if the preceding activity ends with warnings.

This illustration is described in the surrounding text.

Complex

The process flow continues only if the preceding activity returns a value that meets the criteria you specify in an expression.

This illustration is described in the surrounding text.

Extended

The process flow continues only if the preceding notification activity ends with an extended result.


Extended transition is valid only for Notification activities because they are the only activity that return an extended result. The activity acquires this icon when set to an outcome of #MAIL, #NOMATCH, #TIE, or #TIMEOUT. Table 20-4 lists the output and the description of the Extended transition.

Table 20-4 Output and Description of the Extended Transition

Output Description

#NOMATCH

Result of a voting notification where no candidate acquired the minimum number of votes to win.

#TIE

Result of a voting notification where the result was a tie.

#MAIL

A mail error occurred for the notification. Some recipients did not receive an email notification and so it was canceled.

#TIMEOUT

The notification did not receive a response within the configured amount of time.


If the activity has only one outgoing activity, then you can specify any of the conditions listed in Table 20-3 or leave the transition as unconditional.

The rules for using multiple outgoing transitions depend on the type of activity. The general rule is that you can use an unlimited number of complex conditions in addition to one of each of the following: SUCCESS, WARNING, ERROR, and UNCONDITIONAL. The exception to this rule is when you use control activities such as AND, FORK, and OR.

When you add multiple outgoing transitions from an activity, ensure that the conditions do not conflict. A conflict occurs when the process flow logic evaluates that more than one outgoing transition is true.

Figure 20-7 shows a portion of a process flow in which different activities are triggered based on the three possible completion states of MAP1. Because only one of these conditions can be satisfied at a time, there is no conflict. If you attempt to add an unconditional transition or another conditional transition, two transition conditions would be true and the process flow would be invalid.

Figure 20-7 Outgoing Transition Conditions

This illustration is described in the surrounding text.
Description of "Figure 20-7 Outgoing Transition Conditions"

Example: Using Process Flows to Access Flat Files with Variable Names

Scenario

Your company relies on a legacy system that writes data to a flat file on a daily basis and assigns a unique name to the file based on the date and time of its creation. You would like to create a mapping that uses the generated flat files as a source, and transforms and loads the data to a relational database. However, mappings require files to have permanent names and, in this situation, the name of the source file changes each time the file is created.

Solution

In Warehouse Builder, you can design a process flow that locates the generated file in a specific directory, renames it to a permanent name you designate, and starts a dependent mapping. You can now use the permanent flat file name as the source for your mapping.

Case Study

This case study describes how to create a process flow and a mapping to extract data from a legacy system that generates flat files with variable names. The process flow relies on the use of an external process activity. Assume the following information for the purposes of this case study:

Your objective is to create logic that ensures the generated flat file is renamed appropriately before it triggers the execution of a mapping.

To extract data from a generated flat file with a name that varies with each generation, refer to the following sections:

  1. "Creating the Process Flow"

  2. "Setting Parameters for the External Process Activity"

  3. "Configuring the External Process Activity"

  4. "Designing the Mapping"

  5. "Deploying and Executing"

Creating the Process Flow

Create a process flow that starts a mapping on the condition that the external process activity completes successfully. Your process flow should resemble Figure 20-8. For more information on creating the process flow, refer to "Instructions for Defining Process Flows".

Figure 20-8 Process Flow with External Process Transitioning to a Mapping

Description of Figure 20-8 follows
Description of "Figure 20-8 Process Flow with External Process Transitioning to a Mapping"

Setting Parameters for the External Process Activity

This section describes how to specify the DOS commands for renaming the generated file. The DOS commands you issue from the external process activity should be similar to the following:

copy c:\staging_files\sales*.* c:\staging_files\s_data.dat
del c:\staging_files\sales*.*

The first command copies the temporary file into a file with a fixed name s_data.dat. The second command deletes the originally generated file.

You can either direct Warehouse Builder to a file containing the script of commands or you can store the commands in the Warehouse Builder user interface. Choose one of the following methods:

Method 1: Write a script within Warehouse Builder

Choose this method when you want to maintain the script in Warehouse Builder. Consider using this method when the script is small and need not be very flexible.

For this method, write or copy and paste the script into the Value column of the SCRIPT parameter. In the COMMAND parameter, type the path to the DOS shell command such as c:\winnt\system32\cmd.exe. Also, type the ${Task.Input} variable into the Value column of the PARAMETER_LIST parameter. Your Activity View should resemble Figure 20-9.

Figure 20-9 External Process Parameters When Script Maintained in this Product

Description of Figure 20-9 follows
Description of "Figure 20-9 External Process Parameters When Script Maintained in this Product"

Although this case study does not illustrate it, you can use substitution variables in the script when you maintain it in Warehouse Builder. This prevents you from having to update activities when server files, accounts, and passwords change.

Table 20-5 lists the substitute variables you can type for the external process activity. Working refers to the computer hosting the Runtime Service, the local computer in this case study. Remote refers to a server other than the Runtime Service host. You designate which server is remote and local when you configure the activity as described in "Configuring the External Process Activity". These values are set when you register the locations at deployment.

Table 20-5 Substitute Variables for the External Process Activity

Variable Value

${Working.Host}

The host value for the location of the Runtime Service host.

${Working.User}

The user value for the location of the Runtime Service host.

${Working.Password}

The password value for the location of the Runtime Service host.

${Working.RootPath}

The root path value for the location of the Runtime Service host.

${Remote.Host}

The host value for a location other than the Runtime Service host.

${Remote.User}

The user value for a location other than the Runtime Service host.

${Remote.Password}

The password value for a location other than the Runtime Service host.

${Remote.RootPath}

The root path value for a location other than the Runtime Service host.

${Deployment.Location}

The deployment location.


Method 2: Call a script maintained outside of Warehouse Builder

If extra maintenance is not an issue, you can point Warehouse Builder to a file containing a script including the necessary commands. This method is more flexible as it enables you to pass in parameters during execution of the process flow.

The following example shows how to call an external process script outside of Warehouse Builder and illustrates how to pass parameters into the script during execution of the process flow. This example assumes a Windows operating system. For other operating systems, issue the appropriate equivalent commands.

To call a script outside the external process activity:

  1. Write the script and save it on the file directory. For example, you can write the following script and save it as c:\staging_files\rename_file.bat:

    copy c:\staging_files\%1*.dat c:\staging_files\s_data.datdel c:\staging_files\%1*.dat

    In this sample script, we pass a parameter %1 to the script during the execution of the process flow. This parameter represents a string containing the first characters of the temporary file name, such as sales010520041154.

  2. Select the start activity on the canvas to view and edit activity parameters in the Available Objects tab of the Explorer panel displayed in the Process Flow Editor.

    To add a start parameter, click Add on the upper left corner of the Explorer pane in the Available Objects tab. Create a start parameter named FILE_STRING as shown in Figure 20-10. During execution, Warehouse Builder will prompt you to type a value for FILE_STRING to pass on to the %1 parameter in the rename_file.bat script.

    Figure 20-10 Start Activity in the Activity View

    Description of Figure 20-10 follows
    Description of "Figure 20-10 Start Activity in the Activity View"

  3. Select the external process activity on the canvas and edit its parameters as shown in Figure 20-11.

    For the COMMAND parameter, type the path to the script in the column labeled Value. If necessary, use the scroll bar to scroll down and reveal the column. For this example, type c:\staging_files\rename_file.bat.

    For PARAMETER_LIST, click the row labeled Binding and select the parameter you defined for the start activity, FILE_STRING

    Accept the defaults for all other parameters for the external process. Your Activity View for the external process activity should resemble Figure 20-11.

    Figure 20-11 External Process Parameters When Calling an Outside Script

    Description of Figure 20-11 follows
    Description of "Figure 20-11 External Process Parameters When Calling an Outside Script"

Configuring the External Process Activity

When you apply conditions to the outgoing transitions of an external process, you must define the meaning of those conditions when you configure the external process activity.

To configure the external process activity:

  1. Right-click the process flow on the navigation tree and select Configure.

  2. Expand the external process activity and the Path Settings. Warehouse Builder displays the configuration settings.

  3. Complete this step if you wrote the script in the Warehouse Builder user interface using the substitution variables related to Remote Location, Working Location, and Deployment Location as listed in Table 20-5. Use the list to select the values.

    Because this case study does not use substitution variables, accept the defaults values.

  4. Set the Deployed Location to the computer where you deploy the process flow.

  5. Select Use Return as Status.

    This ensures that the process flow uses the external process return codes for determining which outgoing transition to activate. For the process flow in this case study, shown in Figure 20-8, if the external process returns a success value, the process flow continues down the success transition and executes the downstream mapping.

Designing the Mapping

Now you can design a mapping with s_data.dat as the source. You can create a PL/SQL mapping or a SQL*Loader mapping. For a PL/SQL, map the flat file source to an external table and design the rest of the mapping with all the operators available for a PL/SQL mapping. For SQL*Loader, map the flat file source to a staging table and limit the mapping to those operators permitted in SQL*Loader mappings.

Deploying and Executing

Deploy the mapping. Also, deploy the process flow package or module containing the process flow OWF_EXT.

Execute the process flow manually. When you execute the process flow, Warehouse Builder prompts you to type values for the parameter you created to pass into the script, FILE_STRING For this case study, type ?sales where the question mark is the separator, as shown in Figure 20-12. The external activity then executes the command rename_file.bat sales.

Figure 20-12 External Process Activity in the Activity View

Description of Figure 20-12 follows
Description of "Figure 20-12 External Process Activity in the Activity View"

Subsequent Steps

After you successfully execute the process flow manually, consider creating a schedule.You can define a daily schedule to execute the process flow and therefore the mapping.

Creating a Schedule

Use schedules to plan when and how often to execute operations such as mappings and process flows that you deploy through Warehouse Builder.

To create a scheduler:

  1. Right-click the Schedules node in the Project Explorer and select New.

    Warehouse Builder displays the Welcome page for the Create Module Wizard.

  2. Click Next.

    On the Name and Description page, type a module name that is unique within the project. Enter an optional text description.

  3. Click Next.

    The wizard displays the Connection Information page.

    You can accept the default location that the wizard creates for you based on the module name. Or, select an existing location from the location list. Click Edit to type in the connection information and test the connection.

  4. Click Next.

    The wizard displays the Summary page. Verify the name and status of the new Scheduler module.

    When you click Finish, Warehouse Builder stores the definition for the module and inserts its name in the Project Explorer, and prompts you to create a schedule.

Example: Using Process Flows to Transfer Remote Files

Scenario

Developers at your company designed mappings that extract, transform, and load data. The source data for the mapping resides on a server separate from the server that performs the ETL processing. You would like to create logic that transfers the files from the remote computer and triggers the dependent mappings.

Solution

In Warehouse Builder, you can design a process flow that executes file transfer protocol (FTP) commands and then starts a mapping. For the process flow to be valid, the FTP commands must involve transferring data either from or to the server with the Runtime Service installed. To move data between two computers, neither of which host the Runtime Service, first transfer the data to the Runtime Service host computer and then transfer the data to the second computer.

You can design the process flow to start different activities depending upon the success or failure of the FTP commands.

Case Study

This case study describes how to transfer files from one computer to another and start a dependent mapping. The case study provides examples of all the necessary servers, files, and user accounts.

Your objective is to create logic that ensures the flat file on salessrv1 is copied to the local computer and then trigger the execution of the salesresults mapping.

To transfer files and start a dependent mapping, refer to the following sections:

  1. "Defining Locations".

  2. "Creating the Process Flow"

  3. "Setting Parameters for the FTP Activity"

  4. "Configuring the FTP Activity"

  5. "Registering the Process Flow for Deployment"

After you complete the instructions in the above sections, you can run the process flow.

Creating the Process Flow

Use the Process Flow Editor to create a process flow with an FTP activity that transitions to the salesresults mapping on the condition of success. Your process flow should appear similar to Figure 20-13.

Figure 20-13 Process Flow with FTP Transitioning to a Mapping

Description of Figure 20-13 follows
Description of "Figure 20-13 Process Flow with FTP Transitioning to a Mapping"

Setting Parameters for the FTP Activity

This section describes how to specify the commands for transferring data from the remote server salessrv1, to the local computer. You specify the FTP parameters by typing values for the FTP activity parameters on the Activity View as displayed in Figure 20-14.

Warehouse Builder offers you flexibility on how you specify the FTP commands. Choose one of the following methods:

  • Method 1: Write a script in Warehouse Builder: Choose this method when you want to maintain the script in Warehouse Builder and/or when password security to servers is a requirement.

    For this method, write or copy and paste the script into the Value column of the SCRIPT parameter. In the COMMAND parameter, type the path to the FTP executable such as c:\winnt\system32\ftp.exe. Also, type the Task.Input variable into the Value column of the PARAMETER_LIST parameter.

  • Method 2: Call a script maintained outside of Warehouse Builder: If password security is not an issue, you can direct Warehouse Builder to a file containing a script including the FTP commands and the user name and password.

    To call a file on the file system, type the appropriate command in PARAMETER_LIST to direct Warehouse Builder to the file. For a Windows operating system, type the following: ?"-s:<file path\file name>"?

    For example, to call a file named move.ftp located in a temp directory on the C drive, type the following: ?"-s:c:\temp\move.ftp"?

    Leave the SCRIPT parameter blank for this method.

Example: Writing a Script in Warehouse Builder for the FTP Activity

The following example illustrates Method 1 described above. It relies on a script and the use of substitution variables. The script navigates to the correct directory on salessrv1 and the substitution variables are used for security and convenience.

This example assumes a Windows operating system. For other operating systems, issue the appropriate equivalent commands.

To define a script within the FTP activity:

  1. Select the FTP activity on the canvas to view and edit activity parameters in the Available Objects tab of the Explorer panel in the Process Flow Editor.

  2. For the COMMAND parameter, type the path to the FTP executable in the column labeled Value. If necessary, use the scroll bar to scroll to the right and reveal the column labeled Value.

    For windows operating systems, the FTP executable is often stored at c:\winnt\system32\ftp.exe.

  3. For the PARAMETER_LIST parameter, type the Task.Input variable.

    When defining a script in Warehouse Builder and using Windows FTP, you must type ?"-s:${Task.Input}"? into PARAMETER_LIST.

    For UNIX, type ?"${Task.Input}"?.

  4. Navigate and highlight the SCRIPT parameter. Your Available Objects tab should display similar to Figure 20-14.

    Figure 20-14 Activity View for FTP Activity Using a Script

    Description of Figure 20-14 follows
    Description of "Figure 20-14 Activity View for FTP Activity Using a Script"

  5. Click the Ellipses displayed to the right of the Value field displayed in the Object Details panel.

    Warehouse Builder displays the SCRIPT Value editor. Write or copy and paste FTP commands into the editor.

    Figure 20-14 shows a script that opens a connection to the remote host, changes the directory to the local computer, changes the directory to the remote host, transfers the file, and closes the connection.

    Notice that the script in Figure 20-15 includes ${Remote.User} and ${Remote.Password}. These are substitution variables. Refer to "Using Substitution Variables" for more details.

    Figure 20-15 SCRIPT Value Editor Using Substitution Variables

    Description of Figure 20-15 follows
    Description of "Figure 20-15 SCRIPT Value Editor Using Substitution Variables"

Using Substitution Variables

Substitution variables are available only when you choose to write and store the FTP script in Warehouse Builder.

Use substitution variables to prevent having to update FTP activities when server files, accounts, and passwords change. For example, consider that you create 10 process flows that utilize FTP activities to access a file on salessrv1 under a specific directory. If the file is moved, without the use of substitution variables, you must update each FTP activity individually. With the use of substitution variables, you need only update the location information as described in "Defining Locations".

Substitution variables are also important for maintaining password security. When Warehouse Builder executes an FTP activity with substitution variables for the server passwords, it resolves the variable to the secure password you provided for the associated location.

Table 20-6 lists the substitute variables you can provide for the FTP activity. Working refers to the computer hosting the Runtime Service, the local computer in this case study. Remote refers to the other server involved in the data transfer. You designate which server is remote and local when you configure the FTP activity. For more information, see "Configuring the FTP Activity".

Table 20-6 Substitute Variables for the FTP Activity

Variable Value

${Working.RootPath}

The root path value for the location of the Runtime Service host.

${Remote.Host}

The host value for the location involved in transferring data to or from the Runtime Service host.

${Remote.User}

The user value for the location involved in transferring data to or from the Runtime Service host.

${Remote.Password}

The password value for the location involved in transferring data to or from the Runtime Service host.

${Remote.RootPath}

The root path value for the location involved in transferring data to or from the Runtime Service host.


Configuring the FTP Activity

As part of configuring the complete process flow, configure the FTP activity.

To configure the FTP Activity:

  1. Right-click the process flow on the navigation tree and select Configure.

  2. Expand the FTP activity and the Path Settings. Warehouse Builder displays the configuration settings.

  3. Set Remote Location to REMOTE_LOCATION and Working Location to LOCAL_LOCATION.

  4. Click to select the Use Return as Status. This ensures that the process flow uses the FTP return codes for determining which outgoing transition to activate. For the process flow in this case study, shown in Figure 20-13, if FTP returns a success value of 1, the process flow continues down the success transition and executes the salesresults mapping.

Registering the Process Flow for Deployment

After you complete these instructions, you can deploy and run the process flow. To deploy the process flow, start the Deployment Manager by right-clicking and selecting Deploy from either the process flow module or package on the navigation tree. The Deployment Manager prompts you to register the REMOTE_LOCATION and the LOCAL_LOCATION.

Figure 20-16 shows the registration information for the REMOTE_LOCATION. For the LOCAL_FILES, only the root path is required.

Figure 20-16 Example Location Registration Information

Description of Figure 20-16 follows
Description of "Figure 20-16 Example Location Registration Information"

Now you can run the process flow.

Defining Locations

Locations are logical representations of the various data sources and destinations in the warehouse environment. In this scenario, the locations are the logical representations of the host and path name information required to access a flat file. Warehouse Builder requires these definitions for deploying and running the process flow. When you deploy the process flow, Warehouse Builder prompts you to type the host and path name information associated with each location. You must define locations for each computer involved in the data transfer.

To define locations, right-click the appropriate Locations node in the Connection Explorer and select New. For salessrv1, right-click Files under the Locations node and create a location named REMOTE_FILES. Repeat the step for local and create the location LOCAL_FILES.