Detailed explanation of the Graphical Data Mapping Data Warehousing sample
An explanation about how the sample works, focusing mainly on the graphical data map.
The input XML message, LoyaltySalesData, contains a unique customer number.
This is used as the primary key in the LOYALTY table, creating a select on the database, using a Select transform.
The result of this Select transform is queried by using an XPath expression, fn:count().
This counts the returned rows of the select. If no records are found in the LOYALTY table, then
the count transform returns 0, and proceeds through the Else nested transform.
In this instance, a new record is inserted into the database.
The Insert transform is contained in the Else nested transform. For more information about inserting data in to a database,
see Inserting data into a table
in the IBM Integration Bus Information Center.
In the Insert nested transform, you must create a total sum of all items, from the Item element, within each category before inserting into the database. This is implemented using a Sum transform.
You must provide an expression to select the item elements you want to sum up using the transform's General Properties.
To provide the parameters to sum, you can use square brackets to implement a filter on $Items, because it is an array.
In this sample, all confectionery
has an item code in the range 1-10, so this condition is used as the filter. For help filling in this information, you can use the Content Assist (Ctrl+Space).
The Return transform gives XML output as a LoyaltySalesDataOut message. This message is placed in the LOYALTYDATA.OUT
output queue of the message flow to analyze successfully completed insertions into the database.
The message contains the same data as the input message, but also has a DatabaseReturn element and an Action element.
The Action element contains a simple message stating that the insert was successful.
The items are mapped using a For Each transform because the item is a repeating element.
You have the option of handling failures when inserting and updating a database, by using a Failure transform. To add a Failure transform to the insert or update, right-click the transform, and select Database > Handle failure. You can then route the SQL failure messages to an output message in this transform.
The Failure transform uses the output message LoyaltySalesDataOut. When an insert fails, the SQL
code and failure state are mapped in to the DatabaseReturn element. All other fields are mapped as in the Insert transform.
For more information about Failure transforms, see Failure
in the IBM Integration Bus Information Center.
If the Select statement finds a matching record, the fn:count() will return the number of rows and If transform proceeds.
If this customer exists in the database, the results set count is 1 and that record should be updated with the new data in the message.
You cannot select more than one record, because the customer number is a primary key.
The Update transform is contained in the If nested transform. For more information about updating data in to a database, see
Updating data in a table
in the IBM Integration Bus Information Center.
For the Update transform, you must create a total sum of all items within each category. This includes the current value in the database and the items from the input message.
You must provide the Sum transform with the subset of the items for a category and the existing database field from the Select using an expression on the General Properties.
The Update Failure transform is implemented in the same way as in the Insert transform.