| Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide 11g Release 2 (11.2) E10935-05 |
|
|
PDF · Mobi · ePub |
| Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide 11g Release 2 (11.2) E10935-05 |
|
|
PDF · Mobi · ePub |
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 enable activities to be linked to define flow of control among different activities. Supported flow of control constructs include conditional branches, loops, parallel flows or serial dependencies. Activities can be mappings, transformations, or external commands such as e-mail, 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 "Defining Schedules".
This chapter contains the following topics:
A process flow describes dependencies between Oracle Warehouse Builder mappings and external activities such as e-mail, FTP, and operating system commands. Use process flows to sequence individual steps in the ETL process. The individual steps often include mappings, but can also include manual activities or external activities such as FTP or e-mail.
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 8-1 shows an example of a process flow that starts a mapping (MAP1). If the mapping completes successfully, then Oracle Warehouse Builder sends an e-mail notification (EMAIL_SUCCEED) and starts another process flow (SUBPROC1). If the mapping fails, then Oracle Warehouse Builder sends an email (EMAIL_FAIL) and ends the process flow.
When you design a process flow in Oracle Warehouse Builder, you use an interface known as the Process Flow Editor. Alternatively, you can create and define process flows using Oracle Warehouse Builder scripting language, OMB*Plus, as described in Oracle Warehouse Builder API and Scripting Reference.
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.
The cube SALES_CUBE is loaded using data in the PRODUCTS, CATEGORIES, and CUSTOMERS dimensions. These dimensions, in turn, are loaded using one or more transaction tables. Data must be loaded into the cube only if all the dimensions are loaded successfully.
You create separate mappings to load the cube and dimensions. However, you want a sequential flow in which the three dimensions PRODUCTS, CATEGORIES, and CUSTOMERS are loaded first and, if these dimensions are loaded successfully, the cube SALES_CUBE is loaded. Use a process flow to link these mappings and create a sequential flow.
Figure 8-2 displays the process flow that loads the SALES_CUBE. This process flow is created after you complete the steps listed in "Steps to Define the Process Flow".
Figure 8-2 Process Flow that Loads a Cube
Create the following mappings:
LOAD_PRODUCTS: This mapping transforms source data stored in transaction tables and loads transformed data into the PRODUCTS dimension.
LOAD_CATEGORIES: This mapping transforms source data stored in transaction tables and loads transformed data into the CATEGORIES dimension
LOAD_CUSTOMERS: This mapping transforms source data stored in transaction tables and loads transformed data into the CUSTOMERS dimension
LOAD_SALES_CUBE: This mapping loads data into the cube SALES_CUBE, using the dimensions PRODUCTS, CATEGORIES, and CUSTOMERS.
Steps to Define the Process Flow
Create an Oracle Workflow location to which the process flows are deployed.
See "Creating Oracle Workflow Locations" for information about creating Oracle Workflow locations.
In the Projects Navigator, expand the project node under which you want to create the process flow and then expand the Process Flows node.
Right-click the Process Flow Modules node and select New Process Flow Module.
The Create Process Flow Module wizard is displayed.
On the Name and Description page, provide a name and an optional description for the process flow.
On the Connection Information page, in the Location field, select the location created in step 1. Click Finish to create the process flow module.
The Create Process Flow Package dialog box is displayed.
Enter a name and an optional description for the process flow package and click OK.
The Create Process Flow dialog box is displayed.
Enter the name and an optional description for the process flow and click OK.
The Process Flow Editor is displayed. The editor canvas contains the Start activity named START1 and a Stop activity called End_success. Use the editor to add other activities that are part of your process flow and to define relationships between them.
From the Component Palette, drag a Fork activity and drop it on to the editor canvas.
From the Projects Navigator, drag the following mappings and drop them on to the editor canvas: LOAD_PRODUCTS, LOAD_CATEGORIES, and LOAD_CUSTOMERS.
When you drag a mapping and drop it onto the canvas, the mapping activity is displayed on the canvas with a default name such as MAPPING_n. The activity name highlighted in blue so that you can change the name, if required. For each activity, enter the same name as the mapping. For example, for the LOAD_PRODUCTS mapping, enter the name of the activity as LOAD_PRODUCTS.
Position these activities in a vertical line, one below the other, to the right of the Fork activity.
Create the following transitions:
From the Fork activity to the LOAD_PRODUCTS activity
From the Fork activity to the LOAD_CATEGORIES activity
From the Fork activity to the LOAD_CUSTOMERS activity
To create a transition, select the source activity. The cursor is displayed as a small horizontal arrow on the activity. Drag and drop on the target activity.
From the Component Palette, drag and drop an AND activity on to the editor.
Define a conditional transition, with the condition defined as SUCCESS, from the LOAD_PRODUCTS activity to the And activity.
A conditional transition is one that is based on a predefined condition. To define a condition for the transition:
On the editor canvas, select the transition.
The Property Inspector displays the properties of the selected transition.
Click the Ellipsis button on the Condition property.
The Edit Property dialog box is displayed.
Select Enumerated Condition. In the list below this option, select Success and click OK.
Define a conditional transition, with the condition defined as SUCCESS, from the LOAD_CATEGORIES activity to the And activity.
Define a conditional transition, with the condition defined as SUCCESS, from the LOAD_CUSTOMERS activity to the And activity.
From the Projects Navigator, drag and drop the mapping LOAD_SALES_CUBE onto the editor canvas. Enter the name of the activity as LOAD_SALES_CUBE.
Create a transition from the AND activity to the LOAD_SALES_CUBE activity. Select the AND activity to display a small horizontal arrow. Drag and drop on to the LOAD_SALES_CUBE activity.
Create a conditional transition, with the condition defined as SUCCESS, from the LOAD_SALES_CUBE activity to the End_success activity.
To enable deployment of process flows, install Oracle Workflow as described in the Oracle Warehouse Builder Installation and Administration Guide.
To define a process flow, refer to the following sections:
(Optional) "Creating Oracle Workflow Locations"
Validating and Generating Process Flows
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 "Defining Schedules".
Deploying Process Flows, see "Steps in the Deployment and Execution Process".
Use an Oracle Workflow location to deploy process flows. This location corresponds to the Oracle Workflow schema.
To create an Oracle Workflow location:
In the Locations Navigator, expand the Locations node and then the Process Flows and Schedules node.
Right-click the Oracle Workflow node and select New Oracle Workflow Location.
The Create Oracle Workflow Location dialog box is displayed.
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.
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:
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.
Click Next.
On the Name and Description page, type a module name that is unique within the project. Enter an optional text description.
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.
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.
Within a process flow module, you can create user folders to group process flow packages based on criteria such as product line, functional groupings, or application-specific categories.
User folders can contain other user folders and other process flow packages. There is no limit on the level of nesting of user folders. You can also move, delete, edit, or rename user folders. To move a user folder, select the user folder in the Projects Navigator and click Cut on the toolbar. Then, select the process flow module into which the user folder is to be moved and click Paste.
You can move process flow packages that are contained in a user folder either to the corresponding parent process flow module or to another process flow module.
Deleting a user folder removes the user folder and all its contained objects from the repository.
To create a user folder within a process flow module:
In the Projects Navigator, expand the Process Flows node. Right-click the Process Flow module or user folder under which you want to create a user folder and select New.
The New Gallery dialog box is displayed.
In the Items section, select User Folder.
The Create User Folder dialog box is displayed.
Specify a name for the user folder and click OK.
The user folder is created and added to the tree under the Process Flow module.
To create a process flow package within a user folder:
In the Projects Navigator, expand the Process Flows node. Right-click the user folder and select New.
The New Gallery dialog box is displayed.
In the Items section, select Process Flow Package.
Click OK.
The Create Process Flow Package dialog box is displayed. Use this dialog box to create a process flow. Subsequently, create the required process flows within this process flow package.
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:
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.
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.
Click OK.
Oracle Warehouse Builder prompts you to create a process flow.
After you create a module and package for process flows, you can create a process flow.
To create a process flow:
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.
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.Click OK.
Oracle Warehouse Builder runs the Process Flow Editor and displays the process flow with a Start activity and an End_Success activity.
You can now model the process flow with activities and transitions.
Continue with the steps listed in "Steps for Defining Process Flows".
You can add activities in a process flow by using the Projects Navigator.
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".
To add an activity to a process flow:
Open the process flow by right-clicking the process flow in the Projects Navigator and selecting Open.
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.
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.
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.
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
Each parameter has the following properties:
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".
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.
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.
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.
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.
Table 8-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 |
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.
You can enter an optional description for each property.
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 e-mail to the same group of administrators. You create a template for the Email activity, and then use and edit the activity in many process flows.
To create an activity template:
In the Projects Navigator, navigate to the Activity Templates node under the Process Flows node.
To create a folder for containing templates, right-click the Activity Templates node and select New Activity Template Folder.
Assign a name to the activity template folder and click OK.
Consider creating a folder for each type of template that you plan to create. For instance, you could create separate folders to contain Email and Ftp templates.
The Create Activity Template Wizard is displayed.
Note:
If the wizard does not appear automatically, then right-click a folder and select New Activity Template.Follow the prompts in the Create Activity Template Wizard to complete the "Name and Description Page", the "Parameters Page", and the Summary page.
See "Using Activity Templates" for instructions about how to use the template in a process flow.
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.
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.
The description can be up to 4,000 alphanumeric characters and can contain blank spaces. Specifying a description for an activity template is optional.
The following activity templates are available from the list.
Assign
Enterprise Java Bean
FTP
File Exists
Java Class
Manual
Notification
OMBPlus
Set Status
Sqlplus
User Defined
Wait
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.
Complete the following steps to use an activity template:
In the Projects Navigator, navigate to the process flow module under the Process Flows node.
To open the Process Flow Editor, right-click the Process Flow and select Open.
From the Graph menu, select Add, then Available Objects.
The Add Available Objects dialog box is displayed.
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.
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.
When you add a transition to the canvas, by default, the transition has no condition applied to it. The process flow continues after 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 which 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).
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."
To create dependencies using transitions:
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.
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.
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.
Some activities, such as Sqlplus, require additional configuration. These configuration details for a given activity are listed in Chapter 27, "Activities in Process Flows".
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
Process flows adhere to the following rules for enabling the data to be passed between data stores:
Process flow variables can be initialized from process flow parameters, but the reverse is not enabled.
Activity parameters can pass data bidirectionally between process flow variables and process flow parameters.
Transition expressions can be evaluated against their source activity parameters, process flow parameters, and process flow variables.
A data store cannot be accessed from another data store within the same scope.
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.
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:
All the data is passed within the process flow by value.
Variables can be initialized through a binding. They cannot return a value.
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.
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 has access to the outer scope that encloses it. So, an expression for an activity parameter can use process flow variables and process flow parameters in its evaluation.
The PL/SQL expression is run for of the Control Center user who requested the process of the activity. However, if the Oracle Workflow schema is hosted in a remote database instance, the effective user of the generated database link is used instead. A different Control Center user may be selected by configuring the process flow and specifying an evaluation location. Thus, the expression may reference any PL/SQL function that is accessible to the Control Center user.
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-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 |
An integer, 0 to 255, specific to the 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 8-3 lists the additional constants provided.
Use the Transition Editor to specify one of the enumerated conditions or to 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 8-4.
Table 8-4 Types of Conditions for Transitions
| Icon | Transition Condition | Description |
|---|---|---|
 |
Success |
The process flow continues only if the preceding activity ends in success. |
 |
Warning |
The process flow continues only if the preceding activity ends with warnings. |
 |
Error |
The process flow continues only if the preceding activity ends in error. |
 |
Complex |
The process flow continues only if the preceding activity returns a value that meets the criteria you specify in an expression. |
 |
Extended |
The process flow continues only if the preceding notification activity ends with an extended result. |
The extended transition condition is valid only for Notification activities, because this is the only type of activity that returns an extended result. The activity acquires this icon when it is set to an outcome of #MAIL, #NOMATCH, #TIE, or #TIMEOUT.
Table 8-5 lists the output and the description of the Extended transition.
Table 8-5 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. |
|
|
A mail error occurred for the notification. Some recipients did not receive an e-mail notification, 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 8-4 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 multiple outgoing transition is true.
Figure 8-5 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, then two transition conditions would be true and the process flow would be invalid.
Figure 8-5 Outgoing Transition Conditions
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.
In Oracle Warehouse Builder, you can design a process flow that locates the generated file in a specific directory, renames it to a permanent name that you designate, and starts a dependent mapping. You can now use the permanent flat file name as the source for your mapping.
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 a User Defined activity. Assume the following information for the purposes of this case study:
Generated Flat File: The legacy system generates a flat file containing sales data on a daily basis. It saves the file to the c:\staging_files directory and names the file based on the time and date, such as sales010520041154.dat. Every generated file is saved to the same directory and begins with the word sales, followed by the timestamp information.
Permanent Flat File Name: You decide to rename the generated file name to s_data.dat. This is the name that you reference as the flat file source in the mapping.
Process Activity: You design a process flow named OWF_EXT to run batch commands in DOS to copy the generated file, save it as s_data.dat, and delete the originally generated file.
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:
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
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:
Choose this method when you want to maintain the script in Oracle 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, enter 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.
Although this case study does not illustrate it, you can use substitution variables in the script when you maintain it in Oracle Warehouse Builder. This prevents you from having to update activities when server files, accounts, and passwords change.
Table 8-6 lists the substitute variables that you can type for the User Defined 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 which is local when you configure the activity, as described in "Configuring the User Defined Activity". These values are set when you register the locations at deployment.
Table 8-6 Substitute Variables for the User Defined 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 |
If extra maintenance is not an issue, you can point Oracle 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 Oracle 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 User Defined activity:
Write the script and save it to 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.dat
del c:\staging_files\%1*.dat
This sample script passes 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.
Select the Start activity on the canvas to view and edit activity parameters in the Structure view.
To add a start parameter, select the Start activity on the canvas, and click Add New Activity Parameter on the Structure tab. Create a start parameter named FILE_STRING. During execution, Oracle Warehouse Builder prompts you to type a value for FILE_STRING to pass on to the %1 parameter in the rename_file.bat script.
Select the User Defined activity on the canvas and edit its parameters.
For the COMMAND parameter, enter 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, enter c:\staging_files\rename_file.bat.
For PARAMETER_LIST, click the row labeled Binding and select the parameter that you defined for the start activity, FILE_STRING.
Accept the defaults for all other parameters for the external process.
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:
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.
Expand the User Defined Activities node, then the User Defined activity, and the Path Settings node. Oracle Warehouse Builder displays the configuration settings.
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.
Set the Deployed Location to the computer where you deploy the process flow.
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.
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.
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.
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.
See Also:
"Defining Schedules" for information about defining schedules.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.
In Oracle Warehouse Builder, you can design a process flow that runs 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 hosts 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.
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.
Data host computer: For the computer hosting the source data, you need a user name and password, host name, and the directory containing the data. In this case study, the computer hosting the data is a UNIX server named salessrv1. The source data is a flat file named salesdata.txt located in the /usr/stage directory.
Runtime Service host computer: In this case study, Oracle Warehouse Builder and the Runtime Service are installed on a computer called local with a Windows operating system. local runs the mapping and the process flow.
Mapping: This case study includes a mapping called salesresults that uses a copy of salesdata.txt stored on local at c:\temp as its source.
FTP Commands: This case study illustrates the use of a few basic FTP commands on the Windows operating system.
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, see the following sections:
After you complete the instructions in the above sections, you can run the process flow.
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.
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
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.
The following example illustrates Method 1. 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:
Select the FTP activity on the canvas to view and edit activity parameters in the Property Inspector.
For the COMMAND parameter, enter 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.
For the PARAMETER_LIST parameter, enter the Task.Input variable.
When defining a script in Oracle Warehouse Builder and using Windows FTP, you must enter ?"-s:${Task.Input}"? into PARAMETER_LIST.
For UNIX, enter the following: ?"${Task.Input}"?.
Navigate to and highlight the SCRIPT parameter in the Structure tab.
The Property Inspector displays the properties of the SCRIPT parameter.
Click the Ellipsis button to the right of the Value field displayed in the Property Inspector.
Oracle Warehouse Builder displays the SCRIPT Value editor. Write or copy and paste FTP commands into the editor.
Notice that the script in Figure 8-8 includes ${Remote.User} and ${Remote.Password}. These are substitution variables. See "Using Substitution Variables" for more details.
Figure 8-8 SCRIPT Value Editor Using Substitution Variables
Substitution variables are available only when you choose to write and store the FTP script in Oracle Warehouse Builder.
Use substitution variables to prevent having to update FTP activities when server files, accounts, and passwords change. For example, suppose that you create 10 process flows that use FTP activities to access a file on salessrv1 under a specific directory. If the file is moved, then, 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 Oracle Warehouse Builder runs an FTP activity with substitution variables for the server passwords, it resolves the variable to the secure password that you provided for the associated location.
Table 8-7 lists the substitute variables that 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 which is local when you configure the FTP activity. For more information, see "Configuring the FTP Activity".
Table 8-7 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 |
As part of configuring the complete process flow, configure the FTP activity.
To configure the FTP activity:
Right-click the process flow on the navigation tree and select Configure.
Expand the FTP activity and the Path Settings. Oracle Warehouse Builder displays the configuration settings.
Set Remote Location to REMOTE_LOCATION and Working Location to LOCAL_LOCATION.
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.
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.
|
 Copyright © 2000, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
