A Practical Introduction to the OTBI and BI Publisher web services

A short time ago someone from ERPWebTutor users asked me to explain the ways how to utilize OTBI webservices.
BI Analyses or/and BIP reports can be considered as a simple approach for outbound integration scenarios. In that case an external application should consume OTBI webservices and the major effort will be related to the development of a webservice client to remotely run OTBI reports and fetch results to the third-party applications.
Unfortunately currently we can use only SOAP services. Even though the latest version of BIP (12c) has REST services, Oracle Fusion HCM still uses 11.1.9 version that doesn’t provide ability to use restful services.

Below you can see WSDL endpoints and services you can use for integration.

• OTBI SOAP services endpoint: https://zzzz-test.bi.zzz.oraclecloud.com/analytics/saw.dll/wsdl/v6

• BIP SOAP services endpoint: https://zzzz-test.bi.zzz.oraclecloud.com/xmlpserver/services/ExternalReportWSSService?WSDL

Worked out example

Now I would like to provide an example how to consume SOAP services from java to execute OTBI report remotely and get results in the XML format. In my mind, Java is the most suitable technique for this task because of JAX-WS API.
Java API for XML-Based Web Services (JAX-WS) tooling supports generating Java artifacts you need to develop static JAX-WS web services clients when starting with a Web Services Description Language (WSDL) file.
There is an example I used as a start point for my scenario: https://netbeans.org/kb/docs/websvc/client.html

Below the steps to get it working:

1. Login + get session ID

   SAWSessionService service = new SAWSessionService();
   SAWSessionServiceSoap port = service.getSAWSessionServiceSoap();
   String sessionId = port.logon(userName, userPass);

2. Prepare parameters for the query

   XmlViewService xmlservice = new XmlViewService();
   XmlViewServiceSoap xmlport = xmlservice.getXmlViewServiceSoap();
   ReportRef report = new ReportRef();
   report.setReportPath(reportName);
   XMLQueryOutputFormat outputFormat = XMLQueryOutputFormat.SAW_ROWSET_SCHEMA_AND_DATA;
   XMLQueryExecutionOptions executionOptions = new XMLQueryExecutionOptions();
   executionOptions.setMaxRowsPerPage(1);
   executionOptions.setPresentationInfo(true);
   ReportParams reportParams = new ReportParams();
   QueryResults results;

3. Execute XML query and logoff.

   results = xmlport.executeXMLQuery(
   report,
   outputFormat,
   executionOptions,
   reportParams,
   sessionId);

   port.logoff(sessionId);

4. Parse results

   rowset = results.getRowset();

   …

Here you can see how it works:

You can check out the source code on my GitHub page: https://github.com/vafaranosov/OTBISoapClient

I hope it helps!

Good luck and remember: “I have not failed. I’ve just found 10,000 ways that won’t work”. ~ Thomas A. Edison

SY,
Volodymyr