Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – APEX Integration using Web Services – Triggering Ibots

Posted by Venkatakrishnan J on May 7, 2008

As you might probably be aware, APEX (Application Express) has support for web services. One can call web services and can create several sequential processes out of them. There can be lots of use cases for these web service calls. One of them is to have a custom integration of BI EE within APEX environment. If you had worked on APEX and BI Publisher integration (using Web Services) as documented here excellently by Tyler Muth you would have noticed that APEX can identify BI Publisher web services out of the box since APEX web services have only a single binding i.e all the methods can be identified by using a single root service. But in the case of BI EE, it produces a multiple binding WSDL. APEX does not support multiple binding web services currently. So, the question is how do we go about calling BI EE methods from within APEX? Thats where a tool called SOAP UI comes to our rescue. I came across this tool while going through Tyler’s blog entry above and found this free tool to be very useful to identify end points of SOAP requests. Till now, i was using another tool called Mindreef SOAPScope. Though mindreef is also good, the major drawback was that they did not give a free version. They only provide you an evaluation copy. Use the SOAP UI tool and create a project using the BI EE WSDL below.

http://localhost:9704/analytics/saw.dll?WSDL

If you want you can also include the BI Publisher WSDL to know the difference between a single binding and Multi binding web services.

    

Now, just to get us started, lets try to authenticate into BI EE using the Logon Method of the SAWSession service and then use that session to execute an ibot by using the excuteIBotnow method of the IBotService from within Application Express. Since APEX, does not support multi binding web services, we would have to treat each and every service (different bindings) of the BI EE WSDL as a seperate WSDL in itself. So, go to an APEX application and click on Shared Components -> Logic -> Web Service References. Now click on create and do not search for the WSDL in the UDDI. In the next step, instead of entering the WSDL click on create Web Service Reference Manually. Remember, we would have to create one reference for each and every web service. Following are the URLs that you would have to use for each service


XMLViewService - http://localhost:9704/analytics/saw.dll?SoapImpl=xmlViewService
SecurityService - http://localhost:9704/analytics/saw.dll?SoapImpl=securityService
WebCatalogService - http://localhost:9704/analytics/saw.dll?SoapImpl=webCatalogService
SAWSessionService - http://localhost:9704/analytics/saw.dll?SoapImpl=nQSessionService
JobManagementService - http://localhost:9704/analytics/saw.dll?SoapImpl=jobManagementService
ReplicationService - http://localhost:9704/analytics/saw.dll?SoapImpl=replicationService
ReportEditingService - http://localhost:9704/analytics/saw.dll?SoapImpl=reportService
IBotService - http://localhost:9704/analytics/saw.dll?SoapImpl=ibotService
HTMLViewService - http://localhost:9704/analytics/saw.dll?SoapImpl=htmlViewService
MetadataService - http://localhost:9704/analytics/saw.dll?SoapImpl=metadataService

In our case since we want to authenticate and trigger an ibot, we would have to use 2 services. They are SAWSession service and IBotService. While creating a web service reference for the saw session service, use the below XML (you can obtain this from the SOAP UI tool).


<soapenv:Envelope xmlns:soapenv="<a href="http://schemas.xmlsoap.org/soap/envelope/">http://schemas.xmlsoap.org/soap/envelope/</a>" xmlns:v5="com.siebel.analytics.web/soap/v5">
   <soapenv:Header/>
   <soapenv:Body>
      <v5:logon>
         <v5:name>Administrator</v5:name>
         <v5:password>welcome1</v5:password>
      </v5:logon>
   </soapenv:Body>
</soapenv:Envelope>

In the above XML, Administrator and welcome1 are the username/passwords for my BI EE instance. If you want you can replace the actual values with #ITEMNAME# to populate them dynamically based on page/application items.

    

Remember, to enter a collection name for storing the reponse. This is very important since we would have to extract the session id and pass it on to another service. In my case, i had used WSDL_COLLECTION as the name of the collection (Dont worry about creating these collections. These are automatically created. All you need to do is to enter the name. you can get what is stored in the collection by querying the APEX_COLLECTIONS view). Similarly use the below XML for the executeiBotnow method of the IBotService.

 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v5="com.siebel.analytics.web/soap/v5">
<soapenv:Header/>
<soapenv:Body>
<v5:executeIBotNow>
<v5:path>/users/administrator/_ibots/Test</v5:path>
<v5:sessionID>#F101_SESSIONID#</v5:sessionID>
</v5:executeIBotNow>
</soapenv:Body>
</soapenv:Envelope> 

Here, replace the v5:path tag value with the ibot path that you need to execute. Since session ids are dynamic, we would have to reference an application level item (F101_SESSIONID) which would be populated by the SAWSession service. Again, enter a collection name like WSDL_COLLECTIONSCHED for storing the result. Once these 2 web service references have been created, create a page process to call the Logon Method of the SAWSession service.

    

Make this service to be conditionally called on the submit of a button (Login Button). Whenever each of the service is called from within APEX, the result is stored in a collection. Since this stores the result of a web service response, it would be stored as a CLOB in the CLOB001 column of the APEX_COLLECTIONS view. Typically the response of the 1st service would be like the one shown below.

<?xml version="1.0" encoding="UTF-8" ?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:sawsoap="com.siebel.analytics.web/soap/v5">
<soap:Body>
<sawsoap:logonResult>
<sawsoap:sessionID xsi:type="xsd:string">ihdcqbtmm8q06ii2opb7qhh44npfqh5v0liad5izOr07UFe9W00</sawsoap:sessionID>
</sawsoap:logonResult>
</soap:Body>
</soap:Envelope>

As you see the sessionid would be embedded in the SOAP response. The most common method to extract the session id is to write a simple XPath query with the corresponding namespaces. But in our case, we would not be doing that since the resultant SOAP response is a constant. So, let write a simple query as shown below to extract the session id from the above XML.

SELECT SUBSTR(DBMS_LOB.SUBSTR(CLOB001,4000,1),392,INSTR(DBMS_LOB.SUBSTR(CLOB001,4000,1),'</sawsoap:sessionID>')-392) FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'WSDL_COLLECTION'

Use the above query to populate the F101_SESSIONID application level item. Once this is done, create another page process to call the executeIBot method of the IBotservice. Make this process to be executed after the execution of SAWSessionservice page process. Now this would automatically trigger the ibot.

    

This should give you an idea of how to go about calling BI EE web services from APEX. Lets look at more complex scenarios lin future blog entries.

Advertisements

3 Responses to “Oracle BI EE 10.1.3.3/2 – APEX Integration using Web Services – Triggering Ibots”

  1. Sid said

    Venkat,

    Thanks!! Good post!! It was seriously required given the scarcity of examples of OBIEE SOAP interaction. Regarding the soap testing you can use a free tool called soapui (http://soapui.org) and give it a try. This looks interesting.

  2. zhaoxin said

    Hi Venkat,
    I am from china, and I’m calling web service to integrate the report of biee with j2ee project.
    I can get the report in my jsp page, when I am trying to access the links from report, i am getting( Access Prohibited You are not currently authorized to use Oracle BI Interactive Dashboards. If you would like to use this powerful capability, please contact this site’s administrator.
    )

    I have tried for a long time, but…
    Can you help me?
    Thanks in advaced!
    xzhao@wiscom.com.cn

    • Somnath De said

      This happens when you have setup the session using SOAP method calls. The issue is the Session COOKIE that needs to be created to ensure security is missing in your calls. Now how to resolve this issue.

      First you have Mozilla Firefox then using that log in to OBI and check which are the COOKIE parameters (use view cookies 1.10.1) that are being passed in every call. Now check the COOKIE details for the J2EE call as well. You will see some of the COOKIE parameters are not set from J2EE. Just set them manually in the session from your J2EE application code. This normally solve the issue.

      Regards,
      Somnath

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: