Data enrichment from data sources in WebSphere Business Events V7

WebSphere® Business Events V7 provides three methods to leverage a data source to enrich data after an event is received or before an action is sent: expression SQL, mapped keys, and mapped expression. This article shows how to use these methods to enhance and enrich incoming or outgoing messages or events that require querying a database for additional information. This content is part of the IBM Business Process Management Journal.

Share:

Shenfu (Mike) Fan (sfan@us.ibm.com), Certified IT Architect and IT Specialist, IBM

Photo of Shenfu (Mike) FanShenfu (Mike) Fan is a Senior Managing Consultant with IBM Software Services for WebSphere (ISSW). He is primarily focused on architectural design, development, and implementation of enterprise application integration solutions using WebSphere products that include WebSphere Message Broker, WebSphere MQ, WebSphere Business Events, WebSphere Service Registry and Repository, WebSphere ESB, and others.


developerWorks Contributing author
        level

15 February 2012

Introduction

WebSphere Business Events (hereafter called Business Events) is a business event processing product that can detect, evaluate, correlate, monitor, and respond to various types of event patterns according to the configured business logic. Frequently, the incoming messages, or events, may need to be enhanced before being processed by the Business Events runtime. The outgoing messages (actions) may also require to be enriched before being sent out. In Business Events, you can use JavaScript™, a data source, or both for data enrichment. For example, if an event contains the firstName and lastName fields and the combination of these two fields needs to be assigned to a fullName field in an intermediate object, you can use JavaScript. There are cases that a database may need to be queried for data enrichment. For example, given a customer ID, an action needs to be generated with the customer’s purchase order information, which is stored in a database.

In the following sections, examples show how to use a data source for message enrichment. This article does not cover how to call stored procedures.

To benefit from this article, you need a fundamental understanding of the complex business event processing concept and basic knowledge of Business Events. For product information, see the Resources section at the end of the article.


Retrieving data from a data source

Business Events provides the following three methods to retrieve data from a data source:

  • Expression SQL: In this method, a native SQL expression, including joins and multiple tables, is defined in and applied to an intermediate object field. You can only select one column of a table and only the value from the first row is returned. In other words, if multiple rows are retrieved, all rows except the first one are discarded.
  • Mapped keys: By using this method, you can retrieve multiple rows from a data source in an intermediate object. First, define a field or fields that have values as a mapped key or keys. Then, other fields (at least one field) need to be defined as non-mapped key fields. In this intermediate object, the defined mapped keys are automatically applied across any other non-mapped key fields. The Business Events runtime generates a SQL statement based on the mapped and non-mapped key fields to select the appropriate rows from a table or tables. With each retrieved row, an instance of the intermediate object is created.
  • Mapped expression: You can also use this method to retrieve multiple rows from a data source in an intermediate object. The mapped expression defined in a field is a WHERE clause and can contain any valid SQL syntax, including GROUP BY, HAVING, and sub-queries. The mapped expression is automatically applied across any other intermediate object fields that form part of the mapped expression. The Business Events runtime uses the expression to generate a single SQL statement that includes all the referenced columns to select the appropriate rows from a table or tables. One instance of the intermediate object is created for each retrieved row.

Note that mapped expression is a generalization of mapped keys. They function the same way. Mapped keys builds the WHERE clause for you. However, with mapped expression, you can build any WHERE clause that you need.


Setting up a data source

To demonstrate how data is retrieved from a database using each of the methods described previously, examples are created in Business Events V7.0.1.1 on Microsoft® Windows® XP®. The data source used in the examples is an IBM® DB2® database. The database name is TESTDB with two tables, Customer and PurchaseOrder, as shown in Figures 1 and 2. The examples are included in the Business Events project file, which you can download from the Download section of the article.

Figure 1. Customer Table
Customer Table
Figure 2. PurchaseOrder Table
PurchaseOrder Table

To use the TESTDB database as a data source, you need to add the database as a system data source using the following steps:

  1. Open the ODBC data source by clicking Start => Control Panel => Administrative Tools => Data Source (ODBC).
  2. In the ODBC Data Source Administrator, click Add on the System DSN tab.
  3. In the Create New Data Source page, select IBM DB2 ODBC DRIVER and then click Finish.
  4. Select the database from the drop-down list in the Database alias field, type the database name in the Data source name field, and then click OK. The database is added as an ODBC data source.

Then perform the following steps to insert the database as a data source using the Business Events Design Data (hereafter called Design Data). Design Data is a Business Events component that supports the definition of the metadata layer required for business event processing.

  1. Open Design Data.
  2. In the Data Sources section, right-click Hosted Database and select Insert Hosted Database => TESTDB.
  3. Provide the user ID and password for the database connection.
  4. Right-click the TESTDB entry and select Source Properties.
  5. On the Source tab, modify the JDBC URL from jdbc:db2://DB2:50000/TESTDB to jdbc:db2://localhost:50000/TESTDB. Keep in mind that the JDBC string is database installation specific.
  6. After typing and confirming the password, click OK.
  7. On the "Connect To DB2 Database" page, type the user ID and password again.
  8. Click OK and the CUSTOMER and PURCHASEORDER tables are displayed as shown in Figure 3.
    Figure 3. Inserting the database TESTDB as a data source
    Inserting the TESTDB database as a data source

Creating events, actions, and intermediate objects

Tables 1, 2, and 3 show the simple event, action, and intermediate objects that are used in the examples for demonstration purposes. These objects are created using Design Data. To learn how to create events, actions, and intermediate objects, see the tutorial provided in the WebSphere Business Events Information Center.

Table 1. Event objects
Event nameEvent objectFieldType
Customer_ECustomer_ObjcustomerIDString
Table 2. Action objects
Action nameAction objectFieldType
ExpressionSQL_AExpressionSQL_ObjcustomerID
firstName
lastName
PONumber
orderItem
orderDate
shippingDate
shippingAddress
String
String
String
String
String
DateTime
DateTime
String
MappedExpression_AMappedExpression_ObjcustomerID
firstName
lastName
PONumber
orderItem
orderDate
shippingDate
shippingAddress
String
String
String
String
String
DateTime
DateTime
String
MappedKey_AMappedKey_ObjcustomerID
firstName
lastName
PONumber
orderItem
orderDate
shippingDate
shippingAddress
String
String
String
String
String
DateTime
DateTime
String

Note: All these actions have the same set of fields.

Table 3. Intermediate objects
Intermediate objectField
ExpressionSQL_IOThe same as in the action object ExpressionSQL_Obj
MappedKey_IOThe same as in the action object MappedKey_Obj
MappedExpression_IOThe same as in the action object MappedExpression_Obj

The customerID field from the Customer_Obj event is mapped to the customerID field in each of the intermediate objects. Based on the customerID, values for all the other fields in the intermediate objects are retrieved from the Customer and PurchaseOrder tables. Then each field in the intermediate objects is simply mapped to the field with the same name in the corresponding action objects.

Now you are going to create the Expression SQL on the ExpressionSQL_IO intermediate object, the mapped key on MappedKey_IO, and the mapped expression on MappedExpression_IO, respectively.

Creating the Expression SQL

To create the Expression SQL on the ExpressionSQL_IO intermediate object:

  1. Open Design Data.
  2. Click the open arrow for the PONumber intermediate object field to display the field properties.
  3. Select the Expression (SQL) from the Type list and the TESTDB data source name from the Source list. The SELECT part of the statement is automatically inserted in the Expression box.
  4. Click Insert source column, select the PURCHASEORDER table and the ORDERNO column that contains the data to be loaded into this field. The name of the table and column is automatically entered into the Expression box.
  5. Type FROM CUSTOMER,PURCHASEORDER, where CUSTOMER and PURCHASEORDER are the table names. Note that some database management systems require that the schema name of the table is included in the From clause.
  6. Add the WHERE keyword to the SQL, and then build the selection criteria by using Insert source column to select CUSTOMERID in the CUSTOMER table to identify the linking field in the data source.
  7. Add the equal sign (=), and click Insert object field to select the customerID field to identify the linking field in the intermediate object. The field inserted into the Expression box is displayed as $(customerID).
  8. Add another selection criteria, PURCHASEORDER.CUSTOMERID=$(customerID). The expression SQL created is shown in Figure 4.
Figure 4. Creating the expression SQL
Creating the expression SQL

Keep in mind that you can always manually type the entire SQL statement in the expression box.

Here, only one field is populated from the data source using the expression SQL. Of course, you can also populate other fields using the same procedures. However, if multiple columns are involved with the same query, use mapped keys or mapped expression.

Note that Design Data does not validate the SQL statement. Therefore, check and make sure the syntax is correct.

Creating mapped keys

To create a mapped key on the MappedKey_IO intermediate object, do the following:

  1. Open Design Data.
  2. Click the open arrow for the customerID field to display its properties window. The customerID field is used to form a mapped key.
  3. Select Mapped Keys from the Type list.
  4. Select the name of the TESTDB data source from the Source list.
  5. In the Mapping section, select the CUSTOMER table from the Table list.
  6. Select the column CUSTOMERID from the Column list.
  7. Click the Key Options-> button and select Key Equal. The string "=$(customerID)" is automatically added in the Expression box.
  8. Click the Join Mapping button, add the PURCHASEORDER table, and set the column to CUSTOMERID as shown in Figure 5.
  9. Click OK. The mapped key based on the customerID with join mapping is created. You can create multiple mapped keys if needed.
    Figure 5. Creating mapped key
    Creating mapped key

Now you need to include the non-key columns in the database query. To create a non-mapped key on the MappedKey_IO intermediate object:

  1. Open Design Data.
  2. Click the open arrow for the firstName field to display its properties.
  3. Select the data source name, TESTDB, from the Source list.
  4. In the Mapping section, select the CUSTOMER table from the Table list.
  5. Select the FIRSTNAME column from the Column list. The Mapped Keys in the Definition Type and =$(customerID) in the Expression box are automatically populated as shown in Figure 6.
    Figure 6. Creating non-mapped keys
    Creating non-mapped keys

Follow the same steps to create other non-mapped keys in the MappedKey_IO intermediate object.

Based on the mapped keys, the Business Events runtime builds the following SQL statement:

select CUSTOMER.CUSTOMERID, CUSTOMER.FIRSTNAME, CUSTOMER.LASTNAME, PURCHASEORDER.ORDERNO,
  PURCHASEORDER.ITEM, PURCHASEORDER.ORDERDATE, PURCHASEORDER.SHIPDATE, 
  PURCHASEORDER.SHIPADDRESS from CUSTOMER, PURCHASEORDER where 
  CUSTOMER.CUSTOMERID = $(customerID) and CUSTOMER.CUSTOMERID = PURCHASEORDER.CUSTOMERID

It is worth mentioning that one easy way of creating a mapped key data object is to drag and drop one of the tables from the data source panel to the intermediate object panel. Design Data creates a new intermediate object with all the fields matching the table columns. To finish the definition, all you need to do is to define a mapped key in one of the fields and map an event object field value to the key.

Creating a mapped expression

To create a mapped expression on the MappedExpression_IO intermediate object, do the following:

  1. Open the Design Data.
  2. Click the open arrow for the firstName field to display its properties.
  3. Select Mapped Expression (SQL) from the Type list and the data source name, TESTDB, from the Source list. The word "WHERE" is automatically added in the Expression box.
  4. In the Mapping section, select the CUSTOMER table from the Table list.
  5. Select the FIRSTNAME column from the Column list.
  6. In the Expression box, provide the WHERE CUSTOMER.CUSTOMERID=$(customerID) clause.
  7. To reference the PURCHASEORDER table in the mapped expression, click the Join Mapping button, select the PURCHASEORDER table, and select the CUSTOMERID column as shown in Figure 7.
    Figure 7. Creating the mapped expression
    Creating the mapped expression
  8. Click OK. The join mapping is created.
  9. Type ADD PURCHASEORDER.CUSTOMERID=$(customerID) to complete the WHERE clause as shown in Figure 8.
    Figure 8. Creating the mapped expression with join mapping
    Creating the mapped expression with join mapping

Repeat the same steps for other intermediate fields that maps to a table column. The mapped expression defined for the first intermediate object field, firstName, is automatically applied to all the fields. If the mapped expression is changed, the modification also automatically applies to all the fields.

Based on the mapped expression, the Business Events runtime builds the following SQL statement:

select CUSTOMER.CUSTOMERID, CUSTOMER.FIRSTNAME, CUSTOMER.LASTNAME, PURCHASEORDER.ORDERNO,
  PURCHASEORDER.ITEM, PURCHASEORDER.ORDERDATE, PURCHASEORDER.SHIPDATE, 
  PURCHASEORDER.SHIPADDRESS from CUSTOMER, PURCHASEORDER where 
  CUSTOMER.CUSTOMERID = $(customerID) and PURCHASEORDER.CUSTOMERID = $(customerID)

Note that the mapped expression can be static, which means the syntax does not reference any I/O fields. Therefore, the results are always the same. The mapped expressions can also contain variable substitution. That is, the syntax references I/O fields that provide the value for a variable in the column=variable portion of the WHERE clause. Furthermore, the mapped expressions can dynamically build SQL clauses, which references intermediate object fields containing JavaScript that dynamically builds a portion of the SQL syntax.


Creating an interaction set

One simple CustomerPO interaction set with no filters is created using Business Events business space. In this interaction set, as shown in Figure 9, and in response to the event Customer_E, three actions are fired immediately: ExpressionSQL_A, MappedKey_A, and MappedExpression_A. To learn how to create interaction sets, see the tutorial provided in the WebSphere Business Events Information Center.

Figure 9. Creating an interaction set
Creating an interaction set

Performing tests

To generate multiple actions, open Design Data, check Automatically generate multiple Actions if the number of occurrence of an Action Object would exceed its maximum in each action's properties as shown in Figure 10.

Figure 10. Action properties setting
Action properties setting

Also uncheck No fixed limit in each action object's properties as shown in Figure 11. Note that if No fixed limit is checked, only one action is created with an array of the returned rows.

Figure 11. Action object properties setting
Action object properties setting

Use the Business Events Tester to perform the tests:

  1. With Business Space open, publish the project to the Business Events runtime.
  2. Open the Business Events Tester, click the Send an Event page.
  3. Click the Select Event Template button to choose App1 => Customer_E event template.
  4. Type 100001 in the customerID field and send the event as shown in Figure 12. With this customer ID, two rows are retrieved.
    Figure 12. Sending the Customer event
    Sending the Customer event
  5. On the Actions page, check out the actions generated. As shown in Figure 13, one ExpressionSQL_A action is generated because only the first row is returned by using the Expression SQL method. Two actions for both MappedKey_A and MappedExpression_A are generated because multiple rows are retrieved by using the mapped keys and mapped expression methods.
    Figure 13. Generated actions
    Generated actions
  6. Details of these two action messages for MappedExpression_A are shown in Figure 14. One message represents one row returned.
    Figure 14. Action messages
    Action messages
    Figure 14 displays details of the two action messages. As you can see, one message represents one row returned.

Conclusion

This article showed how you can use WebSphere Business Events methods to leverage a data source (database) to enrich data after an event is received or before an action is sent. These methods are expression SQL, mapped keys, and mapped expression.


Acknowledgments

The author would like to thank Tim Galvin from the IBM WebSphere Business Events product development team for reviewing this article.


Download

DescriptionNameSize
Sample project filesWBEProject.zip3KB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=792950
ArticleTitle=Data enrichment from data sources in WebSphere Business Events V7
publish-date=02152012