8 Designing Process Flows

About Process Flow Modules and Packages

Process flows must be grouped into process flow packages, which in turn are grouped in process flow modules. 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 because 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 8-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 process flow modules, but they must be deployed to the same location.

Deploying Process Flows to Workflow Engines

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

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

Creating Oracle Workflow Locations

Use an Oracle Workflow location to deploy process flows. This location corresponds to the Oracle Workflow schema.

To create an Oracle Workflow location:

1. In the Locations Navigator, expand the Locations node and then the Process Flows and Schedules node.

2. Right-click the Oracle Workflow node and select New Oracle Workflow Location.

   The Create Oracle Workflow Location dialog box is displayed.

3. On the Details page, provide information in the following fields:

   • Name: Represents the name of the Oracle Workflow location.

   • Description: Represents the description of the location. Providing a description is optional.

   • Connection Type: Represents the type of connection to Oracle Workflow. Select one of the following options:

     • Host:Port:Service: Makes a connection using the Easy Connect Naming method, which requires no prior setup. Enter the following additional information.

       Host: The name of the system where Oracle Database is installed with Oracle Workflow Manager. If the client software is installed on the same system as Oracle Database, you can enter localhost instead of the computer name.

       Port: The SQL port number for the Oracle Database.

       Service: The service name of the Oracle Database.

       SQL*NET Connection: Defining workflow connections with SQL*Net is not supported and results in a RTC-5336 error when testing the connection.

   • Schema: Represents the user name for the Workflow schema.

   • Password: Represents the password for the user specified in the Schema field.

   • Version: Represents the version of Oracle Workflow.

Creating Process Flow Modules

Before working with process flows, create a process flow module. The module is a container using 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 Projects Navigator and select New Process Flow Module.

   Oracle 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 enter 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, Oracle Warehouse Builder stores the definition for the module, inserts its name in the Projects Navigator, 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 Projects Navigator and click New Process Flow Package.

   Oracle 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, Oracle Workflow restricts package names to 8 bytes.

3. Click OK.

   Oracle 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 Projects Navigator and click New Process Flow.

   Oracle 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 to schedule, the limit is 25 characters for physical names and 1,995 characters for business names. Follow this additional restriction to enable Oracle 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.

   Oracle 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 "Steps for Defining Process Flows".

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 Oracle Warehouse Builder, you select activities from the Component Palette, drag them onto the canvas, and set their parameters. Oracle Warehouse Builder includes the following types of activities:

• Oracle Warehouse Builder Specific Activities: These activities enable you to start Oracle 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 e-mails 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 e-mail, for example, use an e-mail 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. Open the process flow by right-clicking the process flow in the Projects Navigator and selecting Open.

2. View the activities listed in the Component Palette.

   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.

3. 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.

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

   The editor lists the activity in the Structure panel. The properties of this activity are displayed in the Property Inspector.

5. In the Property Inspector, add parameters for the activity by clicking New Process Activity Parameter at the top of the Structure panel.

   The parameters for an activity vary according to the type of activity. For each activity, Oracle Warehouse Builder defines read-only parameters "Name", "Direction", and "Data Type". And for each parameter, you can specify values for "Binding", "Literal", "Value", and "Description" in the Property Inspector.

   For example, Figure 8-3 shows the parameters for a Notification activity. The parameters include DEFAULT_RESPONSE, EXPAND_ROLES, HTML_BODY, PERFORMER, PRIORITY, RESPONSE_PROCESSOR, RESPONSE_TYPE, SUBJECT, TEXT_BODY, and TIMEOUT.

   Figure 8-3 Parameters for a Notification Activity

   
   Description of "Figure 8-3 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 Example 27-0, "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. Oracle 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 enter 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.

The values you can select for Literal are True or False. When you set Literal to False, then the value entered for the Value property must be a valid PL/SQL expression which is evaluated at the Control Center. When you set Literal to True, then the value depends on the type of activity. If the activity is a PL/SQL object, such as a mapping or process flow, then the Value is a PL/SQL snippet. If the activity is not a PL/SQL object, then the Value is language-dependent.

Table 8-1 provides the Literal value type, format, and some examples.

Value

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

Description

You can enter an optional description for each property.

Name and Description Page

The rules for naming objects in the activity template depend on the naming mode that you select in that Naming Preferences section of the Preferences dialog box. Oracle 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 that Oracle Warehouse Builder uses when generating code.

When you name objects while working in one naming mode, Oracle 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 Oracle Warehouse Builder creates a default physical name for the objects.

Assign a name and select the type of activity template 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 enabled. In the business naming mode, the limit is 200 characters and blank spaces and special characters are enabled. In both naming modes, the name should be unique across the project.

Describing Activities

The description can be up to 4,000 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

• Enterprise Java Bean

• FTP

• File Exists

• Java Class

• Manual

• Notification

• OMBPlus

• Set Status

• Sqlplus

• User Defined

• Wait

Parameters Page

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

Enter default values for the activity. When you use the activity template in a process flow, you can retain or edit the default values. For example, an Email activity template contains the parameters FROM_ADDRESS and REPLY_TO_ADDRESS. When you use an Email activity template in a process flow, you can overwrite the default values of these parameters with different values.

Using Activity Templates

Complete the following steps to use an activity template:

1. In the Projects Navigator, navigate to the process flow module under the Process Flows node.

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

3. From the Graph menu, select Add, then Available Objects.

   The Add Available Objects dialog box is displayed.

4. Select the activity template you want to use and click OK.

   The activity template is added to the Process Flow Editor canvas. Activity templates in a process flow acts like regular activities.

Alternatively, instead of Steps 3 and 4, you can drag and drop an activity template from the Projects Navigator on to the Process Flow Editor canvas.

To edit the activity, select the activity on the canvas and use the Structure tab to modify the activity details.

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 multiple incoming transition.

• Only a FORK activity can have multiple 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 because of the "loop."

Connecting Activities

To create dependencies using transitions:

1. When working in the Select mode in the Process Flow Editor, 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 toward the next activity. As you begin to scroll, the cursor appears as an arrow with a plus sign under it. Continue to scroll toward 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 Structure panel. The properties of the transition are displayed in the Property Inspector.

3. In the Property Inspector, view or edit the following attributes for the transition:

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

   Description: You can enter 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 new conditions, select the transition. The Property Inspector displays the transition properties. Click the Ellipsis button to the right of the Condition field and, in the Edit Property dialog box, select the condition to apply to the transition. When 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 Chapter 27, "Activities in Process Flows".

Using Parameters and Variables

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

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

• Variables enable 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 8-4 shows the direction in which the data is passed.

Figure 8-4 Relationship Between the Scope and the Direction in Which the Data is Passed

Description of "Figure 8-4 Relationship Between the Scope and the Direction in Which the Data is Passed"

Process flows adhere to the following rules for enabling the data to be passed between data stores:

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

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 a Namespace

The namespace enables 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 enable 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.

Because a variable cannot pass data out to a process flow parameter, this is accomplished by the use of an Assign operator, which can be bound to the variable and the parameter.

Global Expression Values

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

Table 8-2 lists these global expression values.

Table 8-3 lists the additional constants provided.

Creating the Process Flow

Create a process flow that starts a mapping on the condition that the User Defined activity completes successfully. For more information about creating the process flow, see "Steps for Defining Process Flows".

Figure 8-6 displays the process flow you create to extract data from a generated flat file.

Figure 8-6 Process Flow with User Defined Activity Transitioning to a Mapping

Description of "Figure 8-6 Process Flow with User Defined Activity Transitioning to a Mapping"

Setting Parameters for the User Defined Activity

This section describes how to specify the DOS commands for renaming the generated file. The DOS commands that you issue from the User Defined 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 Oracle Warehouse Builder to a file containing the script of commands or you can store the commands in Oracle Warehouse Builder user interface. Choose one of the following methods:

• "Method 1: Write a script Within Oracle Warehouse Builder"

• "Method 2: Call a script maintained outside of Oracle Warehouse Builder"

Configuring the User Defined Activity

When you apply conditions to the outgoing transitions of a User Defined activity, you must define the meaning of those conditions when you configure the User Defined activity.

To configure the User Defined activity:

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

   The configuration properties for the process flow are displayed in a new tab.

2. Expand the User Defined Activities node, then the User Defined activity, and the Path Settings node. Oracle Warehouse Builder displays the configuration settings.

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

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

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

5. Under the Execution Settings node, set Use Return as Status to true.

   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, if the external process returns a success value, the process flow continues down the success transition and runs 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 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.

Run the process flow manually. When you run the process flow, Oracle Warehouse Builder prompts you to enter values for the parameter that you created to pass into the script, FILE_STRING. For this case study, enter ?sales where the question mark is the separator. The external activity then runs the command rename_file.bat sales.

Subsequent Steps

After you successfully run the process flow manually, consider creating a schedule.You can define a daily schedule to run the process flow and, therefore, the mapping. Use schedules to plan when and how often to run operations such as mappings and process flows that you deploy through Oracle Warehouse Builder.

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. Oracle Warehouse Builder requires these definitions for deploying and running the process flow. When you deploy the process flow, Oracle 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 Locations Navigator 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.

For the remote location, enter the host name, root path to the file, user name, and password. Oracle Warehouse Builder keeps the password secure. For the local location, only the host name is necessary.

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 8-7.

Figure 8-7 Process Flow with FTP Transitioning to a Mapping

Description of "Figure 8-7 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 entering values for the FTP activity parameters on the Activity View.

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

• Method 1: Write a script in Oracle Warehouse Builder: Choose this method when you want to maintain the script in Oracle Warehouse Builder 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, enter the path to the FTP executable, such as c:\winnt\system32\ftp.exe. Also, enter the Task.Input variable into the Value column of the PARAMETER_LIST parameter.

• Method 2: Call a script maintained outside of Oracle Warehouse Builder: If password security is not an issue, you can direct Oracle 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, enter the appropriate command in PARAMETER_LIST to direct Oracle Warehouse Builder to the file. For a Windows operating system, enter 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, enter the following:

  ?"-s:c:\temp\move.ftp"?

  Leave the SCRIPT parameter blank for this method.

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. Oracle 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, if FTP returns a success value of 1, the process flow continues down the success transition and runs 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.

Now you can run the process flow.