Many Cloud products such as Oracle Sales Cloud, and Oracle ERP Cloud come packaged with Oracle Transaction Business Intelligence (OTBI). OTBI allows users to create and run reports against both Transactional and Warehouse databases. At times there may be a need to extract that data to an external system. On-Premise customers can create ETL jobs to run against the Database, but would need to figure out how everything joins together – the RPD does a great job in obfuscating that for the end user. For Cloud customers, it’s going to be even more complicated getting access to the source databases.
This post will cover a method for calling a BI Publisher report (a component of OTBI), via SOAP web services, and returning an XML file with the report data (embedded as a Base64 encoded content).
This approach can be used as an alternative to using the standard SOAP APIs documented in https://fusionappsoer.oracle.com/oer/ and as a basis to extract data automatically from your OTBI, albeit in the Cloud or On-Premise.
For this demonstration, the assumption is being made that the reader has access to a user with the minimum of BI Author rights to create and run BI Publisher .
Create a simple BI Publisher report and name it ‘BIP_demo_report’ and save it into the shared location:
e.g. /Shared Folders/Custom/demo , make not of this path as it will be referenced later in the WSDL.
If the BIP report is based on a BI analyses report (see this article) and if you wish to pass a filter from the BIP report, then create a Prompt for the field(s) in the Analyses report. This is done in the ‘Prompts’ section. Set the User Input to be ‘Choice List’, and make sure ‘Enable user to type values’ is checked. Note – in this example, the field the filter was based on was called ‘Level4’. This will be referenced later.
Go to XMLPServer
Within the XMLPServer, edit the BI Publisher report.
In the upper right, select ‘View a List’
And then in the ‘Output Formats’ drop down – deselect everything except for ‘XML’:
Make sure you ‘Save’ the changes.
If a filter is being passed, select the ‘Parameters’ menu and make a note of the ‘Name’. In this case it is named ‘saw.param.Level4’. This will be referenced later.
Building Web Service Request
To create and test the Web Service, this post will use the opensource tool SoapUI. This is free and can be downloaded from https://www.soapui.org
In SoapUI create a new Soap Project. The WSDL you should use is the Cloud, or On-Premise URL with the BIP suffix “xmlpserver/services/ExternalReportWSSService?wsdl” appended
This will generate a Soap UI project and list the available methods available from the WSDL.
Take a copy of the ‘runReport’ command and edit it. There are a number of parameters available, but in this example we will remove the majority of those and just leave the basic attributes as shown in this example;
This includes the path of the report. There is an assumption that the report is in the /Shared folder, so that’s not included in the path. The suffix ‘.xdo’ is also required.
This service follows Oracle’s OWSM policy. Within SoapUI you’ll also need to enter the user authentication details in the request properties:
Run the WSDL and the report is returned as an XML file, encoded in Base64 format, within the <reportBytes> tag:
The base64 payload can easily be parsed by most programming languages, for example in Java you would use the Base64 class
If a filter is being passed, then add the following XML to the request, being sure to update the parameter name to match the name identified previously. Use the <pub:item> tag to pass in the value you wish to filter on. If you wish to pass in multiple values for the filter, add multiple <pub:item> lines, as shown below.
This post demonstrated a method which can be used to leverage BI Publisher within an Oracle Application that is packaged with OTBI – beit cloud or on-premise – and extract the data through a web service for a downstream process.
All site content is the property of Oracle Corp. Redistribution not allowed without written permission