Tagged ‘odi web services‘

ODI 11g in a Services Oriented Architecture. Part IV. Consuming a SOAP OBIEE web service using ODI 11g

This post completes our series on using ODI in a web services environment.

In part one of this series (Oracle Data Integrator in a Services Oriented Architecture) I had laid the groundwork and explained the basics of a SOA environment and how ODI fits into it. In part two we then looked at creating and deploying data services with ODI 11g in Weblogic. In part three we looked at how to consume data services using ODI 11g.

In this last example we will examine how we can invoke Oracle Business Intelligence Session-Based Web Services to query the Oracle Business Intelligence Presentation Services. The Oracle Business Intelligence Presentation Services allow us (amongst various other things) to retrieve catalog information such as user names, groups, object privileges etc.

The services and methods that are available can be retrieved from a WSDL at the following location


You can find a full list of all the methods and a description in the documentation

For the purpose of this tutorial we will retrieve the members of a given OBIEE catalog group.

Oracle Business Intelligence Session-Based Web Services require a valid Oracle Business Intelligence session ID to be passed as a parameter.

In a first step we will generate this session ID.

In ODI we create a new package, name it OBIEE and add an OdiInvokeWebService tool.

Next we highlight the OdiInvokeWebService tool and click the Advanced… button. This will launch the ODI web service client. Next we supply the WSDL URL, e.g. http://localhost:9704/analytics/saw.dll/wsdl/v6
and click the Connect to WSDL button. This will display all of the available web services and methods.

From the web services dropdown we select the SAWSessionServiceSoap service and highlight the logon method. In the Editor pane we submit an OBIEE username and password with SOAP Access privileges and click the Invoke Web Service button. This will return a new OBI session ID. Close the ODI web service client by clicking OK.

As parameters for the OdiInvokeWebService tool we then submit the following additional parameters.

Storage Mode for Response File: NEW_FILE
File Encoding for Response File: UTF8
XML Encoding for Response File: UTF-8
Response File: C:/obiee_session_id.xml
Response File Format: XML

Now we are ready to execute the Package using our local agent.

Once the execution of the package has finished successfully we should find the obiee_session_id.xml file on our local C:\ drive

We are now ready to extract the session ID from the XML using ODI. As in the previous example we will have to create a new physical and logical data server and a new model for this XML file.

In ODI Studio Topology insert a new XML data server in the Physical Architecture.

In the JDBC Driver field submit com.sunopsis.jdbc.driver.xml.SnpsXmlDriver and for JDBC Url submit jdbc:snps:xml?f=C:/obiee_session_id.xml

Next click the Test Connection button and test the connection with the Local Agent.

This will throw an error. We have come across ODI bug 9159263, which is documented on the My Oracle Support site in note 971754.1. In summary the ODI XML driver can not parse XML files that contain any xsi: references. The note suggests using a workaround and deleting any references to xsi: in our XML.

Let’s edit our XML and remove any instances of xsi. Your XML should look similar to the one in the figure below. Later when we extract information from the XML file in our package we will write some Jython code to replace any xsi: instances in the XML file at runtime.

Now let’s retest the connection to the XML.

Next we create the physical schema

Then we create the logical data server XML_OBIEE_SRC

Next we create a model for the XML_OBIEE_SRC logical schema.

Finally we reverse engineer the model

We are now ready to assign the session ID to an ODI variable. Create a new variable V_OBIEE_SESSION_ID. As the schema for the variable choose XML_OBIEE_SRC and for the query supply ‘select sessionid_data from OBIEE.SESSIONID’

As mentioned earlier, before we can add the ODI variable to our package we will first need to create a step that removes any xsi: instances from the obiee_session_id.xml file. We will use Jython to accomplish this.

We create an ODI procedure XSI_REPLACE and add one step “replace xsi” to it. As Technology, we select Jython.

s = open("").read()
s = s.replace('xsi:', '')
f = open("", 'w')

Next we create an ODI option FILE_PATH. The procedure we have just created will take the path to an XML file passed in via the option and replace any occurrence of xsi:

Next we will add our new procedure and the ODI variable we created earlier on to our OBIEE package. We also enter the path to the c:\obiee_session_id.xml file into the option field as per figure below.

In a final step we will add another OdiInvokeWebService tool, which we will use to retrieve the members of a given group.

As per figure below add the following piece of code to the XML Request parameter. The name BI_RPT is the name of the catalog group in OBIEE that we will retrieve the members from. You need to replace this with a catalog group that exists in your own OBIEE environment. As account type we pass in 1 for accounts of type group. As sessionID we pass in the ODI variable #V_OBIEE_SESSION_ID that we populated in an earlier step in our package.


The values for the other parameters are

WSDL URL: http://localhost:9704/analytics/saw.dll/wsdl/v6
Port Type: SecurityServiceSoap
Operation: getMembers
Storage Mode for Response File: NEW_FILE
File Encoding for Response File: UTF8
XML Encoding for Response File: UTF-8
Response File: C:/OBIEE_group_members.xml
Response File Format: XML

Leave all the other parameter fields blank.

We are now ready to execute our package and retrieve the members of the BI_RPT catalog group.

We execute the OBIEE package with our local agent

As you can see from the figure below, our package executed successfully.

Let’s have a look at the output of the XML. As you can see from the figure below there is one user in our given catalog group.

Congratulations. We have completed the walkthrough. You now know how you can use ODI in a SOA environment and should be able to query SOAP and RESTful web services using ODI.

ODI 11g in a Services Oriented Architecture? Exposing data services and consuming web services with ODI 11g. Part I.

ODI in a SOA environment

This will be a four part series/tutorial on using ODI in a SOA environment.

One of the most overlooked features in ODI in general and in ODI 11g in particular is the ability to fully integrate ODI into a SOA environment. Typically technical architects associate ODI with high data volume ELT type data loads. However, ODI is very versatile when it comes down to meeting data integration requirements. Apart from the usual high volume, high performance ELT loads it can also be used in a web services centric environment.

In this series I will outline how ODI 11g can be deployed in a SOA environment to make data stores available to clients as data services. I will also show you how you can use ODI to consume data and web services. In the article I will give a step by step guide on how to create, deploy, and test a data service through ODI on Weblogic (without writing a single line of code). For the example I will use the customer table in the OE sample schema ( On top of this, ODI can act as a consumer of web services. It can access both SOAP and RESTful web services. I will walk you through consuming both SOAP and RESTful web services using ODI. For the SOAP example we will connect to the OBIEE 11g web services API. For the RESTful web services we will consume the OpenStreet Map API to augment data in the OE schema customer table by longitude and latitude information.

Before we dive into the step by step guides, however, I first want to give a brief overview on the world of SOA, data services, SOAP and RESTful web services and how they are related to ODI.

What is SOA?

SOA is not a technology. It is rather a set of design principles for enterprise application architecture. In a SOA environment software units are loosely tied together as reusable services. They are platform independent and can be consumed by applications over standard network protocols. In a SOA environment the various web and data services are orchestrated in a workflow to implement a complex business process. The main benefit of SOA is faster application development. Already existing services can be orchestrated to quickly implement a given business process.

What are data services?

Data services expose enterprise data. Typically a data service makes data in one or more database tables available to consumers in the form of a web service. Client applications and end users can retrieve this data across physical and logical boundaries. On the web this could be across different geographies. For example this happens when you query the OpenStreet Map API. Irrespective of your location the data service returns the exact same data set. In the enterprise world this could be across business units within an enterprise. For example both the marketing and sales department can consume centrally held customer data. It could also be across enterprise boundaries, e.g. company 1 consumes product data from company 2.

What obstacles do we encounter in the implementation of data services?

Exposing data as a service without a supporting Master Data Management (MDM) program is like data warehousing without MDM. Difficult at best. Ideally the data that data services expose should be integrated, ought to be cleansed of errors, should be complete, needs to be up to date, and most not violate referential integrity. How would you feel when the longitude and latitude information changes each time you query the Open Street Map API? Good data quality is key for an environment that relies on data services. Any data services program should be accompanied by a data quality and master data management initiative. The so called golden record is a pre-requisite for a successful implementation of data services.

How are data services consumed?

Data services are typically consumed as web services. In the enterprise world you will mainly find SOAP based implementations. In the Web 2.0 world a lot of data services are now implemented as RESTful web services.

Data services and web services in ODI 11g

With ODI you can both create data services and consume them via web services. Once you have integrated, cleansed, and created the golden record of your data using ODI you can also expose this master data via data services. In turn you can consume data services via the ODI web services functionality.
When implementing data services you are not limited to read only access. You can also perform other operations on your ODI data services such as: deleting, updating, filtering.
Data service functionality has been before the latest release of ODI. However, in ODI 10g the data services had to be deployed in an Axis web services stack. This was a tedious and time consuming process. In ODI 11g it is a lot easier. ODI now supports the Java API for XML Web Services (JAX-WS) and you can deploy your data services directly to your Weblogic application server. This all happens automagically with a single click of a button. You don’t have to write a single line of code.
This means that you can use ODI to do the heavy lifting in a data warehouse environment but also in a master data management program to expose your golden record to client applications via data services.
As of ODI version you can now also use web service container based authentication. It is now also possible to use fully formed SOAP messages as an input to a web service call.

In part of using Oracle Data Integrator in a Services Oriented Architecture we will create and deploy some ODI data services in Weblogic.

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

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.

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.



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.


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.


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


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=”″>
<ns1:sessionID xmlns:xsi=”” xsi:type=”xsd:string”>5lll90nu19bbhi49u57h8vdott63gcs5j19g9vazOr07UFe9W00</ns1:sessionID>

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’)

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


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

Jython Essentials (O’Reilly Scripting)