Best Practices from Oracle Development's A‑Team

Extracting Data from Oracle Analytics Cloud using REST

Note: This post is only for Oracle Analytics Cloud - Classic Infrastructure.


This post provides a simple method of extracting data from Oracle Analytics Cloud (OAC-C) using Business Intelligence Publisher (BIP) REST web services. Refer to REST API for Oracle BI Publisher for details.

It builds upon and uses the analysis from the post ‎Extracting Data from Oracle Analytics Cloud using SOAP

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.


  • Creating a BIP Report Definition

  • Using API Testing Tools

  • Creating a REST Request to Retrieve the BIP Report Definition

  • Creating a REST Request to Run the BIP Report

Creating a BIP Report Definition

Access the Analytics home page using the URL https://<Your OAC-C Public IP>/analytics


  • Creating a BIP Data Model

  • Creating a BIP Report

  • Editing the Output Format

Creating a BIP Data Model

From the menu bar, select New>Published Reporting Data Model.


For the Data Sets properties, in the Diagram pane, create a New Data Set. Choose Oracle BI Analysis.


Enter a Name and choose the Analysis created in the previous blog from Shared Folders/DLC/Top 10 Airports


In the Structure pane, rename the XML Tag Names to reflect the Analysis Display Names using underscores to replace spaces.


In the Data pane, click View, Save as Sample Data and OK.


Click Save. Enter a location, e.g. Shared Folders/DLC, enter a Name and click OK.


Creating a BIP Report

From the menu bar, select Create Report.


Click Use Data Model, choose the data model just created and click Next.


Keep the defaults (Table) for Layout and click Next.

In the Create Table pan, drag the columns over and click Next


From the Save Report pane and click Finish. Give the report a Location, a Name, and click OK.


Editing the Output Format

While viewing the report, use the Actions icon to select Edit Report


Click View a List


In the Output Formats drop down – deselect everything except for ‘XML’


Click Save.

Using API Testing Tools

The REST requests should be developed in API testing tools such as cURL and Postman.

This post uses Postman. Download and install Postman with these links:

API Testing POSTMAN Download and API Testing using POSTMAN

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.

Creating a REST Request to Retrieve the BIP Report Definition

Parts of the BIP Report Definition are used to call the BIP report if parameters values are used. This post does not use parameters.

This post uses HTTP. The BIP REST API requires access to HTTP port 9704. This port needs to be opened via an access rule in OAC-C. Refer to Managing Access Rules for details on creating instance access rules.


  • Creating a New Collection

  • Adding a New Request

  • Adding an URL

  • Adding an Authorization Header

  • Retrieving the BIP Report Definition


Creating a New Collection

Click the CreateCollection icon to create a new collection.

Enter a Name and Save the collection.

Adding a New Request

Click Add Request.

Enter a Request Name e.g. GET_BIP_DEFINITION

Select your collection and click Save to <Your Collection>

Adding an URL

Add the URL with this format:

http://<Your OAC-C IP>:<Your BI Server HTTP port>/xmlpserver/services/rest/v1/reports/path/reportname
For example:
http://<Your OAC-C IP>:9704/xmlpserver/services/rest/v1/reports/DLC%2FBIP_Top_10_Airports_Report


The catalog location of the first report is Shared Folders/DLC/BIP_Top_10_Airports_Report. The top level shared folder in the catalog, Shared Folders, is assumed. The starting point is the folder named below that i.e. DLC.

The URL must be HTML encoded to be sent over the internet. Any space character in the path is replaced with %20 and any slash character i.e. / is replaced with %2F.

The folder and BIP report names are case-sensitive.

Adding an Authorization Header

Click Authorization.

Select a Type of BasicAuth

Enter a Username and Password and click Update Request.

Retrieving the BIP Report Definition

Set the command to Get, and click Send. The response returns in JSON format as shown below:

"ESSJobName": "",
"ESSPackageName": "",
"autoRun": true,
"cacheDocument": true,
"controledByExtApp": false,
"dataModelURL": "/DLC/BIP_Top_10_Aiports_Model.xdm",
"defaultOutputFormat": "xml",
"defaultTemplateId": "BIP_Top_10_Airports_Report",
"diagnostics": false,
"listOfTemplateFormatsLabelValues": [
"active": true,
"applyStyleTemplate": true,
"default": true,
"listOfTemplateFormatLabelValue": [
"templateFormatLabel": "Data",
"templateFormatValue": "xml"
"templateAvailableLocales": [
"templateDefaultLocale": "en_US",
"templateID": "BIP_Top_10_Airports_Report",
"templateType": "xpt",
"templateURL": "BIP_Top_10_Airports_Report.xpt",
"viewOnline": true
"onLine": true,
"openLinkInNewWindow": true,
"parameterColumns": 3,
"parameterNames": [
"reportDefnTitle": "",
"reportName": "BIP_Top_10_Airports_Report",
"reportParameterNameValues": [
"UIType": "Hidden",
"dataType": "xsd:string",
"defaultValue": "",
"multiValuesAllowed": false,
"name": "_dashboardexpr",
"refreshParamOnChange": false,
"selectAll": false,
"templateParam": false,
"useNullForAll": false
"showControls": true,
"showReportLinks": true,
"templateIds": [

Creating a REST Request to Run the BIP Report

This creates the request to run the BIP report and extract the data.


  • Duplicating the Request

  • Adding an Additional Header

  • Creating the Body

  • Sending the Request and Reviewing Results

  • Using the REST Report Request

Duplicating the Request

Duplicate the request above and rename it e.g. RUN_BIP_REPORT.

Change the command in the upper left to Post.

Add /run to the URL e.g.

http://<Your OAC-C IP>:9704/xmlpserver/services/rest/v1/reports/DLC%2FBIP_Top_10_Airports_Report/run

Adding an Additional Header

For the additional header, enter a Key of Content-Type.

Enter a Value of multipart/form-data; boundary=“Boundary_1_1153447573_1465550731355”Note: Include the quotes. The boundary value entered here in the header is for usage in the body below. The boundary text may be any random text not used elsewhere in the request.

The two headers are shown below:


Creating the Body

Open the Body tab.

Check the Raw button

Enter the body text as shown below:

Content-Type: application/json
Content-Disposition: form-data; name="ReportRequest"



The boundary text specified in the header is the first and last line of the body structure. All boundary lines must be prefixed by the “--” string. Additionally, the closing boundary line must also be suffixed with the “--” string.

The Content-Type: application/json line specifies the request format.

The Content-Disposition: form-data; name=“ReportRequest” line specifies that the text following the blank line are the non-default items and values to be used for the run.

The JSON request text specifies the cache is bypassed.

Sending the Request and Reviewing Results

Click Save and Send.

The XML results are shown below:

Content-Type: application/json
Content-Disposition: form-data; name="ReportResponse"

Content-Type: application/octet-stream
Content-Disposition: form-data; filename="xmlp9011434949132240371tmp"; modification-date="Tue, 27 Mar 2018 17:53:03 GMT"; size=1570; name="ReportOutput"

<?xml version="1.0" encoding="UTF-8"?>
<!--Generated by Oracle BI Publisher -Dataengine, datamodel:_DLC_BIP_Top_10_Aiports_Model_xdm -->
<AIRPORT_NAME> Hartsfield–Jackson Atlanta International Airport</AIRPORT_NAME><COUNTRY>United States</COUNTRY><PASSENGERS>101491106</PASSENGERS>
<AIRPORT_NAME> Beijing Capital International Airport</AIRPORT_NAME><COUNTRY>China</COUNTRY><PASSENGERS>89938628</PASSENGERS>
<AIRPORT_NAME> Dubai International Airport</AIRPORT_NAME><COUNTRY>United Arab Emirates</COUNTRY><PASSENGERS>78014841</PASSENGERS>
<AIRPORT_NAME> O&apos;Hare International Airport</AIRPORT_NAME><COUNTRY>United States</COUNTRY><PASSENGERS>76942493</PASSENGERS>
<AIRPORT_NAME> Los Angeles International Airport</AIRPORT_NAME><COUNTRY>United States</COUNTRY><PASSENGERS>74937004</PASSENGERS>
<AIRPORT_NAME> Dallas/Fort Worth International Airport</AIRPORT_NAME><COUNTRY>United States</COUNTRY><PASSENGERS>65512163</PASSENGERS>



The result section is separated by system-generated boundary lines.

The XML output is shown above the closing boundary line.

Using the REST Report Request

Click Code to get the following which can be used with any tool issuing a REST API request.

POST /xmlpserver/services/rest/v1/reports/DLC%2FBIP_Top_10_Airports_Report/run HTTP/1.1
Host: <Your OAC-C Public IP>:<Your OAC-C Port>
Content-Type: multipart/form-data; boundary="Boundary_1_1153447573_1465550731355"
Authorization: Basic YWRtaW46V2VsY29tZTEj
Cache-Control: no-cache

Content-Type: application/json
Content-Disposition: form-data; name="ReportRequest"



This post detailed a simple method of extracting data from an OAC-C instance using BI Publisher and the BI Publisher Rest API.

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 http://www.ateam-oracle.com/dayne-carley

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha