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.
Note - BI Publisher is NOT RECOMMENDED as an approach for extracting large data sets. This article is intended to provide an option for accessing real time data for adhoc reports, or for integrations that pull limited data sets, via web-service. BI Publisher is not intended to be used as a large scale extraction tool, and will not scale. If used for this, you should not expect help from support if you run into performance issues !
For larger scale extraction of data, other approaches need to be considered. These include, but are not limited to -
The BI Cloud Connector - more details in this article. This is the recommended approach for large scale extraction of data for warehouses.
File Based Data Export - more details here.
Web-service - more details here.
Product Specific options - some fusion offerings provide specific approaches. For instance, HCM provides the HCM extract tool. More details on that here.
For OAC / BICS customers, the Data Sync tool might also be appropriate in some cases. Details on how to set up and configure it can be found in this article, and an additional article for partitioning data by date to reduce server load can be found here.
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.
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.
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.