Creating an ODI Error Hospital That Uses BPEL Human Workflow

Purpose

This tutorial walks you through the steps that are needed to create an ODI Error Hospital using BPEL Human Workflow to notify users of rejected records. The users can then edit and update these records within Human Workflow. The updated records are recycled on the next execution of the ODI scenario and if the record passes the constraints, it is loaded to the target and deleted from the Error Hospital.

 

Time to Complete

Approximately 1 hour and 30 minutes

Topics

This OBE tutorial covers the following topics:

  Overview

Scenario

 Examples
 Verifying the Prerequisites
 Example 1: Developing an ODI XML to DB Transformation Using an Interface with ODI Constraint and CKM
 Example 2: Developing a BPEL Process for Invoking ODI from SOA and Generating Human Workflow Tasks
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

The screenshots will not reflect the specific environment that you are using. They are provided to give you an idea of where to locate specific functionality in Oracle Data Integrator.

Overview

For enterprise SOA deployments, there is almost always a need to for enterprise data extraction, loading, transformation, and validation. By leveraging the native SOA architecture within Oracle Data Integrator (ODI), you can take advantage of Oracle Data Integrator to perform the ELT. Oracle Data Integrator provides the ability to validate data during the load to the target using ODI constraints or database constraints. When this data is checked against a constraint using Flow Control, any errors that are found are not loaded to the target but are loaded to an errors table that is created and managed by ODI. Each row of this table represents a record that did not pass a constraint; the row also has a message column that explains why the record was rejected. This table can be edited within ODI Designer or any other tool that can edit relational tables. However, this is not always a convenient way for the end user or business user to edit the data. This example walks you through the steps to build an ODI scenario that will load an XML file with a constraint. The constraint will only allow data to be loaded where client age is >= 21. Any rows that do not pass the constraint will be loaded to a table on the target database – E_CLIENT. The ODI Scenario will be executed, and after the ODI ELT process is completed, the ODI Scenario will then call back to a BPEL Web service to notify of any errors during the load. The BPEL process will import the errors and manage them using BPEL Human Workflow tasks. A user can then use BPEL Worklist to update bad records. On subsequent executions of the ODI Scenario, the updated records are recycled into the ELT process. The records that pass the constraint are loaded into the target, and the error record is in the E_CLIENT table. Because ODI manages the E_CLIENT table, we do not have to write any code to recycle the errors or to delete the updated records when they are loaded successfully.

 

 

Back to Topic List

Scenario

Linda works as a database administrator for Global Enterprise. In Global Enterprise, Linda is responsible for performing database management and integration tasks on various resources within the organization. In particular, Linda is responsible for data loading, transformation, and validation that can be used within a BPEL process. She needs to build an ODI Scenario that will validate data during the load to the target using ODI constraints or database constraints. This ODI Scenario will then call back to a BPEL Web service to notify of any errors during the load. Linda needs to create a BPEL process that will import the errors and create BPEL Human Workflow tasks. A user can then use BPEL Worklist to update records that contain such errors.

Examples

In this OBE, students learn the scenario through two examples.

Example 1: Developing an ODI XML to DB Transformation Using an Interface with ODI Constraint and CKM
This example walks you through the steps needed to create an ODI interface that imports an XML file into a database table CLIENT. An ODI constraint is added to the CLIENT model. Any records that do not pass the constraint are loaded to the E_CLIENT table. The BPEL process will then use this table to generate Human Workflow tasks.

Example 2: Developing a BPEL Process for Invoking ODI from SOA and Generating Human Workflow Tasks
This example walks you through the steps needed to create the BPEL process that imports the data errors from the E_CLIENT table. The BPEL process will then create Human Workflow tasks from the error records.

Back to Topic List

Verifying the Prerequisites

Before you start the tasks, make sure that your system environment meets the following requirements:

Software Requirements

The system should include the following installed products:

The system should include the following configuration:

  • odi-public-ws.aar must be deployed in Axis2. Use the Upload Service link on the Axis2 admin page to upload this file (download from here).
  • The ODI Master and Work repository must be created. You can refer to the steps from here.
  • The xml, xtd, and csv files for the examples must be available. For example, the GEO_DIM.xml file that is included with this sample has much data and is approximately 8 MB to demonstrate using a moderately sized XML file with ODI and BPEL. You can download the complete set of demo/xml files from here.
  • A schema ( ODI_STAGE) must be created for the target tables of the exercise and a data server and physical schema must be available for this schema within the ODI Topology Manager. To do this, perform the following steps:
    • Start the Oracle Database 10g XE Administrative Console.
    • Log in using system as the Username and oracle1 as the Password.
    • From the Enterprise Manager Console, select Administration > Database Users > Create Users.
    • Provide the Username as ODI_STAGE and Password as oracle1.
    • Select the DBA check box and click Create. Note that the ODI_STAGE has been created.
    • Select Start > All Programs > Oracle > Oracle Data Integrator > Topology Manager to open ODI Topology Manager.
    • Scroll down to the pane at the bottom left and click the the Physical Architecture tab.
    • At the top-left corner, under the Physical Architecture pane, click " +" to expand Technologies > Oracle.
    • Right-click Oracle and select Insert Data Server.
    • The Data Server: new window appears. Enter the name as Local_XE, Instance/dblink (Data Server) as XE, User as ODI_STAGE, and Password as oracle1. Next, click the JDBC tab.
    • For the JDBC Driver value, click browse and select Oracle JDBC Driver from the Name drop-down list. Click OK.
    • For the JDBC Url, click browse and select jdbc:<hostname>:<port>:<sid>, and then click OK. Populate the values as per your environment details.
    • Click Test, Test again, and then OK.
    • Click OK in the Data server: new window.
    • Under the Physical Architecture pane, click " +" to expand Technologies > Oracle > Local_XE.
    • In the Physical Schema: Local_XE.Schema window, under the Definitions tab, select ODI_STAGE from the Schema (Schema) drop-down list and ODI_STAGE from the Schema (Work Schema) drop-down list.
    • Click the Context tab.
    • Click Add context. Select Global from the Context drop-down list and enter the Logical schema as ODI_STAGE.
    • Click OK.

    If not done before, start the services and components for Database 10g XE, Oracle Data Integrator 10g (10.1.3.4), and Oracle SOA Suite 10g.

Back to Topic List

Example 1: Developing an ODI XML to DB Transformation Using an Interface with ODI Constraint and CKM

This example walks you through the steps needed to create an ODI interface that will import an XML file into a CLIENT database table. An ODI constraint will be added to the CLIENT model. Any records that do not pass the constraint will be loaded to the E_CLIENT table. The BPEL process will then use this table to generate Human Workflow Tasks.

 Creating a New Project in ODI
 Inserting a New Model for the XML File
 Inserting a Model for the CLIENT Relational Table
 Deleting the "Copy of CITY_CLIENT" Constraint
 Setting Up an ODI Constraint on the CLIENT Data Store
 Importing the Knowledge Modules to Load, Check, and Integrate the XML File to RDBMS
 Creating a New Interface to Load the XML File to the RDBMS Target
 Executing and Testing the Interface
 Viewing the Constraint Errors from Interface Execution

 

Creating a New Project in ODI


A project is a collection of ODI objects that have been created by users. It should encompass a single functional domain. Each project contains its own knowledge modules, variables, markers, and other types of objects. These objects can be shared between different projects by duplicating them. You can also use global objects such as variables or sequences to define parameters that are common to all projects.

To create a new project in ODI, perform the following steps:

1.

Click Start > All Programs > Oracle > Oracle Data Integrator > Designer to open ODI Designer.

 

2.

The Oracle Data Integrator Login screen appears. From the Login Name drop-down list, select Repo_designer.

 

3.

The default User name and Password are preset as SUPERVISOR and SUNOPSIS. Click OK to login.

 

4.

In the pane at the bottom left, click the Projects tab.

 

5.

Note that the Projects pane appears at the top-left corner. Right-click to select Insert Project.

 

6.

The Project: New window appears. Enter the name as ODI_ER_HSP. Note that the Code value is also populated with the same text. Then, click OK to view the new project that is populated in the Projects pane.

 

 

Back to Topic List

Inserting a New Model for the XML File

To insert a new model for the source XML file, perform the following steps:

1.

In ODI Designer, scroll to the pane at the bottom left and click the Models tab.

 

 

2.

Note that the Models pane appears at the top-left corner. Right-click and select Insert Model.

 

3.

In the Model: New window, you need to enter the values for the new model. For this, provide the following parameters, and then click the Reverse tab.

Parameter Value
Name SRC_GEO_DIM_XML
Technology XML
Logical Schema XML_DEMO_GEO

 

4.

Under the Reverse tab, select Global from the Context drop-down list. Scroll down, and then click Reverse. This generates the XML source.

 

 

5.

Click Yes for confirmation, and then click OK.

 

6.

In the Models pane, you can note the new data store being populated. Click the " +" sign to expand and view the SRC_GEO_DIM_XML model.

 

Back to Topic List

Inserting a Model for the CLIENT Relational Table

Note: A relational logical schema is needed for this step. The ODI_STAGE schema was set up within Oracle XE database. A new physical schema was also set up within the Topology Manager for the ODI_STAGE schema. Due to an issue with BPEL handling table names with the $ symbol, you must change the prefixing of the internal error table that ODI will create.

 

To insert a model for the CLIENT relational table, perform the following steps:

1.

Start Topology Manager: Start > All Programs > Oracle > Oracle Data Integrator > Topology Manager. The default User name and Password are preset as SUPERVISOR and SUNOPSIS. Click OK to login.

 

2.

Click the Physical Architecture button. Expand Technologies > Oracle > Local_XE. Right-click the physical schema for your target ( Local_XE.ODI_STAGE) and select Edit. Change the value of the Errors field from E$ to E_. Click OK.

 

.

 

3.

Click the Models tab within Designer. Right-click and select Insert Model.

 

4.

Enter the parameters from the following table. Click the Reverse tab. Select the Global context. Don't click Reverse. Click OK and the new data sources will be added in the next step.

Parameter Value
Name RDBMS_TARGET
Technology Oracle
Logical Schema ODI_STAGE

 

 

5.

You create the CLIENT data store within the RDBMS target by copying from the XML model. Select and expand the SRC_GEO_DIM_XML model. Right-click the CLIENT table and select Duplicate. Click Yes on the " Duplicate CLIENT?" dialog box.

 

6.

Drag the new duplicate “Copy of CLIENT” from the XML model to the RDBMS TARGET model. Right-click the “Copy of CLIENT” data store within the RDBMS Target and select Edit. Edit the name to CLIENT. Click OK.

Note: No physical table is created on RDBMS_TARGET. When the Interface is run, it creates the CLIENT physical table.

 

 

Back to Topic List

Deleting the "Copy of CITY_CLIENT" Constraint

During the duplication of the CLIENT data store, the constraint for the XML file was also copied. This constraint should be deleted.

1.

Expand the CLIENT data store within RDBMS_TARGET. Expand Constraints.

 

 

 

2.

Right-click the " Copy of CITY_CLIENT" constraint and click Delete. Click Yes in the Confirmation dialog box.

 

Back to Topic List

Setting Up an ODI Constraint on the CLIENT Data Store

To set up the ODI constraint on the CLIENT data store, perform the following steps:

1.

Expand the CLIENT data store within RDBMS_TARGET. Right-click Constraints and select Insert Condition.

 

 

2.

Set the parameters for this condition as shown in the following table. The constraint should look as follows. Click OK to save.

Note: On first execution of the interface, the SQL will not validate until the table is created.

Parameter Value
Name AgeConstraint
Type Oracle Data Integrator Condition
Where cast(Client.age as numeric)>=21
Message Client must be at least 21 years old.

 

Back to Topic List

Importing the Knowledge Modules to Load, Check, and Integrate the XML File to RDBMS

Before the interface is created, the Load Knowledge Modules (LKM) and Integrate Knowledge Modules (IKM) that are needed to load and integrate the XML data need to be imported into the project. To import the IKM, LKM SQL to SQL, and the Check Knowledge Modules (CKM), perform the following steps:

1.

Click the Projects tab within Designer. Expand the “ Knowledge Modules” node. Right-click the “ Loading (LKM)" node and select “ Import Knowledge Modules.” Browse to the IMPEXP folder in the directory where ODI is installed, for example I:\ODI\oracle\impexp. Select LKM SQL to SQL. Click OK.

 

 

2.

Right-click the “ Integration (IKM)" node and select “ Import Knowledge Modules.” Browse to the IMPEXP folder of ODI Install ( I:\ODI\oracle\impexp). Select IKM SQL Control Append. Click OK.


 

3.

Right-click the “ Check (CKM)" node and select “ Import Knowledge Modules." Browse to the impexp folder in the directory where ODI is installed (for example, I:\ODI\oracle\impexp). Select CKM SQL. Click OK.

4.

Verify that your “ Knowledge Modules” node looks as shown below.

Back to Topic List

Creating a New Interface to Load the XML File to the RDBMS Target

To create a new interface to load the XML file to the RDBMS target, perform the following steps:

1.

On the Projects tab within ODI Designer, expand the First Folder node. Right-click the Interfaces node and select Insert Interface. Name the Interface INT_LOAD_TBL_CLIENT. Select Sunopsis_Memory_Engine from the Staging Area Different From Target field. Click the Diagram tab.

 

 

2.

Click the Models tab to drag and drop the source and target to the Interface. Drag the Client data store from SRC_GEO_DIM_XML into the Sources container. Drag the Client data store from RDBMS target into the Target container. When Designer asks “Do you want to perform and Automatic Mapping?” click Yes. The Diagram tab should look as shown below.


 

3.

Click CLIENT_ID in Target Datastore. Scroll down the " Mapping CLIENT_ID" section and select the Key box to set the CLIENT_ID column to be the key. Click the Flow tab.

4.

Click Client SS_0 (1-XML_GEO_DIM) and set the LKM option to LKM SQL to SQL. Similarly click Client Target (Local_XE). Set the IKM for Client Target (Local_XE) to IKM SQL Control Append. In the Target Local_XE section, set Options as provided in the following table. Click the Controls tab.

Option Value
RECYCLE_ERRORS YES
CREATE_TARG_TABLE YES
DELETE_ALL YES

 

5.

On the Controls tab, leave the default values as shown. Click Apply. The INT_LOAD_TBL_CLIENT interface is now set up.

Back to Topic List

Executing and Testing the Interface

To execute and test the NT_LOAD_TBL_CLIENT interface, perform the following steps:

1.

Click the Execute button. Leave the execution settings as default. Click OK. When the Information dialog box indicates that " Session started," click OK.

 

 

2.

View the execution results in ODI Operator. To start ODI Operator, select All Programs > Oracle > Oracle Data Integrator > Operator. Log in as Repo_designer using SUPERVISOR as the Username and SUNOPSIS as the Password. In the pane at the bottom left, click the Hierarchical Sessions tab. Locate the INT_LOAD_TBL_CLIENT session and verify that it was executed successfully as shown below.


 

Back to Topic List

Viewing the Constraint Errors from Interface Execution

To view the constraint errors, perform the following steps:

1.

Right-click the CLIENT datastore within RDBMS_TARGET and select Control > Errors. View the rows with the constraint errors. The E_CLIENT table on the target schema can now be updated. On subsequent executions of the interface, the rows from the E_CLIENT table are recycled by ODI, and if the rows now pass the constraints, they are loaded to the target.

Note: After creating the BPEL process in Exercise 2, we will create a package to execute the INT_LOAD_TBL_CLIENT interface. This package will also call the BPEL process to notify of the data errors, thereby allowing BPEL to create the Human Workflow Tasks.

 

Back to Topic List

 

Example 2: Developing a BPEL Process for Invoking ODI from SOA and Generating Human Workflow Tasks

This example walks you through the steps needed to create the BPEL process that will import the data errors from the E_CLIENT table. The BPEL process will then create the Human Workflow Tasks from the error records. In the next steps, we will build a new BPEL process that will track the errors for each execution of the ODI package and present them to a user for review. The user will be able to update each of the fields and correct any errors so that on the next execution of the ODI package, the corrected rows are inserted into the target. JDeveloper is the tool that we will use to build the BPEL process and deploy it to the application server.

 

 Creating a New Application for the BPEL Process
 Creating a New Project for the BPEL Process
 Connecting to the ODI Data Source
 Collecting Errors from the Error Table
 Invoking the Read Errors Database Adapter
 Writing the Error Corrections Back to the Error Table
 Invoking the Error Corrections Database Adapter
 Adding Human Workflow to Handle the Errors
 Passing the Updated Values into the Write_Corrections Database Adapter
 Deploying the Error Hospital BPEL Process
 Creating the ODI Package
 Reviewing the BPEL Process Steps from the BPEL Console and Completing the Process

 

Creating a New Application for the BPEL Process


To create a new application for the BPEL process, perform the following steps:

1.

Start JDeveloper by clicking jdeveloper.exe in G:\jdevStudio10131. Alternatively, use the SOA Suite Jdev shortcut on the desktop. In the Applications Navigator on the left, right-click Applications and select New Application.

 

2.

Name the application ODI_ErrorHsptl, and then click OK. Click Cancel in the Create Project dialog box. Your new application will appear in the Application Navigator.

 

Back to Topic List

Creating a New Project for the BPEL Process

To create a new project for your BPEL process, perform the following steps:

1.

Right-click your newly created application ODI_ErrorHsptl, and then select New project. Select BPEL Process Project from the Projects category. Click OK.

 

 

2.

In the BPEL Project Creation Wizard, name your BPEL process Client_ErrorHsptl, select Synchronous BPEL Process from the drop-down list, and then click Finish. Your new project appears in the Application Navigator and the Client_ErrorHspt.bpel file opens in the JDeveloper window.

 

Back to Topic List

Connecting to the ODI Data Source


This procedure is the basis for your ODI process. It should be set up to be as dynamic as possible using options and variables. To create the new procedure ImportXFormExportXML, perform the following steps:

1.

Select Connection Navigator from View. It should appear at the top left of your screen. Double-click the Database folder to open the Database Connection. Click Next on the Welcome page.

 

 

2.

In Step 1 of 4, enter ODI_DB as the connection name and select Oracle (JDBC) as the Connection Type (select the correct connection type if you are not using Oracle XE). Click Next.

.

 

 

3.

In step 2 of 4, enter the Username and Password to connect to the schema that you used earlier to create the CLIENT table. Enter ODI_STAGE in the Username field. Enter the correct password ( oracle1) for the ODI_DATA schema. Click Next.

4.

In Step 3 of 4, change the SID to XE and leave all the others as default. Click Next. On the last screen, click Test Connection. The Status result should show " Success!" Click Finish.

Note: If the status does not show "Success!" verify your settings.

Back to Topic List

Collecting Errors from the Error Table

To collect errors from the Error table, perform the following steps:

1.

From the Component Palette on the right, select Services from the drop-down list. Drag the Database Adapter partner link onto the gray Services swim lane to the right of your process diagram. The Database Adapter wizard appears. Click Next on the Welcome page. In the Service Name field, enter Read_Errors. Click Next.

 

 

 

2.

From the Connections drop-down list, select ODI_DB (or the ODI Database connection that you created earlier). Click Next.

 

3.

For Operation type, select Perform an Operation on a Table. Deselect all check boxes except Select . Click Next.

4.

Click the Import Tables button. In the Import Tables window, click the Query button at the top right. Move the error table E_Client to the Selected box. Click OK. Click Next.

Note: If you see an E$_Client table instead of the E_Client table, you need to reconfigure the ODI prefixing for the Error table within the Topology Manager (see details in the Insert Model for the CLIENT Relational Table section).

 

 

 

5.

On the Define Primary Keys page, select CLIENT_ID. Click Next. On the Relationships page, click Next.

6.

On the Object Filtering page, deselect the errType, origin, and consType check boxes. Click Next. Click Next on the Define Selection Criteria page. Click Finish.

7.

On the Create Partner Link Wizard screen, retain the defaults and click OK. Your process diagram should match the following image:

Back to Topic List

Invoking the Read Errors Database Adapter

To invoke the Read Errors database adapter, perform the following steps:

1.

From the Component Palette on the right, select Process Activities from the drop-down list. Select the Invoke activity and drag it onto your diagram below the replyOutput activity.

2.

Drag the arrow to the right of the Invoke activity onto the Read_Errors partner link in the swim lane at the right. The Edit Invoke window appears. Name the activity Invoke_ReadErrors. Click the Magic Wand icon next to the Input Variable field. In the Edit Invoke window, name the variable LoadErrors_InputVariable. Click OK.

 

 

3.

Click the Magic Wand icon next to the Output Variable field. In the Edit Invoke window, name the output variable LoadErrors_OutputVariable. Click OK. Click OK again.

 

 

Back to Topic List

Writing the Error Corrections Back to the Error Table

To write the error corrections back to the Error table, perform the following steps:

1.

In the Component Palette to the right, select Services from the drop-down list. Drag the Database Adapter partner link to the gray Services swim lane to the right of your process diagram. The Database Adapter wizard appears. Click Next on the Welcome page. In the Service Name field, enter Write_Corrections. Click Next.

 

2.

From the Connections drop-down list, select ODI_DB (or the ODI Database Connection that you created earlier). Click Next.

 

 

3.

For Operation Type, select Perform an Operation on a Table. Deselect all check boxes except Update Only. Click Next.

 

4.

Click the Import Tables button. The Import Tables window appears. Click the Query button at the top right. Move the error table E_Client to the Selected column. Click OK. Click Next.

5.

On the Relationships page, click Next. Click Finish. The Create Partner Link wizard appears.

6.

On the Create Partner Link wizard screen, retain the defaults and click OK. Your process diagram should match the following image:

Back to Topic List

Invoking the Error Corrections Database Adapter

To invoke the Error Corrections database adapter, perform the following steps:

1.

From the Component Palette at the right, select Process Activities from the drop-down list. Select the Invoke activity and drag it onto your diagram below the Invoke_ReadErrors activity.

 

2.

Drag the arrow at the right of the Invoke activity onto the Write_Corrections partner link in the swim lane at the right. The Edit Invoke window appears. Name the activity Invoke_Corrections. Click the Magic Wand icon next to the Input Variable field. In the Create Variable window, name the variable LoadCorrections. Click OK. Click OK again. Verify that your process matches the following image.

 

 

Back to Topic List

Adding Human Workflow to Handle the Errors

To add the Human workflow to handle the errors, perform the following steps:

1.

From the Component Palette at the right, drag the Human Task activity onto your diagram below the Invoke_ReadErrors activity. The Add Human Task wizard appears. Click the Magic Wand icon next to the Task Definition field. The Add a Human Task page appears.

 

2.

On the Add a Human Task page, name the task ErrorHospital. Click OK. The ErrorHospital task page appears. In the Title field, enter ODI Load Errors . In Outcomes, enter SUBMIT CORRECTIONS. Click the green " +" icon next to the Parameters heading. The Add Task Parameter page appears.

 

3.

On the Add Task Parameter page, select Element and click the Flashlight icon to the right. Expand Project Schema Files > ReadErrors_table.xsd and select EclientCollection. Click OK. Select the Modifiable via worklist check box. Click OK.

4.

Click the green " +" icon next to the Assignment and Routing Policy heading. The Add Participant Type page appears. In the Group Id(s) field, enter Supervisor (alternatively, click the Flashlight icon to browse the group in the application server security repository). Click OK.

5.

Verify that the Human Task is now configured as shown below. Select File > Save All from the main menu. Close the ErrorHospital.task page by clicking the cross on the tab. Your BPEL process should now look like the following screenshot.

6.

Double-click the ErrorHospital_1 activity to open the Human Task wizard. Under the BPEL Variable header and next to EclientCollection, click the Flashlight icon. Expand Process > Variables > LoadErrors_OutputVariable > EClientCollection and select EclientCollection. Verify that Xpath contains /ns3:EClientCollection. Click OK. Click OK again.

7.

In the SwitchCase Regeneration window, click Yes. Select Save All from the File menu.

8.

From the Applications Navigator at the left, expand Client_ErrorHsptl > Integration Content. Right-click the ErrorHospital folder. Select Auto Generate Simple Task Form. A JSP page to show the task details in the workflow appears. Select Save All from the main menu. Close the JSP page (you retain all the defaults).

Back to Topic List

Passing the Updated Values into the Write_Corrections Database Adapter

To pass the updated values into the database, perform the following steps:

1.

From the Component Palette at the right, drag an Assign activity onto the process diagram below the taskSwitch activity and above the Invoke_Corrections activity. Double-click the Assign_1 activity.

 

2.

From the Create drop-down list, select Copy Operation. The Create Copy Operation wizard appears. In the From column, expand Process > Variables > LoadErrors_OutputVariable > EClientCollection and select EclientCollection. The Xpath below should contain /ns3:EclientCollection. In the To column, expand Process > Variables > LoadCorrections > EclientCollection and select EclientCollection. The Xpath below should contain /ns4:EclientCollection. Click OK. Click OK again on the Assign wizard. Your process should now match the following image. Select Save All from the File menu.

Back to Topic List

Deploying the Error Hospital BPEL Process

To deploy the Error Hospital BPEL process, perform the following steps:

1.

From the Application Navigator on the left, right-click your project Client_ErrorHsptl and select Deploy > SoademoIntegConnection > Deploy to default domain.

 

Back to Topic List

Creating the ODI Package

To create the ODI package to execute the INT_LOAD_TBL_CLIENT Interface and the ErrorHsptl BPEL process, perform the following steps:

1.

Within ODI Designer, expand the ODI_ER_HSP project. Expand First Folder. Right-click Packages and select Insert Package. Name the package Create_BPEL_HWF_Tasks. Click the Diagram tab.

 

2.

Expand the Interfaces node. Drag the INT_LOAD_TBL_CLIENT interface into the diagram. Click the Toolbox icon  . Click the Internet list.

 

 

3.

Select ODIInvokeWebService. This tool is used to execute the BPEL process. Click within the diagram, as shown below, and the tool to invoke the Web service appears. Click the Selection icon  , and then click ODIInvokeWebService to edit the properties for the BPEL Web service.

4.

In the Properties section, name the Tool InvokeBPELErrHSPTL. Click the Advanced button. Set the WSDL URL to http://rst-act3ct2.us.oracle.com:8888/orabpel/default/Client_ErrorHsptl/1.0/Client_ErrorHsptl?wsdl. Click the Connect to WSDL  icon to get a description of the WSDL. Verify that your screen is similar to the following screenshot. Click OK. The ODIInvokeWebService tool is now set up.

Note: No input message is needed because we are only executing the BPEL process, which will handle the import and creation of the Human Workflow Tasks.

 

5.

Connect the Interface to the ODIInvokeWebservice tool using the " Next Step on success" option. Click the Next Step on success button  , and then click the interface INT_LOAD_TBL_CLIENT. Drag an arrow from INT_LOAD_TBL_CLIENT to the ODIInvoke tool InvokeBPELErrHSPTL as shown below. Click Execute to execute the package. Click Yes to save the package. In the Package execution window, click OK. In the Message dialog box that states " Session is started," click OK again.

6.

Review the execution results in ODI Operator. To start ODI Operator, select All Programs > Oracle > Oracle Data Integrator > Operator. Log in as Repo_designer with SUPERVISOR as the Username and SUNOPSIS as the Password. In the pane at the bottom left, click the Hierarchical Sessions tab. Scroll up the pane and click " +" to expand Date > The date of execution > Steps. The execution steps of your package Create_BPEL_HWF_Tasks should look as follows.

Back to Topic List

Reviewing the BPEL Process Steps from the BPEL Console and Completing the Process

After the ODI Package has successfully executed and called the BPEL process, we can check the BPEL Console and the BPEL Worklist. To review the BPEL process steps from the BPEL Console and complete the process, perform the following steps:

1.

Open a browser and enter http://localhost:8888/BPELConsole as the URL. The Login page appears. Enter your application server credentials. Enter oc4jadmin in the Username field and welcome1 in the Password field. Click Login. The main BPEL Dashboard appears. Verify that your Client_ErrorHsptl BPEL project appears under the Deployed BPEL Processes column on the left. Select the Client_ErrorHsptl instance. The Visual Flow view appears by default.

Note: This is the diagram of your process execution. By clicking each of the activities, you can see the data that the process has collected.

 

2.

Select the Invoke_ReadErrors activity and verify that the data from the E_Client table has been collected from the database.

 

3.

The process execution diagram ends at the ErrorHospital Human task activity. This is because the process is waiting for someone from the Supervisor group to view, fix, and resubmit the errors. In another browser window, enter http://localhost:8888/integration/worklistapp as the URL. The Worklist application page opens, where you can log in to view and perform actions on the workflow tasks. Log in with jcooper as the Username and welcome1 as the Password. Jcooper's Worklist page appears. All the tasks assigned to jcooper, or any group that jcooper is a member of, are shown on the page. You see that jcooper currently has one task called ODI Load Errors, which is currently assigned to the Supervisor group.

 

4. From the Actions drop-down list at the right, select Claim and click Go (this removes the task from the group and assigns it to Jcooper to work on). The task details page appears. On this page, we can view all the errors and correct them.

Note: The look and feel of this page can be altered by updating the JSP page that we generated earlier.

5.

To correct errors, you can change the values in the Age column (for one or all rows with errors) to 21 or older. In the Age column for all the errors, enter 21 as shown below. Click the Save button at the top-right corner of the page to confirm your changes. To resubmit the changes so that the errors are corrected during the next ODI load, select SUBMIT CORRECTIONS from the Task Action drop-down list, and then click Go. Return to your task list and see that there is no longer any actionable tasks for Jcooper.

6.

Return to the BPEL Console page and refresh the browser. The process has now updated and you see that it has progressed past the ErrorHospital Human workflow activity. Click the Invoke_Corrections activity to see the data that the E_Client table has been updated with and note that the errors have been updated with your corrections (you can also confirm this by checking the database table directly).

Note: You should now have a fully functional ODI to BPEL Human Workflow Error Hospital. If you rerun your ODI Scenario now, the corrected errors are picked up and resubmitted to the target table. Remember not to submit the new values with the same Client_ID as the corrected values to see this functionality. If you resubmit values with the same Client_ID, these new values take precedence over your corrections and ODI tries to load the new values (even if they still contain errors). You should now have a fully functional ODI to BPEL Human Workflow Error Hospital.

Back to Topic List

Summary

In this lesson, you learned how to:

 Verify the Prerequisites
 Create a New Project in ODI
 Insert a New Model for the XML File
 Insert a Model for the CLIENT Relational Table
 Delete the "Copy of CITY_CLIENT" Constraint
 Set Up the ODI Constraint on the CLIENT Data Store
 Import the Knowledge Modules to Load, Check, and Integrate the XML File to RDBMS
 Create a New Interface to Load the XML File to the RDBMS Target
 Execute and Test the Interface
 View the Constraint Errors from Interface Execution
 Create a New Application for the BPEL Process
 Connect to the ODI Data Source
 Collect Errors from the Error Table
 Invoke the Read Errors Database Adapter
 Write the Error Corrections Back to the Error Table
 Invoke the Error Corrections Database Adapter
 Add Human Workflow to Handle Errors
 Pass the Updated Values into the Database
 Deploy the Error Hospital BPEL Process
 Create the ODI Package
 Review the BPEL Process Steps from the BPEL Console and Complete the Process

 

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document