* This blog was last tested on Fusion is 21A (11.13.21.01.0) by Jay Pearson (Oracle A-Team) *
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 should be considered as an exception, only when you are NOT able to obtain the data you need through the standard Fusion SaaS BICC extracts or through Fusion REST API's (Financials, HCM, Supply Chain, and Sales and Service).
Note - BI Publisher is NOT RECOMENDED as an approach for extracting large data sets.
a) Keep datasets small. i.e. less than 100 rows.
b) Run maximum 10 concurrent requests (not per second - running concurrently).
Always only use BI Subject Areas (OTBI). Never use plain SQL against the SaaS DB. The support of plain SQL against the SaaS DB may be discontinued in the near future.
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:
- BI Cloud Connector (BICC) - more details in this article. This is the recommended approach for large scale extraction of data for warehouses.
- The HCM Extract process is the preferred approach to extract data from HCM. That method is outlined here in this article.
- Data Sync may also be appropriate in some cases - partitioning data by date to reduce server load. An article on that topic 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.
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.
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;
If you would like to copy and paste:
<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
<pub:reportAbsolutePath>/Custom/Jay Pearson (A-Team)/my_bip_report.xdo</pub:reportAbsolutePath>
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 Base64.decodeBase64(base64String)
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.