Oracle Data Integrator: enabling the transition to SOA

This article will review the web service features of ODI and discuss how they can be used to develop and deploy a 'data service' layer that will prove critical within a Service Oriented Architecture (SOA).

Enabling the transition to SOA: Oracle Data Integrator

Oracle Data Integrator (ODI) is a relatively recent addition to the Oracle Business Intelligence product set (subsequent to the acquisition of Sunopsis in 2006) and represents a powerful tool that will form the cornerstone of the Oracle Fusion data integration strategy. This article will review the web service features of ODI and discuss how they can be used to develop and deploy a 'data service' layer that will prove critical within a Service Oriented Architecture (SOA).

In this article we will use the example of an organisation actively involved in the transition to SOA. This will allow us to demonstrate how an existing data integration environment can be built upon to create the SOA data service layer. This layer provides all data integration functionality, including transformational, bulk data processing and transactional services that will provide a solid foundation for the entire SOA initiative.

SOA is a design approach that accommodates the free exchange of data between 'loosely coupled' applications. This is achieved through the creation of a number of services, each of which exposes key application functionality. While SOA is not intrinsically linked to a single technological approach, web services based on industry standard communication protocols have emerged as a key enabler. Web services are comprised of a number of well established technologies including Extensible Markup Language (XML), Web Services Description Language (WSDL) and Simple Object Access Protocol (SOAP).

What is SOA?

The above standards provide a layer of abstraction, negating the need for deep technical linkage hand coded within each application. This promises to reduce development time and maintenance, while promoting the re-use of services to create composite business applications.

SOA has emerged as a potential solution to the architectural challenges facing many organisations, providing the flexibility required by the modern enterprise. However, there is an obvious worry as to how data access and usage will be controlled in such a flexible and abstract environment.

SOA and Data Integration

SOA increases the requirement for a coherent and well developed data integration strategy, as opposed to negating it. Data access, manipulation and usage may well be hidden deep within the application code of each service, making governance and administration enormously complex. Data objects can be utilised differently across applications, increasing the potential for inconsistency, misunderstanding and misinterpretation of results. These are exactly the challenges that are addressed by data integration. Therefore, it is of critical importance to understand how data integration principles can be incorporated within SOA.

Data integration encapsulates a range of techniques and methodologies to integrate, standardise and re-model data to meet the information demands of the enterprise. This ensures the consistent use of data objects across the organisation, providing a single version of the 'truth'. There is clear concern as to how this can be incorporated within a web service based architecture that is markedly different to traditional data integration environments.

An elegant solution to this challenge is the introduction of a 'data service' layer that de-couples business services from the underlying data, as shown in Fig 1. In this model business services do not directly interact with the underlying data assets. Rather, business services make requests for data operations via a data service layer. The data service layer is a well controlled and highly visible access point to organisational data assets, incorporating all the data integration functionality required within the SOA. This should include transformational services, bulk data processing services and more general transactional services.

Business Service Layer: Process driven, abstract - decoupled from direct interaction with datastores, access data via the data service layer Data Service Layer: Data-centric integration, full data integration functionality, controlled access point to data assets, direct interaction with data - knowledge of underlying data model required to build and maintain Transformational Services Transactional Services Bulk Data Processing Services Data Quality Services Data operation Request: Invoke batch processing job, Synchronise systems, Insert new record, Cleanse dataset Response: Data message, Confirmation of task completion Process new orders Open New Account Merge Accounts Close account Data Assets.This concept is enormously appealing, as it places mature and well established data integration principles firmly at the core of SOA. A well formed data service layer can ensure that data objects are accessed and used in a consistent manner by all business services. The key question then becomes how can data integration functionality be exposed and incorporated within the data service layer. This article will demonstrate how an organisation actively involved in the transition to SOA can build upon their data integration expertise with ODI to develop and deploy the SOA data service layer.

The key SOA features of ODI that will be explored are; ODI Public Webservices, OdiInvokeWebService function and ODI Data Services.

A genuine data service layer must incorporate transformational services, bulk data processing services and data quality services. This functionality is critical to many business processes yet is not easily accommodated by the web service technology that supports SOA. Hence, it is important to understand how this functionality can be incorporated within the web service architecture. ODI Public Webservices provide an elegant solution to this challenge by exposing the full functionality of ODI via a single web service, OdiInvoke.

ODI Public Webservices

To demonstrate the power of ODI Public Webservices let us use our example of an organisation actively involved in the transition to SOA. Using the Oracle SOA suite they have developed a BPEL (Business Process Execution Language) workflow that models their order management process. Whenever orders are placed a well defined series of steps must be performed, perfectly suited to web service messaging technology. However, they also run a bulk data processing ODI scenario that periodically synchronises disparate parts of the order tracking system. They would now like to incorporate this within the BPEL workflow to ensure that all functionality can be accessed and controlled from a single place.

Effectively, we have to take an existing ODI scenario and somehow expose it as invokable web service. The first step that must be performed is to ensure that ODI Public Webservices have been installed within a web container, such as axis2. This is a relatively simple matter of loading the odi-public-ws.aar, found within the ODI_HOME\oracledi\tools\web_services folder. The success of this can then be confirmed by viewing the list of available services. It can be seen that there is now an OdiInvoke service.OdiInvoke is an incredibly powerful service, which allows ANY ODI scenario to be invoked as a web service. Effectively, OdiInvoke acts as a single access point to the extensive cleansing, transformational and bulk processing functionality of ODI. The next step is to generate the 'message' that will let this service know which particular scenario should be invoked. This is greatly simplified by the 'Test Web Service' utility within ODI, which allows us to select a web service and the particular operation we would like to test. For this service we have 3 operations available; one to return the web service version, another to invoke a scenario and another to re-start a session. In this case we wish to invoke the order synchronisation scenario.

The utility is incredibly useful as it visually displays all the available elements for the operation that has been selected. The values for each relevant element can then be added and the SOAP request is automatically generated. This is significantly easier than hand-coding the request - especially for those unfamiliar with SOAP and XML. The request can then be tested by invoking the service and the success confirmed by the return message displayed on the right panel of the utility.At this stage we now have the potential to execute any ODI scenario via Public Webservices. In addition, the SOAP request to invoke the specific scenario has also been built and tested. The next step is to somehow introduce this request within an order processing workflow. This can be achieved very easily by incorporating the OdiInvoke request within the existing BPEL workflow.This simple example has demonstrated how OdiInvoke allows us to build upon our existing skill base and expertise to create bulk data processing services. All the functionality of ODI can be exposed as a web service and utilised by the Oracle SOA suite. This is an exceptional feature that has enormous potential.

For example, the profiling, cleansing and conforming capabilities of ODI could easily be exposed as data quality services while the EL-T architecture of ODI could be utilised to create transformational services. In addition, there is a serious question mark over the ability of a web service based infrastructure to handle large data volumes. ODI can compensate for this weakness by providing the 'horsepower' within SOA by exposing bulk data processing as a web service.

From an administrative perspective, ODI Public Webservices also represent an excellent option.

OdiInvoke acts as a single point of entry for all transformational and bulk processing services, removing the need for a large number of individual services that serve separate functions. Additionally, all the scenarios remain within the confines of the ODI repository, for which there is strong metadata support.

The ability to expose the functionality of ODI is critical to the development of an effective data service layer. However, this only allows ODI to respond to execution requests from the SOA infrastructure. Ideally, we would like ODI to adopt a dynamic role within the architecture by directly invoking web services as part of the processing logic, which is provided by the OdiInvokeWebService feature.


OdiInvokeWebService is a standard ODI feature that can be incorporated within any package. The OdiInvokeWebService function accepts a number of parameters, including WSDL location, SOAP request and location of response file.In order to demonstrate a potential use case we will return to our example of an organisation making the transition to SOA. As described above they have utilised ODI Public Webservices to deploy a scenario as a bulk data processing service. This has also been incorporated within a BPEL workflow. While this is incredibly useful, there is slight discontent that the underlying scenario does not interact directly with the SOA infrastructure.

The specific need that has been identified involves the handling of complex errors. In particular, they would like complex errors to be handled by a BPEL process with human workflow. This would provide a degree of human interaction to fix the error and re-commence the flow. This can be easily accommodated by the combined error handling of ODI and the ability to invoke a web service via OdiInvokeWebService.

A stand-out feature of ODI is the manner in which errors are handled. ODI uses Check Knowledge Modules (CKMs) to determine whether data meets certain predefined criteria, including referential constraints and user defined limits. Data that fails these checks is loaded to an error table where it can be re-cycled, corrected and loaded to the target. This is an excellent feature that prevents an entire load failing because of a few suspect records.

A number of such checks are performed as part of the SYNC_ORDERS scenario. Ideally, we wish to introduce a step that will determine if any such errors have been encountered, by counting the number of records that have been added to the error table on a particular execution of the scenario. If there are any such records then a BPEL process with human workflow will be invoked, the error will be rectified and the data re-cycled.

This logic can be easily added to the existing package. Firstly we need to add steps to refresh and evaluate a variable that will hold a count of the number of errors. If there are any errors then the BPEL process is invoked, if not then the successful completion of the process is logged. Once the human workflow has been invoked and the error rectified, the scenario can be re-started via ODI Public Webservices.This simple example demonstrates the dynamic role that ODI can adopt within a web service based SOA. The combined use of ODI Public Webservices and OdiInvokeWebService allows ODI to play a valuable role that extends far beyond simple scenario execution. This has enormous potential to ease the transition to SOA, providing not only data integration services but also the capability to invoke services based on the outcome of steps in the processing logic.

The above sections have demonstrated how ODI scenarios can be exposed as a web service. This is incredibly useful and can bring powerful transformational and bulk data processing capabilities to the SOA infrastructure. However, this functionality only allows pre-built scenarios which perform a specific task to be executed. Hence, there is little flexibility as the scenario must exist before the specific data operation can be performed.

Data Services - Exploiting the potential of Metadata

It is clear that a genuine data service layer must provide a greater degree of flexibility via services that allow a range of common data operations (such as simple inserts, updates and deletes) to be performed at will. This functionality is provided by ODI Data Services, specialised web services that allow direct access to the underlying data.

The key to understanding ODI Data Services is to appreciate how ODI gathers and utilises metadata. ODI generates metadata via Reverse Engineering Knowledge Modules (RKMs), re-usable code modules encapsulating all the logic required to gather pertinent information on the underlying datastore.Once the RKM has been executed ODI has a thorough understanding of the datastore structure, including column names, data types and constraints. ODI now has a sufficient understanding of the datastore to generate SQL statements when a scenario is executed. We now wish to take this ability to construct SQL statements from the available metadata and utilise it to create dynamic web services that can support a range of common data operations.

Such services are generated by Service Knowledge Modules (SKM), which encapsulate all the steps required to build web services based upon the metadata gathered from RKMs. The first step in this process is to define the parameters that will be used to access these ODI Data Services.The SKM generates an archive file that can be loaded to a web container, such as axis2. The success of this can then be confirmed by viewing the list of available services. It can be seen that there are now additional services, one for each datastore (table) that has been deployed.

Each ODI Data Service allows a range of operations to be performed on the underlying datastore, including inserts, updates and deletes. Note that additional operations are also available if Change Data Capture is enabled on the model and datastore. It is very important to appreciate how the functionality of ODI Data Services differs from ODI Public Webservices. ODI Public Webservices allow us to execute any scenario, which must be pre-built and extracts data from specified tables. In contrast, ODI Data Services are not limited in this way and a single service can accept any valid input request no matter where the data originated.

To demonstrate this functionality let us return to our example that has charted the progress of an organisation actively involved in the transition to SOA. They have developed a BPEL order processing workflow that utilises ODI Public Webservices to perform a key bulk data processing step. However, many of the business services within their order processing BPEL workflow still access data directly. The aim is to ensure that all data access occurs via a data service layer where it can be well controlled.

To begin with they would like to create a single web service that allows all transactions to be performed on a single table, Salesorderdetail. Records are frequently added and updated within this table as part of the overall order management process.

This service acts as an entry point for a whole range of transactional operations. We can use the ODI Test Webservice utility to demonstrate this, as shown in Fig 10. We select the appropriate operation; in this case we would like to perform an insert statement so we select addSalesorderdetail. This utility then graphically displays all available elements for this operation, which as we can see correspond to all the column names within this table.Of critical importance, this service can accept any valid request - no matter where it originated. This is unlike a scenario which performs a series of pre-defined operations on a specific datastore. For example, the ODI Data 12.

Service above can be re-used by every business service that requires an insert statement on this particular table, regardless of the details of the individual transaction.

ODI Data Services have enormous potential to be incorporated within the SOA data service layer. They can accommodate a range of transactional operations that are likely to be required. This would remove data access from business services, placing it within a well controlled and highly visible data service layer. By utilising ODI this also ensures that services are based on consistent metadata, accessible through the ODI repository. Overall, this provides a level of control and standardisation that would otherwise be difficult to achieve.

SOA is rapidly emerging as the answer to many of the architectural challenges facing the modern enterprise. For many organisations the transition to SOA is inevitable, yet also a major cause for concern. A common pitfall is failing to develop a sound data integration strategy that incorporates data-centric services within the SOA infrastructure.


This article has demonstrated how effectively ODI can provide the data integration features required to develop a data service layer within the SOA infrastructure. ODI Public Webservices provide the transformational and bulk data processing capabilities, while ODI Data Services accommodate a range of transactional operations. In addition, OdiInvokeWebService allows ODI to adopt a dynamic role by providing the capability to invoke web services as part of the complex processing logic.

In conclusion, ODI is a key enabler of the transition to SOA providing the data-centric features that will form a solid platform for business services to access and use data in a controlled manner.