Oracle Data Integrator (ODI) web services (SOAP client via ODIInvokewebservice) and the OBIEE web services API.

July 31, 2010

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes

As outlined in a previous post ODI has a built in SOAP client via the ODIInvokewebservice tool. One of its limitations is that it doesn’t run on Java 6.

Another limitation is that it doesn’t like SOAP headers. So if you are used to SOAP headers you find in tools such as soapUI you are out of luck for the moment. Note 1143755.1 explains that an enhancement request has been logged to include this in a later release, but as of release 10GR3 it is not supported.

So what use is the ODIInvokewebservice? You could, e.g. query the Amazon product advertising API or the ebay equivalent. In this post I’ll show you how you can query the OBIEE web services API. I am currently working on a project to extract the group, user, privilege etc. information from the OBIEE presentation catalog. This is useful to automate the documentation of group hierarchies, inherited privileges etc. I’ll keep you posted on the progress.
[big_data_promotion] The OBIEE web services API does not like Single Sign On (SSO). If you are using IIS as your application server you need to create a second virtual directory and disable integrated security.

obiee_web_services_api1
obiee_web_services_api2

Once this is done we are ready to go. Create a new package in ODI and add an ODIInvokewebservice tool. On the General tab click on the Advanced… button. This will bring up the ODI SOAP client.

obiee_web_services_api3

In the URL field type in the path to the OBIEE WSDL file: http://”server_name”/analyticsSOAP/saw.dll?wsdl. This should point to the new virtual directory in the step above. Next click the connect to WSDL icon.

obiee_web_services_api4

This will return all of the services and methods that are available from the OBIEE web service API. The web service we are interested in is the SAWSessionServiceSOAP and in particular the logon method. Each request to the OBIEE web services API needs to be authenticated and the logon method returns a sessionID for us.

Populate name and password with username and password of an OBIEE account with SOAP privileges and click the Invoke web service icon

obiee_web_services_api5

This will return the sessionID that we can use in other requests to the OBIEE API later on.

Click on OK to return to the previous screen. On the General tab you can then define a response file that ODI writes the returned XML to from your web service call.

This will produce an XML file in the specified location similar to the one below:

<?xml version=”1.0″ encoding=”UTF-8″?>
<ns1:logonResult xmlns:ns1=”com.siebel.analytics.web/soap/v5″>
<ns1:sessionID xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:type=”xsd:string”>5lll90nu19bbhi49u57h8vdott63gcs5j19g9vazOr07UFe9W00</ns1:sessionID>
</ns1:logonResult>

We can then extract and load the sessionID via an ODI Interface into a database or temporarily store in the hsqldb odi memory engine.

There is one issue with this, however. The ODI XML parser doesn’t like xsi attributes such as xsi:type. So before we can reverse engineer the logon.xml from above we need to get rid of any occurrence of xsi:type in the logon.xml file. I have written an ODI procedure in Jython that does exactly that. As a parameter it takes the file path to the logon.xml and replaces any occurrence of xsi:. Of course, if you are on Linux you should use sed & awk to do this as performance is better.

s = open(“<%=odiRef.getOption(“FILE_PATH”)%>”).read()
s = s.replace(‘xsi:’, ”)
f = open(“<%=odiRef.getOption(“FILE_PATH”)%>”, ‘w’)
f.write(s)
f.close()

Now we are ready to reverse engineer the XML and load the sessionID into a relational database. These steps are well documented so I refer you to the “Oracle by Example Series: Oracle Data Integrator” site or to Craig Stewart’s ODI video tutorials.

If you want to master scripting in ODI get the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)