Best Practices from Oracle Development's A‑Team
-
Analytics & Reporting,
PaaS /
Analytics Cloud
March 27, 2018
Extracting Data from Oracle Analytics Cloud using REST
Note: This post is only for Oracle Analytics Cloud - Classic Infrastructure.
Introduction
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.
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-C 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.
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.
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 
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
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-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:
--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'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-C Public IP>:<Your OAC-C 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-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