Delving in to ODI’s Web Services Support

Following on from my postings last week about future OBIEE architecture, I've spent a couple of evenings this week taking a closer look at the web services support in Oracle Data Integrator. Specifically, I was interested to see how ODI mappings and packages could be called by a BPEL process, and how data retrieved from a web service could be included in a mapping or process. I'm also indebted to Douglas Stevenson who's written about this functionality in a forthcoming Oracle Scene article and who batted a few ideas about with me as I came up with my findings.

ODI in the current 10.1.3.4 release has three major areas of web service functionality;

• The Oracle Data Integrator Public Web Service, that lets you execute a scenario (a published package) from a web service call
• Data Services, which provide a web service over an ODI data store (i.e. a table, view or other data source registered in ODI)
• The ODIInvokeWebService tool that you can add to a package to request a response from a web service

The examples in the ODI documentation suggest installing the Oracle Data Integrator Public Web Services, and any Data Services you create, using the Apache Axis2 web services engine that you can then install into a Java application server such as Oracle OC4J. I took Douglas' recommendation and downloaded Apache Axis2 1.2 from the Apache website, and installed it into the default OC4J home in Oracle Application Server 10.1.3.3.1 (the one that comes with Oracle SOA Suite 10g). Having a play around with the three bits of functionality, they seem to work in this way:

The Oracle Data Integrator Public Web Services is fairly straightforward and allows you to call an ODI scenario (in OWB terms, a process flow) via a web service call. A typical way you'd use ODI in this way is to call a bit of bulk data transformation as part of a business process, such as in the BPEL diagram below:

If you've read the recent OTN article I put together on BPEL and OBIEE, setting up ODI to work in this way is pretty similar. You add a reference to the ODI Public Web Service as a partner link in your BPEL process, then make calls to it using Invoke activities. When you add an Invoke activity that refers to the ODI Public Web Service you get a choice of three activities:

• Invoke Scenario
• Get Web Service Version, and
• Invoke Session

with the first activity being the one you'd need to choose.

Other than the Invoke activity, you'd also need to add a corresponding Assign activity before it to set the details of the scenario being called, the respository being connected to and so on. Once this is all in place, you can call a bit of bulk data transformation directly from your BPEL process and use this as a substitute for moving and transforming data on a message-by-message basis.

The second bit of ODI web service functionality is Data Services. Data Services allow you to take a data store in your ODI repository (usually a table or view, but it can also refer to anything that ODI recognizes as providing data) and give it a web service interface, so that outside applications can select from it, update it, delete from it and so on. ODI comes pre-configured to work with Apache Axis2, a web services engine that you can download free from the Apache website and installs pretty easily into the OC4J container in Oracle Application Server. Once you've defined an Axis2 data server in your Topology Manager application, like this:

you can then take any model in the Designer application and select datastores (tables, views etc) within it to publish as web services.

The technology behind this is contained in another type of knowledge module, a "Service Knowledge Module", that takes data in for instance an Oracle database and provides the interface over it. Once you've selected your tables and imported the required knowledge module, you then publish the service to, for example, Axis2 wherapon you can then check out the endpoint.

The tricky bit, I found, with data services is getting the JDBC data source set up correctly. The web service that ODI generates doesn't use your ODI connections but instead uses a JDBC data source on the application server, which in OC4J's case needs to be set up using Enterprise Manager and then in the web.xml file for the Axis2 application. You then need to refer to it correctly in the Services tab for the data store in ODI, get any of these wrong and the service will appear to register correctly but won't return any data, with any error messages being just cryptic java stack traces.

Anyway, the third bit of web services functionality in ODI is the ODIInvokeWebService tool, a bit of functionality you can invoke from the Package editor, analogous to process activities in OWB process flows.

This is the bit of functionality that allows you to bring in data requested via a web service into your ETL process. It works in a bit of a funny way though; first of all, its a package step, not an operation or activity you include in mapping, so you can't "join" to a web service in an interface (analogous to a mapping in OWB), you have to request the data separately, put it into a relational table and then joint to that, if you want to refer to data from a web service in an interface.

Moreover, the way the ODIInvokeWebService tool works is that it makes a web service request, dumps the response into an XML file, and you then process the XML file as a document like any other XML document you might want to load.

Looking at this, it's obviously not something you'd want to make lots of references to as part of a bulk loading ETL project, as each time you call the web service you'll have to get the response, copy to an XML file, load the XML file and so on. There are ways around this if your web service can return data on multiple requests at once - the data services feature I mentioned earlier has a "getCustomerList" operaton that returns data on multiple customers in one go, which means you could get details on all the customers, for example, that you wanted data on and request all their data at the start of the ETL operation - but clearly in this implementation, you can't treat data coming in from a web service as being functionally equivalent to data coming in from file or relational sources.

Then again though, giving the latency of web service calls, would you really want to do this though anyway, "joining" to web service like you would join to a table or file? It's more likely you'd want to gather all your web service data up anyway and stage it into a high-performance table rather than slow down a table join by lots of HTTP requests out to a remote web server, and perhaps the example I've seen in Douglas' upcoming article, where a web service is called when a package fails and we therefore need to get some human workflow intervention, is the more likely scenario.

Anyway, that's the low-down on how web services functionality works in ODI 10.1.3.4. The ability to reference an ODI scenario from BPEL, for example, is a fairly obviousl clear win and of course this is a nice way to have BPEL control your ETL process orchestration. Data Services I'm not sure I'd have a use for, but if you ever need to web service enable a set of data mart tables, for example, then this'd be handy. The ODIInvokeWebservice tool made me think a bit though, in the past I'd thought of data coming in from web services as something you'd drag on to, for example, an OWB mapping and refer to just like any other data source in a mapping, but the way ODI deals with them, together with the inherent latency in such a technology, probably means you'll gather data using them upfront in your mapping at the preparation stage, or you'll use them as a way of invoking some workflow as part of your ETL control process.

Given all this though, I wonder how OWB will provide support for web services in the next release? SOA functionality is on the development plan for the next major release of OWB, due I guess in late 2008, I'd be curious to see whether it takes the same route as ODI (OWB will support ODI knowledge modules in the next release) or whether they make a stab at making web service data as usable in mappings as relational data? We'll have to wait and see.

Anyway, thanks again to Douglas for some of the ideas I bounced around here, and for helping me get Axis2 set up. If you're a UKOUG member, keep an eye out in the next Oracle Scene magazine for his in-depth look at ODI's web service functionality.