• No products in the cart.

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

0 responses on "A Practical Introduction to the OTBI and BI Publisher web services"

Leave a Message

Your email address will not be published. Required fields are marked *

top
ERPWebTutor
2011-2017, All rights reserved © A part of the Orison Consulting Group
PO Box 16014 San Juan Puerto Rico 00908
Email: admin@erpwebtutor.com
Terms of Use
Contact Us