Tuning Messages in an Oracle E-Business Suite – ESB Integration Environment

By Ron Batra [Mar-08]

Introduction and Scope

In a large scale A2A (application-to-application) integration of Oracle E-Business Suite into an Enteprise Application Framework using Oracle SOA Suite, let us consider the sub-case of publication service (mainly Oracle Project Accounting with some other data elements). This service publishes data elements Oracle Enterprise Service Bus (ESB), from where five other applications subscribe to the data elements being published. The focus of this article is to show an iterative process in tuning the messages generated in an event driven architecture framework. Figure 1 shows the conceptual architecture. The focus of this paper is on message sizing and not so much on service invocation and related topics such as adapter selection etc.

Event Driven Architecture and Messaging:

In an event driven architecture, changes to data elements can be transferred in real-time/near real-time through an ESB framework. These data changes are converted into a message that is published to the ESB. In older file transfer based integration methods, delta transactions were simply collected and written to a file and this file was transferred to the target system(s). In most cases, the file-size was never an issue. In an event driven architecture and an ESB framework, message sizing and volume of messages is one of the key design decisions to be made. In most cases, business events cannot be changed, so the focus is on tuning message sizing and throughput. To do this well data cardinatity and affinity needs to be closely examined and implemented in the final design.

Consideration on Integration Design Patterns:

In this scenario of integrating N+ Enteprise Applications, the canonical model design pattern works well – it allows domain value mapping as well as dynamically addition of new subscribers. In this case, while designing the publication service, we started off with 5 subscription services; however that number can change dynamically without any major code rework. It is usually worthwhile to invest time upfront in designing the canonical model and validate rigorously the business requirements that drive the design of the service.

Oracle EBusiness Suite Integration Repository:

The Oracle Integration Repository maintained at http://irep.oracle.com lists integration methods available for each product family in the EBusiness Suite with the most common choices being Concurrent Programs, EDI, Interface Views, Java, Open Interfaces, PL/SQL, Web Services and XML Gateway Maps. In our case of the outbound interface in an ESB framework (publishing to the ESB) - our business requirements drove us to extract data from different product families and combine them into a large message. We chose to develop a system of views and triggers as explained later on in the section.

Data Elements to be published:

The design of the publication service is driven largely by the requirements of the subscribers. However, reading between the tea-leaves and trying to forecast future data needs can often pay off by avoiding redesign of the publication service in the future. Data elements from Human Resources, General Ledger, Purchasing, Project Accounting had to be combined into one large message to be published to the ESB.

Module	Key Data Elements
HR	Organization Units, Organization ID, People, Assignments
GL	GL Code Combinations, GL Periods
PO	Vendors
Project Accounting	Project ID, Project Name, Project Cost, Project Cost Distribution Lines, Periods, Tasks, Expenditures (Items, Groups, Types etc.)

Table 1: Data Elements Listing

Design Approach 1: Complex SQL View and Staging Tables

In the first iteration of the design, we used a combination of views, PL/SQL program units and a parent-child relationship structure created between 4 staging tables. Table 5 has to retrieve a data set based on a Project ID field. This query has to navigate the parent-child relationship between 4 tables for every result-set. A trace of the adapter code found a lot of fetches, even with bind variables; it was obvious there was some room for efficiency here. We traced the SQL calls made to the database using traditional Oracle database tracing methods.

The key point to note here is the number of joins the adapter code had to do, to put together a result set that could be transformed into an XML message. This led to a significant difference between SQL elapsed time and actual execution time. This can increase even more if the Oracle Application Server and Database Server are in different locations and network latency can come into play – and applies to every message published. The average message size was 54KB and we averaged about 10000 messages per day.

Design Approach 2: Adding GROUP BY Clause

In the second iteration, we changed the SQL of the Delta Transactions View, and added a GROUP BY clause. This was done to reduce the number of transactions populated in the cost transactions table. The message size reduced to about 40KB, and we averaged about 15,000 messages per day.

Design Approach 3: Flattened Table Structure with Simplified Logic

To reduce the recursive SQL and the joins coming from the parent-child relationship, we decided to create a flattened (essentially a de-normalized table) where data elements are referenced by project id and date. The temp table creates only records that do not have an ID in table 1. Once a message is published on the ESB, we do not need to store the same data elements in the temp table, and let the ESB queues and its persistence take care of managing data consumption by the publishers.

We found our messages reduced to 14KB and while the number of messages went up, we were able to process 50000 messages in 2 hours. This was well within the acceptable processing window.

Determining message size:

We combined two measurements to determine message size. In an ESB environment, a message is a combination of data elements and an XML structure (tags). The data elements sizing is calculated from the tables where the data originates – eg. VARCHAR2(60) + DATE + NUMBER – one can take the maximum possible and assume that as the upper limit for the data elements. To the bytes obtained from this, we added the size of the XML tags relevant to the data mapping. Since XML is text, this is relatively uncomplicated. However, this is an estimate and not the actual amount that will pass through the ESB, where transport methods and protocols can add their overhead to a message.

Conclusion:

Tuning messages can start from the data extraction layer. Depending on the complexity of the data sets, and related affinity, a publication service can use traditional SQL tuning methods to enable adequate throughout of messages through the adapter. Depending on the integration environment and number of end-points, a high-volume of small sized messages may be processed faster than a smaller number of, but larger sized messages. Message sizing is a key element in an event driven architecture.