Extracting Data from Oracle Analytics Cloud using REST

Introduction

This post provides a simple method of extracting data from Oracle Analytics Cloud (OAC) 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.

Topics

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 Public IP>/analytics

Topics

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.

1

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

2

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

3

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

4

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

5

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

6

Creating a BIP Report

From the menu bar, select Create Report.

7

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

8

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

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

9

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

10

Editing the Output Format

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

11

Click View a List

12

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

13

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. Refer to Managing Access Rules for details on creating instance access rules.

Topics

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

Notes:

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”: [
“en_US”
],
“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”: [
“_dashboardexpr”
],
“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”: [
“BIP_Top_10_Airports_Report”
]
}

Creating a REST Request to Run the BIP Report

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

Topics

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 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:

20

Creating the Body

Open the Body tab.

Check the Raw button

Enter the body text as shown below:

–Boundary_1_1153447573_1465550731355
Content-Type: application/json
Content-Disposition: form-data; name=”ReportRequest”

{“byPassCache”:”true”}
–Boundary_1_1153447573_1465550731355–

Notes: 

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:

–Boundary_309_1557716236_1522173184122
Content-Type: application/json
Content-Disposition: form-data; name=”ReportResponse”

{“reportContentType”:”text/xml”}
–Boundary_309_1557716236_1522173184122
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 12.2.4.0.0 -Dataengine, datamodel:_DLC_BIP_Top_10_Aiports_Model_xdm –>
<DATA_DS>
<G_1>
<AIRPORT_NAME> Hartsfield–Jackson Atlanta International Airport</AIRPORT_NAME><COUNTRY>United States</COUNTRY><PASSENGERS>101491106</PASSENGERS>
</G_1>
<G_1>
<AIRPORT_NAME> Beijing Capital International Airport</AIRPORT_NAME><COUNTRY>China</COUNTRY><PASSENGERS>89938628</PASSENGERS>
</G_1>
<G_1>
<AIRPORT_NAME> Dubai International Airport</AIRPORT_NAME><COUNTRY>United Arab Emirates</COUNTRY><PASSENGERS>78014841</PASSENGERS>
</G_1>
<G_1>
<AIRPORT_NAME> O&apos;Hare International Airport</AIRPORT_NAME><COUNTRY>United States</COUNTRY><PASSENGERS>76942493</PASSENGERS>
</G_1>
<G_1>
<AIRPORT_NAME> Tokyo Haneda Airport</AIRPORT_NAME><COUNTRY>Japan</COUNTRY><PASSENGERS>75316718</PASSENGERS>
</G_1>
<G_1>
<AIRPORT_NAME> London Heathrow Airport</AIRPORT_NAME><COUNTRY>United Kingdom</COUNTRY><PASSENGERS>74989795</PASSENGERS>
</G_1>
<G_1>
<AIRPORT_NAME> Los Angeles International Airport</AIRPORT_NAME><COUNTRY>United States</COUNTRY><PASSENGERS>74937004</PASSENGERS>
</G_1>
<G_1>
<AIRPORT_NAME> Hong Kong International Airport</AIRPORT_NAME><COUNTRY>China</COUNTRY><PASSENGERS>68283407</PASSENGERS>
</G_1>
<G_1>
<AIRPORT_NAME> Paris-Charles de Gaulle Airport</AIRPORT_NAME><COUNTRY>France</COUNTRY><PASSENGERS>65766986</PASSENGERS>
</G_1>
<G_1>
<AIRPORT_NAME> Dallas/Fort Worth International Airport</AIRPORT_NAME><COUNTRY>United States</COUNTRY><PASSENGERS>65512163</PASSENGERS>
</G_1>
</DATA_DS>
–Boundary_309_1557716236_1522173184122–

Notes:

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 Public IP>:<Your OAC Port>
Content-Type: multipart/form-data; boundary=”Boundary_1_1153447573_1465550731355″
Authorization: Basic YWRtaW46V2VsY29tZTEj
Cache-Control: no-cache

–Boundary_1_1153447573_1465550731355
Content-Type: application/json
Content-Disposition: form-data; name=”ReportRequest”

{“byPassCache”:”true”}
–Boundary_1_1153447573_1465550731355–

Summary

This post detailed a simple method of extracting data from an OAC 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 Oracle A-Team Chronicles for BICS and Oracle A-Team Chronicles for OAC.

References

API Testing POSTMAN Download

API Testing using POSTMAN

REST API for Oracle BI Publisher

‎Extracting Data from Oracle Analytics Cloud using SOAP

Managing Access Rules

Report Designer’s Guide for Oracle Business Intelligence Publisher

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

Add Your Comment