Loading Data into Oracle BI Cloud Service using BI Publisher Reports and REST Web Services

Introduction

This post details a method of loading data that has been extracted from Oracle Business Intelligence Publisher (BIP) into the Oracle Business Intelligence Cloud Service (BICS). The BIP instance may either be Cloud-Based or On-Premise.

It builds upon the A-Team post Extracting Data from Oracle Business Intelligence 12c Using the BI Publisher REST API. This post uses REST web services to extract data from an XML-formatted BIP report.

The method uses the PL/SQL language to wrap the REST extract, XML parsing commands, and database table operations. It produces a BICS staging table which can then be transformed into star-schema object(s) for use in modeling.  The transformation processes and modeling are not discussed in this post.

Additional detailed information, including the complete text of the procedure described, is included in the References section at the end of the post.

Rationale for using PL/SQL

PL/SQL is the only procedural tool that runs on the BICS / Database Schema Service platform. Other wrapping methods e.g. Java, ETL tools, etc. require a platform outside of BICS to run on.

PL/SQL can utilize native SQL commands to operate on the BICS tables. Other methods require the use of the BICS REST API.

Note: PL/SQL is a very good at showcasing functionality. However, it tends to become prohibitively resource intensive when deploying in an enterprise production environment.

For the best enterprise deployment, an ETL tool such as Oracle Data Integrator (ODI) should be used to meet these requirements and more:

* Security

* Logging and Error Handling

* Parallel Processing – Performance

* Scheduling

* Code Re-usability and Maintenance

The steps below depict how to load a BICS table.

About the BIP Report

The report used in this post is named BIP_DEMO_REPORT and is stored in a folder named Shared Folders/custom as shown below: BIP Report Location

The report is based on a simple analysis with three columns and output as shown below:

BIP Demo Analysis

Note: The method used here requires all column values in the BIP report to be NOT NULL for two reasons:

* The XPATH parsing command signals either the end of a row or the end of the data when a null result is returned.

* All columns being NOT NULL ensures that the result set is dense and not sparse. A dense result set ensures that each column is represented in each row.

Additional information regarding dense and sparse result sets may be found in the Oracle document Database PL/SQL Language Reference.

One way to ensure a column is not null is to use the IFNull function in the analysis column definition as shown below:

BIP IFNULL Column Def

Call the BIP Report

The REST API request used here is similar to the one detailed in Extracting Data from Oracle Business Intelligence 12c Using the BI Publisher REST API. The REST API request should be constructed and tested using a REST API testing tool e.g. Postman

This step uses the APEX_WEB_SERVICE package to issue the REST API request and return the result in a CLOB variable. The key inputs to the package call are:

* The URL for the report request service

* Two request readers to be sent for authorization and content.

* The REST body the report request service expects.

* An optional proxy override

An example URL is below:

http://hostname/xmlpserver/services/rest/v1/reports/custom%2FBIP_DEMO_REPORT/run

Note: Any ASCII special characters used in a value within a URL, as opposed to syntax, needs to be referenced using its ASCII code prefixed by a % sign. In the example above, the slash (/) character is legal in the syntax but not for the value of the report location. Thus the report location, “custom/BIP_DEMO_REPORT” must be shown as custom%2FBIP_DEMO_REPORT where 2F is the ASCII code for a slash character.

An example request Authorization header is below.

apex_web_service.g_request_headers(1).name := ‘Authorization’;          apex_web_service.g_request_headers(1).value :=  ‘Basic cHJvZG5leTpBZG1pbjEyMw==‘;

Note: The authorization header value is the string ‘Basic ‘ concatenated with a Base64 encoded representation of a username and password separated by a colon e.g.  username:password

Encoding of the Base64 result should first be tested with a Base64 encoding tool e.g. base64encode.org

An example of the Content-Type header is below:

apex_web_service.g_request_headers(2).name := Content-Type’;            apex_web_service.g_request_headers(2).value := ‘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.

An example of a report request body is below:

Boundary_1_1153447573_1465550731355                                                                 Content-Type: application/json                                                                              Content-Disposition: form-data; name=ReportRequest”                        {“byPassCache”:true,”flattenXML”:false}                                         —Boundary_1_1153447573_1465550731355

An example proxy override is below:

www-proxy.us.oracle.com

 An example REST API call:

f_report_clob  := apex_web_service.make_rest_request ( p_url => p_report_url, p_body => l_body,        p_http_method => ‘POST’,  p_proxy_override => l_proxy_override );

Parse the BIP REST Result

The BIP REST result is the report XML data embedded in text with form-data boundaries.

This step uses the :

* INSTR function to determine the beginning and end of the embedded XML

* SUBSTR function to extract just the embedded XML and store it in a CLOB variable

* XMLTYPE.createXML function to convert and return the XML.

The key inputs to this step are:

* The CLOB returned from BIP REST call above

* The XML root name returned from the BIP report, e.g. DATA_DS

An example of the REST result returned is below:

–Boundary_2_1430729833_1479236681852

Content-Type: application/json

Content-Disposition: form-data; name=”ReportResponse”

{“reportContentType”:”text/xml”}

–Boundary_2_1430729833_1479236681852

Content-Type: application/octet-stream

Content-Disposition: form-data; filename=”xmlp2414756005405263619tmp”; modification-date=”Tue, 15 Nov 2016 19:04:41 GMT”; size=1242; name=”ReportOutput”

<?xml version=”1.0″ encoding=”UTF-8″?>

<!–Generated by Oracle BI Publisher 12.2.1.1.0 -Dataengine, datamodel:_custom_BIP_DEMO_MODEL_xdm –>

<DATA_DS><SAW.PARAM.ANALYSIS></SAW.PARAM.ANALYSIS>

<G_1>

<COLUMN0>Accessories</COLUMN0><COLUMN1>5161697.87</COLUMN1><COLUMN2>483715</COLUMN2>

</G_1>

<G_1>

         <COLUMN0>Smart Phones</COLUMN0><COLUMN1>6773120.36</COLUMN1><COLUMN2>633211</COLUMN2>

</G_1>

</DATA_DS>

–Boundary_2_1430729833_1479236681852– >

Examples of the string functions to retrieve and convert just the XML are below. The f_report_clob variable contains the result of the REST call. The p_root_name variable contains the BIP report specific XML rootName.

To find the starting position of the XML, the INSTR function searches for the opening tag consisting of the root name prefixed with a ‘<’ character, e.g. <DATA_DS:

f_start_position := instr ( f_report_clob, ‘<‘ || p_root_name );

To find the length of the XML, the INSTR function searches for the position of the closing tag consisting of the root name prefixed with a ‘</’ characters, e.g. </DATA_DS, determines and adds the length of the closing tab using the LENGTH function, and subtracts the starting position:

f_xml_length := instr ( f_report_clob, ‘</’ || p_root_name ) + length( ‘</’ || p_root_name || ‘>’) f_start_position ;

To extract the XML and store it in a CLOB variable, the SUBSTR function uses the starting position and the length of the XML:

f_xml_clob := substr(f_report_clob, f_start_position, f_xml_length );

To convert the CLOB into an XMLTYPE variable:

f_xml := XMLTYPE.createXML( f_xml_clob );

Create a BICS Table

This step uses a SQL command to create a simple staging table that has 20 identical varchar2 columns. These columns may be transformed into number and date data types in a future transformation exercise that is not covered in this post.

A When Others exception block allows the procedure to proceed if an error occurs because the table already exists.

A shortened example of the create table statement is below:

execute immediate ‘create table staging_table ( c01 varchar2(2048), … , c20 varchar2(2048)  )’;

Load the BICS Table

This step uses SQL commands to truncate the staging table and insert rows from the BIP report XML content.

The XML content is parsed using an XPATH command inside two LOOP commands.

The first loop processes the rows by incrementing a subscript.  It exits when the first column of a new row returns a null value.  The second loop processes the columns within a row by incrementing a subscript. It exits when a column within the row returns a null value.

The following XPATH examples are for a data set that contains 11 rows and 3 columns per row:

//G_1[2]/*[1]/text()          — Returns the value of the first column of the second row

//G_1[2]/*[4]/text()          — Returns a null value for the 4th column signaling the end of the row

//G_1[12]/*[1]/text()        — Returns a null value for the first column of a new row signaling the end of the — data set

After each row is parsed, it is inserted into the BICS staging table.

An image of the staging table result is shown below:

BIP Table Output

Summary

This post detailed a method of loading data that has been extracted from Oracle Business Intelligence Publisher (BIP) into the Oracle Business Intelligence Cloud Service (BICS).

Data was extracted and parsed from an XML-formatted BIP report using REST web services wrapped in the Oracle PL/SQL APEX_WEB_SERVICE package.

A BICS staging table was created and populated. This table can then be transformed into star-schema objects for use in modeling.

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

Complete Text of Procedure Described

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

Database PL/SQL Language Reference

Reference Guide for the APEX_WEB_SERVICE

REST API Testing Tool

XPATH Testing Tool

Base64 decoding and encoding Testing Tool

 

 

Add Your Comment