Extracting Data from Oracle Business Intelligence 12c Using the BI Publisher REST API

Introduction

This post details a method of extracting data from an Oracle Business Intelligence Enterprise Edition (OBIEE) environment that is integrated with Oracle Business Intelligence Publisher (BIP) 12c. The environment may either be Cloud-Based or On-Premise.

The method utilizes the BI Publisher REST API to extract data from a BIP report. It also uses BIP parameters to filter the result set.

It builds upon the A-Team post Using Oracle BI Publisher to Extract Data From Oracle Sales and ERP Clouds. That post uses SOAP web services to accomplish the same result.

Note: The BI Publisher REST API is a new feature in the 12c version and functions only when accessing a BIP 12c environment.

The steps below depict how to build the extract.

Utilize an existing BI Analysis and BI Publisher Report

This post uses the analysis, filter and BI Publisher report from the post Using Oracle BI Publisher to Extract Data From Oracle Sales and ERP Clouds. Note: This post uses a filter named Analysis rather than the one named level4.

Create a REST Request to Retrieve the BIP Report Definition

This step sends a REST request to retrieve information necessary to actually call the report. Specifically, the xml portion needed for the parameter.

This post uses the Postman API testing utility as noted in the References section at the end of this post.

1. Create a new Collection

The collection is created by clicking the icon shown below:

BIP_POSTMAN_COLLECTION

Note: Enter a name and save the collection.

2. Add URL

Add the URL with this format: http(s)://hostname/xmlpserver/services/rest/v1/reports/path/reportname

For example: http(s)://hostname/xmlpserver/services/rest/v1/reports/custom%2FBIP_DEMO_REPORT

Notes:

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

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 URL for this report is shown below:

BIP_POSTMAN_URL

3. Add Authorization Header

Click on Headers as shown in the figure above.

Enter a key of Authorization.

For the value use a Base64 encoded username and password prefixed with “Basic “. To obtain the encoding, this post uses the website at https://www.base64encode.org/

The username and password are shown below separated by a colon character. The encoded result is shown at the bottom.

Base64 Encode Username Password

The header looks like this. Note: the encoded username and password below is derived from valid credentials.

BIP_POSTMAN_AUTHORIZATION

4. Get Report Definition

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

Note: That the parameter name for this report is the prompt label, Analysis, prefixed with the text saw.param.

BIP_POSTMAN_reptDef

Create a REST Request to Run the BIP Report

This creates the request to extract the data.

1. Add 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: 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.

Change the command in the upper left to Post.

The two headers are shown below:

BIP_POSTMAN_RUN1

2. Create the Body

The Body tab is opened as shown in the figure above.

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

BIP_POSTMAN_RUNBODY

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 and the value “Audio” is passed to the prompt / parameter to filter the results.

3. Send the Request and Review Results

The results are shown below:

BIP_POSTMAN_RESULTS

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

The XML output is shown above the closing boundary line.

Usage of the REST Request

The REST API request to run a BIP report may now be used anywhere a REST API request can be issued.

An example of the REST API request used in a Curl statement is shown below. Curl is a command line tool for getting or sending files using REST syntax.

BIP_POSTMAN_CURL

Summary

This post details a simple method of extracting data from an OBIEE environment using BI Publisher 12c and the BI Publisher Rest API.

For more 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.

References

API Testing POSTMAN Download

API Testing using POSTMAN

REST API for Oracle BI Publisher

Get Started with Analyses and Dashboards

Report Designer’s Guide for Oracle Business Intelligence Publisher

Add Your Comment