Using Oracle BI Publisher to Extract Data From Oracle Sales and ERP Clouds

Introduction

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.

Main Article

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.

Untitled

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.

Answers_Prompt

 

Go to XMLPServer

Within the XMLPServer, edit the BI Publisher report.

In the upper right, select ‘View a List’

Add_New_Post_‹_ATeam_Chronicles_—_WordPress

And then in the ‘Output Formats’ drop down – deselect everything except for ‘XML’:

Add_New_Post_‹_ATeam_Chronicles_—_WordPress

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.

BIP_Parameter

 

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

 

For example:

https://cloudlocation.oracle.com/xmlpserver/services/ExternalReportWSSService?wsdl

or

https://my-on-premise-server.com/xmlpserver/services/ExternalReportWSSService?wsdl

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;

Add_New_Post_‹_ATeam_Chronicles_—_WordPress

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:

Add_New_Post_‹_ATeam_Chronicles_—_WordPress

Run the WSDL and the report is returned as an XML file, encoded in Base64 format, within the <reportBytes> tag:

Add_New_Post_‹_ATeam_Chronicles_—_WordPress

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.

SoapUI_5_2_1

Summary

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.

Add Your Comment