Loading Data into Oracle BI Cloud Service using OTBI Analyses and SOAP

Introduction

This post details a method of loading data that has been extracted from Oracle Transactional Business Intelligence (OTBI) using SOAP into the Oracle Business Intelligence Cloud Service (BICS). The OTBI instance may either be Cloud-Based or On-Premise. This method may also be used to load data from Oracle Business Intelligence Enterprise Edition (OBIEE).

It builds upon the A-Team post Using Oracle BI Answers to Extract Data from HCM via Web Services which details the extraction process.

This post uses the PL/SQL language to wrap the SOAP extract, XML parsing commands, and database table operations in a stored procedure in the BICS Schema Service database. 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.

The most complex portion of this post details how to convert the analysis XML report results, embedded in a CDATA (Character Data) text attribute, back into standard XML markup notation so the rows and columns of data can be parsed.

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 OTBI Analysis

The analysis used in this post is named Suppliers and is stored in a folder named Shared Folders/custom as shown below:

A

The analysis has three columns and output as shown below:

B

Note: The method used here requires all column values in the analysis 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:

C

An optional parameter may be sent at run time to filter each column.

Ensuring the Web Services are Available

To ensure that the web services are available in the required environment, type a form of the following URL into a browser:

https://hostname/analytics-ws/saw.dll/wsdl/v9

Note: The version number e.g. v9 may vary from server to server.

If you are not able to reach the website, the services may not be offered.  Discuss this with the server administrator.

Calling the Analysis

Calling the analysis is a two-step process. The first step initiates a session in OTBI and returns a session ID.  The second step uses that session ID to call the analysis and extract the data.

The SOAP API requests should be constructed and tested using a SOAP API testing tool e.g. SoapUI.

Note: API testing tools such as SoapUI, cURL, 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.

The procedure uses the APEX_WEB_SERVICE package to issue the SOAP API requests and store the XML result in a XMLTYPE variable. The key inputs to the package call are:

* The URL for the OTBI Session Web Service

* The URL for the OTBI XML View Web Service

* The Base64 encoded credentials to access the analysis

* The SOAP envelopes expected by the OTBI Web Service.

* Optional Parameters to filter the results

* An optional proxy override

Decoding the Credentials

To avoid hard-coding credentials in the procedure, the credentials are expected to be encoded in a base64 format prior to invoking the procedure. A useful base64 encoding tool may be found at Base64 Decoding and Encoding Testing Tool. The text to encode should be in the format username:password

The APEX_WEB_SERVICE and the DBMS_LOB packages and the INSTR function are used to decode the credentials into username and password variables. The APEX_WEB_SERVICE package decodes the credentials into a BLOB variable. The DBMS_LOB package converts the BLOB to a CLOB variable. The INSTR function then separates the decoded result into the two variables.

Examples are below:

— Decode the Base 64 Credentials
f_blob := apex_web_service.clobbase642blob(f_base64_creds);
— Create a temporary CLOB instance
dbms_lob.createtemporary(f_clob, true);
— Convert the decoded BLOB credentials to a CLOB
dbms_lob.converttoclob(
f_clob,
f_blob,
v_file_size,
v_dest_offset,
v_src_offset,
v_blob_csid,
v_lang_context,
v_warning);
— Parse the credentials into username and password
f_au := substr ( f_clob, 1, instr(f_clob, ‘:’) -1 ); — username
f_ap := substr ( f_clob, instr(f_clob, ‘:’) +1 ); — password

Calling the Session Service

An example Session URL is below:

https://hostname/analytics-ws/saw.dll?SoapImpl=nQSessionService

An example Logon Request envelope is below. The result will be an envelope containing a session ID.

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:v9=”urn://oracle.bi.webservices/v9″>
<soapenv:Header/>
<soapenv:Body>
<v9:logon>
<v9:name>username</v9:name>
<v9:password>password</v9:password>
</v9:logon>
</soapenv:Body>
</soapenv:Envelope>

 An example APEX_WEB_SERVICE call for the login is below:

f_xml := apex_web_service.make_request(p_url => f_session_url
,p_envelope => f_envelope
— ,p_proxy_override => — An optional Proxy URL
— ,p_wallet_path => — An optional path to an Oracle database wallet file
— ,p_wallet_pwd => — The password for the optional Oracle database wallet file
);

The APEX_WEB_SERVICE package is used to parse the XML result from above to obtain the session ID. An example is below:

f_session_id := apex_web_service.parse_xml_clob(p_xml => f_xml
,p_xpath => ‘//*:sessionid/text()
);

Troubleshooting the Session Service Call

Three common issues are the need for a proxy, the need for a trusted certificate (if using HTTPS), and the need to use the TLS security protocol.

The need for a proxy may be detected when the following error occurs: ORA-12535: TNS:operation timed out. Adding the optional p_proxy_override  parameter to the call may correct the issue. An example proxy override is:

www-proxy.us.oracle.com

The need for a trusted certificate is detected when the following error occurs: ORA-29024: Certificate validation failure.

A workaround may be to run this procedure from a full Oracle Database Could Service or an on-premise Oracle database. Adding the trusted certificate(s) to an Oracle database wallet file and adding the optional p_wallet_path  and p_wallet_pwd  parameters to the call should correct the issue.  For more information on Oracle wallets, refer to Using Oracle Wallet Manager in the References section of this post.

The need to use the TLS protocol maybe detected when the following error occurs: ORA-29259: end-of-input reached.

A workaround is to run this procedure from a different Oracle Database Could Service or an on-premise Oracle database. Ensure the database version is 11.2.0.4.10 or above.

Additionally: When using an on-premise Oracle database, the SQL Operations described later in this post (Create Table, Truncate Table, Insert) may be modified to use the BICS REST API. For more information refer to the REST APIs for Oracle BI Cloud Service in the References section of this post.

Calling the XML View Service

An example XML View service URL is:

https://hostname/analytics-ws/saw.dll?SoapImpl=xmlViewService

An example Analysis Request envelope is below. This envelope contains the session ID from the logon call, the location of the analysis, a placeholder variable for the VNUM analysis variable, and a filter value for the VTYPE variable.

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:v9=”urn://oracle.bi.webservices/v9″>
<soapenv:Header/>
<soapenv:Body>
<v9:executeXMLQuery>
<v9:report>
<v9:reportPath>/shared/custom/Suppliers</v9:reportPath>
<v9:reportXml></v9:reportXml>
</v9:report>
<v9:outputFormat>xml</v9:outputFormat>
<v9:executionOptions>
<v9:async></v9:async>
<v9:maxRowsPerPage></v9:maxRowsPerPage>
<v9:refresh></v9:refresh>
<v9:presentationInfo></v9:presentationInfo>
<v9:type></v9:type>
</v9:executionOptions>
<v9:reportParams>
<!–Zero or more repetitions:–>
<v9:variables>
<v9:name>VNUM</v9:name>
<v9:value></v9:value>
</v9:variables>
<v9:variables>
<v9:name>VTYPE</v9:name>
<v9:value>Supplier</v9:value>
</v9:variables>
</v9:reportParams>
<v9:sessionID>’||F_SESSION_ID||'</v9:sessionID>
</v9:executeXMLQuery>
</soapenv:Body>
</soapenv:Envelope>

An example APEX_WEB_SERVICE call for the analysis result is below:

f_xml := apex_web_service.make_request(p_url => f_report_url
,p_envelope => f_envelope
— ,p_proxy_override => — An optional Proxy URL
— ,p_wallet_path => — An optional path to an Oracle database wallet file
— ,p_wallet_pwd => — The password for the optional Oracle database wallet file
);

Preparing the XML Result

The XML result from the Analysis call contains the report results in a CDATA text section. In order to parse the results, the XML within the text section is converted into standard XML using the XMLTYPE package and the REPLACE function.

An example of the CDATA section result, as seen in SoapUI, is below:

<sawsoap:rowset xsi:type=”xsd:string”><![CDATA[<rowset xmlns=”urn:schemas-microsoft-com:xml-analysis:rowset”>
<Row>
<Column0>UJ Catering Service AG</Column0>
<Column1>5991</Column1>
<Column2>Supplier</Column2>
</Row>
</rowset>]]>
</sawsoap:rowset>

The same result, as seen in APEX_WEB_SERVICE, is below:

<sawsoap:rowset xsi:type=”xsd:string”>&lt;rowset xmlns=&quot;urn:schemas-microsoft-com:xml-analysis:rowset&quot;&gt;
&lt;Row&gt;
&lt;Column0&gt;UJ Catering Service AG&lt;/Column0&gt;
&lt;Column1&gt;5991&lt;/Column1&gt;
&lt;Column2&gt;Supplier&lt;/Column2&gt;
&lt;/Row&gt;
&lt;/rowset&gt;
</sawsoap:rowset>

The converted result needed for parsing  is below:

<sawsoap:rowset xsi:type=”xsd:string”><bi:rowset xmlns:bi=”urn:schemas-microsoft-com:xml-analysis:rowset”>
<Row>
<Column0>UJ Catering Service AG</Column0>
<Column1>5991</Column1>
<Column2>Supplier</Column2>
</Row>
</bi:rowset>
</sawsoap:rowset>

The XMLTYPE package and the REPLACE function usage is below.  Note: the CHR(38) function returns the ‘&’ character.

F_CLOB := F_XML.GETCLOBVAL(); — Convert to CLOB
F_CLOB := REPLACE (F_CLOB, CHR(38)||’lt;’, ‘<‘);
F_CLOB := REPLACE (F_CLOB, CHR(38)||’gt;’, ‘>’ );
F_CLOB := REPLACE (F_CLOB, CHR(38)||’quot;’,'”‘);
F_CLOB := REPLACE (F_CLOB, ‘/rowset’, ‘/bi:rowset’); — Insert bi namespace
F_CLOB := REPLACE (F_CLOB, ‘<rowset’, ‘<bi:rowset’); — Insert bi namespace
F_CLOB := REPLACE (F_CLOB, ‘xmlns=’, ‘xmlns:bi=’); — Insert bi namespace
F_XML := XMLTYPE.createXML( F_CLOB ); — Convert back to XMLTYPE

Creating 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. An example is below:

EXCEPTION
WHEN OTHERS THEN NULL; — Ignore error if table exists

Note: The table needs to be created once before compiling the procedure the first time. The complete DDL is below:

CREATE TABLE STAGING_TABLE
(
C01 VARCHAR2(2048 BYTE),C02 VARCHAR2(2048 BYTE), C03 VARCHAR2(2048 BYTE), C04 VARCHAR2(2048 BYTE), C05 VARCHAR2(2048 BYTE),
C06 VARCHAR2(2048 BYTE),C07 VARCHAR2(2048 BYTE), C08 VARCHAR2(2048 BYTE), C09 VARCHAR2(2048 BYTE), C10 VARCHAR2(2048 BYTE),
C11 VARCHAR2(2048 BYTE),C12 VARCHAR2(2048 BYTE), C13 VARCHAR2(2048 BYTE), C14 VARCHAR2(2048 BYTE), C15 VARCHAR2(2048 BYTE),
C16 VARCHAR2(2048 BYTE),C17 VARCHAR2(2048 BYTE), C18 VARCHAR2(2048 BYTE), C19 VARCHAR2(2048 BYTE), C20 VARCHAR2(2048 BYTE)
)

A shortened example of the create table statement is below:

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

Loading 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 5 rows and 3 columns per row:

//Row[2]/*[1]/text() — Returns the value of the first column of the second row
//Row[2]/*[4]/text() — Returns a null value for the 4th column signaling the end of the row
//Row[6]/*[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:

D

Summary

This post detailed a method of loading data that has been extracted from Oracle Transactional Business Intelligence (OTBI) using SOAP into the Oracle Business Intelligence Cloud Service.

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

Using Oracle BI Answers to Extract Data from HCM via Web Services

Database PL/SQL Language Reference

Reference Guide for the APEX_WEB_SERVICE

Soap API Testing Tool

XPATH Testing Tool

Base64 Decoding and Encoding Testing Tool

Using Oracle Wallet Manager

REST APIs for Oracle BI Cloud Service

 

 

 

Add Your Comment