Extracting Data from Oracle Analytics Cloud using SOAP

Introduction

This post provides a simple method of extracting data from Oracle Analytics Cloud (OAC) using web services.

It uses Oracle Business Intelligence Web Services as documented in Fusion Middleware Integrator’s Guide for Oracle Business Intelligence Enterprise Edition

The simple method (using HTTP) is not secure and should only be used in a development environment.

In a production environment, Oracle recommends that you enable HTTPS on the Managed Server that hosts the Analytics and BI middleware J2EE applications. Un-encrypted credentials that are passed to the target web service may be intercepted, and using SSL is a way to mitigate this risk.

These web services are the same as those used in the Oracle Transactional Business Intelligence (OTBI) capabilities provided by many Fusion Applications products as well as those in Oracle Business Intelligence Enterprise Edition (OBIEE).

A key differentiator in OAC and OBIEE 12c is the ability to use Extended Subject Areas (XSA) associated with user-defined datasets. The example included here uses such a subject area.

The following topics are covered:

Existing Data Set, XSA and Analysis in OAC

Retrieving the Formal Definitions of the OAC Services and Methods

Using API Testing Tools

Logging into OAC

Extracting Data from the Analysis

Existing Data Set, XSA and Analysis in OAC

This post uses the following objects in OAC. It does not go into how these were created.

Data Set

The 2015 Busiest Airports is a data set uploaded from an Excel workbook.

8

Extended Subject Area

OAC created this XSA from the data set.

9

Analysis

Top 10 Airports is an analysis created using the XSA.

5

10

11

Retrieving the Formal Definitions of the OAC Services and Methods

It is assumed that the OAC service has an access rule that allows port 9704 to be accessed. For more details on access rules see Controlling Access to Service Components in Administering Oracle Analytics Cloud in a Customer-Managed Environment

In a web browser such as Chrome, Firefox, IE, etc. retrieve the latest release of the web services with this URL:

http://hostname:9704/analytics-ws/saw.dll/wsdl or https://hostname/analytics-ws/saw.dll/wsdl

The response should start like this:

{“items”:[{“version”:”v12″,”isLatest”:true,”lifecycle”:”active”,”links”:[{“rel”:”canonical”,”href”:”http:\/\/hostname:9704\/analytics-ws\/saw.dll\/wsdl\/v12″}

In this example the latest version is v12Note: Contact your service administrator if you receive no results. The web services may not be enabled for the OAC instance.

Append the version to the previous URL to retrieve the Web Services Description (WSDL):

http://hostname/analytics-ws/saw.dll/wsdl/v12 or https://hostname/analytics-ws/saw.dll/wsdl/v12

The result should start with this:

12

Using API Testing Tools

The SOAP requests should be developed and tested in API testing tools such as cURL, SoapUI, and Postman. Links to these tools are provided in the References section.

Alternatively the request can be made manually from the information in the WSDL retrieved above.

Note: API testing tools such as SoapUI, CuriousConcept, Postman, and so on are third-party tools for using SOAP and REST services. Oracle does not provide support for these tools or recommend a particular tool for its APIs. You can select the tool based on your requirements.

This post uses SoapUI.

Create a new project in SoapUI using the WSDL URL retrieved above. Ensure the Create Requests checkbox is checked.

1

The services and requests for the project are displayed in the navigator.

2

Logging into OAC

The Oracle Business Intelligence Session-Based Web Services require a valid Oracle Business Intelligence session ID to be passed as a parameter. This means that the calling application first needs to make a call to get the session ID before calling the web service.

The first call uses the SAWSessionService and the logon request. This request authenticates the user and returns a session ID.

Locate the request in the navigator and expand it. Open the request in the request editor and add a username and password for a user with permission to use the OAC analysis.

3

Submit the request and receive a response envelope with the session ID that is used to call the next service that extracts from the analysis.

4

Extracting Data from the Analysis

This call uses the xmlViewService and the executeXMLQuery request to return the results of the analysis. Note: If the results returned exceed one page, you need to use the fetchNext request to return the next page of rows (not used in this post).

Locate the request in the navigator and expand it. Open the request in the request editor.

SoapUI defaults field values to “?”. These need to be removed manually or by using a text editor replace function.

The only two fields used in this post are the reportPath and the sessionID obtained with the logon call.

For the report path use /shared rather than the “Shared Folders” name displayed in the OAC catalog.

The request envelope is shown below.

6

Submit the request and receive a response envelope with the results of the analysis. Notice that the xml returned uses Column0 to Column2 for the column names instead of the column names used in the analysis.

7

Summary

This post provided a simple method of extracting data from Oracle Analytics Cloud (OAC) using web services. It detailed how to retrieve the formal definitions of the OAC Services and Methods using a browser, how to use the SoapUI API testing tool to generate sample requests, how to log into OAC to get a session ID, and how to extract the data from an OAC analysis using the session ID and the path to the analysis in the OAC catalog.

For more OAC, BICS and BI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for BICS and Oracle A-Team Chronicles for OAC

References

Fusion Middleware Integrator’s Guide for Oracle Business Intelligence Enterprise Edition

Controlling Access to Service Components in Administering Oracle Analytics Cloud in a Customer-Managed Environment

Soap API Testing Tool

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

 

Add Your Comment