Extract Data from Fusion Applications via Web Services

Introduction

The BI tools packaged with Fusion allow a user to create and run reports against both the Transactional, and Warehouse databases, but  sometimes there may be a need to extract some of that data to an external system. On-Premise customers could create ETL jobs to run against the Fusion 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 customer, it’s going to be even more complicated getting access to the source databases.

This post will cover two different methods to call reports created in Fusion Applications through web services.  The first using a BI Answers report, and in a two-step process, returning its output as an XML stream. The second calling a BI Publisher report, in a single step, returning an XML file with the report data embedded as a Base64 encoded field.

Both of these methods could be used as a basis to extract data automatically from your Fusion system, beit in the Cloud or On-Premise.

Main Article

For this demonstration, the assumption is being made that the reader has access to a Fusion user with the minimum of BI Author rights to create and run BI Publisher and Answers reports.

Create a simple BI Publisher report and name it ‘BIP_demo_report’ and save it into the shared location:

/Shared Folders/Custom/demo

Do the same, creating a BI Answers report named ‘Answers_demo_report’, and save it in the same shared location.

1_Catalog_Reports

To create and test the Web Service, this post will use the tool SoapUI.  This is free and can be downloaded here:

http://sourceforge.net/projects/soapui/files/

BI Answers Report

Within SoapUI, create a new SOAP project.  For the Initial WSDL address, use the Fusion Cloud, or Fusion On-Premise URL, appending  ‘/analytics-ws/saw.dll/wsdl/v7′

So for example:

https://cloudlocation.com/analytics-ws/saw.dll/wsdl/v7  or  https://my-fa-on-premise-server.com/analytics-ws/saw.dll/wsdl/v7

Calling the BI Answers report is a 2 step process.  The first web-service call authenticates and returns a session ID.  The second calls the BI Answers report, passing in the session ID, which then returns the data as XML.

Within SoapUI, expand out the ‘SAWSessionService’ and then ‘logon’.  Make a copy of the example ‘Request’ WSDL, then update it to add the username and password for a user with credentials to run the BI Answers report.

Run that WSDL and a sessionID is returned -

SoapUI_4_6_4

For the second part of the call, in SoapUI expand ‘XmlViewService’ / ‘executeXMLQuery’.  Make a copy of the example ‘Request’ WSDL.  Edit that, insert the BI Answers report name and path into the <v7:reportPath> variable, and the SessionID from the first step into the <v7:sessionID> variable.

You will notice a number of other options available.  For this example we are going to ignore those.

You can then run the WSDL.  The report returns the data as an XML stream:

3

 BI Publisher Report

Within the XMLPServer, edit the BI Publisher report.

In the upper right, select ‘View a List’

4

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

5

Make sure you ‘Save’ the changes.

In SoapUI create a new Soap Project, appending  ‘xmlpserver/services/ExternalReportWSSService?wsdl’ to the Fusion URL. For example:

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

(note – this is the supported web services for the R8 version of Fusion and beyond)

Complete the process of creating the project.

In the ‘runReport’ command, make a copy of the existing example and edit it.  Once again there are a number of parameters you can use, but in this example we will remove the majority of those and just leave the basic attributes as shown in this example;

SoapUI_4_6_4

This includes the path of the report.  Notice some differences from the previous example.  This time 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:

SoapUI_4_6_4 2

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

2

Extract that, decode, and then parse it.

Summary

This post demonstrated two methods to call existing BI reports within a fusion applications environment – beit cloud or on-premise – and extract the data through a web service.

Add Your Comment