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.
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.
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.
The collection is created by clicking the icon shown below:
Note: Enter a name and save the collection.
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
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:
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.
The header looks like this. Note: the encoded username and password below is derived from valid credentials.
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.
This creates the request to extract the data.
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:
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.
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.
The results are shown below:
The result section is separated by system-generated boundary lines.
The XML output is shown above the closing boundary line.
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.
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.