Over the past few weeks I've covered some of the new features in OWB11gR2 including heterogeneous sources and targets, code template mappings, hybrid mappings, change data capture and OBIEE integration. 11gR2 comes with a bunch of other interesting new features including data chunking (automatic parallelization of PL/SQL mappings), orphan management for dimension loading, support for EJBs in process flows and enhanced configuration management, but one feature I particularly wanted to blog about was the ability of OWB11gR2 to publish and consume web services.
This feature works in two ways. Mappings, process flows, data auditors and transformations can be published as web services, as well as tables (more on this later on). Process flows can also now consume web services, so that data can be brought in from an external source and copied in to staging tables. The process works through publishing the mapping, process, data auditor or transformation to either OC4J Standalone or Oracle Application Server (though curiously, not Weblogic which is now the standard for the rest of the Oracle BI stack), so that it can either be called from an external process or a remote installation of Warehouse Builder. So how does it work?
To keep things simple, I created a basic mapping that copied a few rows of data from a source table to a target table. The web service integration works with both classic and code template mappings, and this one was created as a straightforward classic (database-resident) mapping.
I then deploy this mapping to the target database as usual.
Now it's time to set the web service up. To start, I create a new Application Server module within the OWB11gR2 Project Explorer, a module type that's new for this release.
I connect to the bundled OC4J instance that comes with OWB11gR2, and enter the connection details.
Once this module is set up, I can see it in the Project Explorer along with entries for individual web services and web service packages.
At this point I can add two types of web service. I can register external web services, via a WSDL file and a URL, so that I can reference them in process flows within my project. Or I can do what I'm going to do now, and register one of the mappings, process flows, transformations or data auditors as a web service, so that I can call them from outside Warehouse Builder or indeed through one of my own process flows (I'll explain why in a moment).
The simplest way to make a mapping available as a web service is to right-click on it and select Create as Web Service. I do this with the mapping I created earlier.
This brings up an initial dialog where I select the application server to deploy it to. I select the local OC4J instance that I registered in the project earlier.
Warehouse Builder creates the new web service in the application server module. The project now looks like this.
Double-clicking on the new web service shows the options for web services within OWB. For this entry though, the choices are greyed out and all I can do is view what's already been set.
Creating a new web service from scratch though, without right-clicking on an existing module, makes all of the options available. Interestingly, there's an option to Create from a Table or Module for Change Data Capture.
In Oracle Data Integrator, creating a web service over an existing table or view makes it available for insert, update, select or delete via web service calls (see here for more information on ODI and web services). In OWB11gR2 though, creating web services off of tables or database modules provides methods for starting and stopping changed data capture, extending and purging the capture window, or adding or removing subscribers, which answers the question in my previous blog post on change data capture as to how these tasks are performed programmatically. What presumably you'd do then is publish these web service methods and then call then from within a process flow to extend and purge the CDC subscriber window, a bit of a roundabout method but one that replicates the module functionality in ODI.
Going back to my previous web service, I now need to generate the code to implement the web service. I select the web service module and select Generate....
This generates the code and reports back on the success or failure of the task.
Everything looks OK so far. I then deploy the web service, again by right-clicking on the entry in the Project Explorer and selecting Deploy... (I could have used the Control Center Manager instead, if I liked).
Again the task seemed to execute properly, with no reported errors.
Before you can run a web service built using an OWB mapping, you need to create an OC4J user that you then assign an execute privilege to. You do this from the main Design Center menu, selecting J2EE User Management.
Using this interface I create a new user, and assign it the OWB_J2EE_EXECUTOR role, which comes pre-created in the bundled OC4J instance that comes with OWB11gR2, but which you'll need to create beforehand in standalone installations.
So now I'm ready to roll. I could execute the web service from a URL, or from the OC4J Console that comes with the OC4J instance, like this:
But the easiest way to test it is to run it from within the Control Center Manager, where I can just right-click on it and select Start.
Starting the web service brings up a menu where i can select the method to be invoked. I select the Run method and press OK.
The web service then runs and completed successfully.
Finally, checking the contents of the target table, the rows from the source table have been loaded as expected.
Consuming a web service is a fairly straightforward process, and a web service called in this way would typically either move data into a staging area, transform some data or in the case below, extend the subscriber window for a CDC operation.
When you add a web service invocation to a process flow, you are presented with a list of the methods available. Once you've selected the method you are interested in, the web service then appears in the process as just another task giving you an easy way to call external processes or perform administration around change data capture.
So there you have it. If you happen to be in Helsinki on 5th November, or at our Training Days later in October, I'll be going through all of these new features in a lot more detail. Until then, if you get to use anything new in OWB11gR2 on a project, be sure to leave some feedback, and when things are a bit quieter around here I'll finish off the series by looking at data chunking and orphan management.