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

Uli Bethke OBIEE, Oracle Data Integrator (ODI)

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.

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.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.