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.
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.