X

Best Practices from Oracle Development's A‑Team

Oracle Analytics Cloud (OAC) - Using Pipelined Table Functions in Answers (Analysis) Dashboards - SOAP/REST API's

Jay Pearson
Consulting Solutions Architect, Business Intelligence

 Background

* This blog was last tested on OAC 105.8.0-133 (Version 5.8) + ADW 18c + Fusion OTBI 11.1.1.9 *

~

This blog explains how to reference SOAP/REST API's using a Pipelined Table Function.

See this blog for instructions on how to reference relational data sources using a pipelined table function.

~

This article outlines how to use a pipelined table function in Oracle Analytics Cloud (OAC).

Using a pipelined table function makes it is possible to display data in OAC without having to load the data to a physical table.

Two possible use cases for pipelined table functions in OAC are:

1)    Drill-to-deal scenarios where data is only required momentarily and temporarily.

2)    Situations where corporate security may restrict sensitive data being physically saved to the cloud.

Pipelined table functions are best suited to small data-sets. Latency issues may occur on large data volumes.

The code snippets provided use Oracle Transactional Business Intelligence (OTBI) as the data source. Data is retrieved using the OTBI Soap API. That said, relational and other SOAP/REST web services may be referenced in the pipelined table function.

The article covers the two steps required to create, configure, and execute pipeline table functions from OAC:

1)    Create Pipelined Table Function

2)    Display Pipelined Data in OAC

Additionally an appendix has been left in the blog that has a diagram explaining how this was previously achieved in BICS.

 Create Pipelined Table Function

Step 1 outlines how to create the SQL artifacts needed for the pipelined table function to run in Oracle SQLDeveoper.

The OTBI report that will be called through the API is "Customer Pipeline".

Four artifacts are created:

Object Type: OTBI_OT

Table Type: OTBI_TYPE

Function: FUNC_OTBI_PIPELINE

View: OTBI_VIEW

Steps:

For a text file containing ALL the code snippet click here

From SQLDeveloper:

a)    Create an Object Type - specifying all columns to return with relevant data types.

CREATE TYPE OAC_USER.OTBI_OT AS OBJECT
(
SALES_STAGE_NAME VARCHAR(500),
OPEN_OPPORTUNITY_REVENUE NUMBER,
NUM_OPEN_OPPORTUNITIES NUMBER
);

b)    Create Table Type OTBI_TYPE based on OTBI_OT.

CREATE TYPE OAC_USER.OTBI_TYPE AS TABLE OF OAC_USER.OTBI_OT;

c)    Create Pipelined Table Function.

Replace the items marked in red with Fusion settings.

CREATE OR REPLACE FUNCTION  OAC_USER.FUNC_OTBI_PIPELINE RETURN OAC_USER.OTBI_TYPE PIPELINED AS
  l_envelope CLOB;
  l_xml XMLTYPE;
  v_session_id varchar2(128) ;
  v_xml xmltype ;
  v_cdata clob ;
BEGIN
  l_envelope := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v7="urn://oracle.bi.webservices/v7">   
<soapenv:Header/>   
<soapenv:Body>      
<v7:logon>         
<v7:name>'||'user'||'</v7:name>         
<v7:password>'||'password'||'</v7:password>      
</v7:logon>   
</soapenv:Body>
</soapenv:Envelope>';
  l_xml      := apex_web_service.make_request(
    p_url => 'https://fusion.fa.em2.oraclecloud.com/analytics-ws/saw.dll?SoapImpl=nQSessionService'
    ,p_envelope => l_envelope
  --  ,p_proxy_override => 'www-proxy.us.myproxy.com'
    );
     select sess.session_id 
          into v_session_id
     from
     xmltable
     (
          xmlnamespaces
          (
               'urn://oracle.bi.webservices/v7' as "sawsoap"
               ---'http://www.ms.com/xml'
               ---'http://schemas.xmlsoap.org/ws/2004/08/addressing' as "wsa",
               ---'http://www.w3.org/2003/05/soap-envelope' as "soap"
          ),
          '//sawsoap:logonResult'
          passing l_xml
          columns
          session_id               varchar2(50)     path     '//sawsoap:sessionID'
     ) sess;
     dbms_output.put_line('Session ID = ' || v_session_id);
       l_envelope := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v7="urn://oracle.bi.webservices/v7">
   <soapenv:Header/>
   <soapenv:Body>
      <v7:executeXMLQuery>
         <v7:report>
            <v7:reportPath>'|| '/shared/Sales/Analytic Library/Customers/Customer Pipeline' ||'</v7:reportPath>                      
            <v7:reportXml></v7:reportXml>
         </v7:report>
         <v7:outputFormat></v7:outputFormat>
         <v7:executionOptions>
            <v7:async></v7:async>
            <v7:maxRowsPerPage></v7:maxRowsPerPage>
            <v7:refresh></v7:refresh>
            <v7:presentationInfo></v7:presentationInfo>
            <v7:type></v7:type>
         </v7:executionOptions>
         <v7:reportParams>
            <!--Zero or more repetitions:-->
            <v7:filterExpressions></v7:filterExpressions>
            <!--Zero or more repetitions:-->
            <v7:variables>
               <v7:name></v7:name>
               <v7:value></v7:value>
            </v7:variables>
            <!--Zero or more repetitions:-->
            <v7:nameValues>
               <v7:name></v7:name>
               <v7:value></v7:value>
            </v7:nameValues>
            <!--Zero or more repetitions:-->
            <v7:templateInfos>
               <v7:templateForEach></v7:templateForEach>
               <v7:templateIterator></v7:templateIterator>
               <!--Zero or more repetitions:-->
               <v7:instance>
                  <v7:instanceName></v7:instanceName>
                  <!--Zero or more repetitions:-->
                  <v7:nameValues>
                     <v7:name></v7:name>
                     <v7:value></v7:value>
                  </v7:nameValues>
               </v7:instance>
            </v7:templateInfos>
            <!--Optional:-->
            <v7:viewName></v7:viewName>
         </v7:reportParams>
         <v7:sessionID>'
         || v_session_id ||
         '</v7:sessionID>
      </v7:executeXMLQuery>
   </soapenv:Body>
</soapenv:Envelope>';
  l_xml      := apex_web_service.make_request(
    p_url => 'https://fusion.fa.em2.oraclecloud.com/analytics-ws/saw.dll?SoapImpl=xmlViewService'
    ,p_envelope => l_envelope
 --   ,p_proxy_override => 'www-proxy.us.myproxy.com'
    );
             select (cdata_section)
             into v_cdata
     from 
     xmltable
     (
           xmlnamespaces
          (
                   'urn://oracle.bi.webservices/v7' as "sawsoap"
         ),
          '//sawsoap:executeXMLQueryResult/sawsoap:return'
          passing l_xml
          columns
          cdata_section               clob     path     '//sawsoap:rowset/text()'
     ) dat
    where rownum = 1;
    for r_curr IN (SELECT  Column0,Column1,Column2
         FROM XMLTABLE(
                XMLNamespaces(default 'urn:schemas-microsoft-com:xml-analysis:rowset'),
                '$aaa/rowset/Row' PASSING XMLTYPE(v_cdata) AS "aaa"
                   COLUMNS Column0 VARCHAR(500) PATH 'Column0',Column1 NUMBER PATH 'Column1',Column2 NUMBER PATH 'Column2')
    ) LOOP
    PIPE ROW (OTBI_OT (r_curr.Column0, r_curr.Column1, r_curr.Column2));
    END LOOP;  
    RETURN;
END;

d)    Test the function in SQLDeveloper.

SELECT * FROM TABLE(OAC_USER.FUNC_OTBI_PIPELINE);

CREATE OR REPLACE VIEW OAC_USER.OTBI_VIEW AS
SELECT * FROM TABLE(OAC_USER.FUNC_OTBI_PIPELINE);

SELECT
SALES_STAGE_NAME,
OPEN_OPPORTUNITY_REVENUE,
NUM_OPEN_OPPORTUNITIES
FROM OAC_USER.OTBI_VIEW;

 Display Pipelined Data in OAC

Data can now be pulled into OAC via the view.

If a filter is needed to pass directly to the pipelined table function instructions are covered in this blog.

Start off by building the below SQL in the RPD -> Physicals Table -> Table Type = Select -> Default Initialization String. Then pass the parameters via a request variable through the TABLE function.

SELECT
SALES_STAGE_NAME,
OPEN_OPPORTUNITY_REVENUE,
NUM_OPEN_OPPORTUNITIES
FROM
(SELECT *
FROM
TABLE(OAC_USER.FUNC_OTBI_PIPELINE)
);

 Appendix (BICS)

This appendix has been left in the blog to outline how this was concept achieved in BICS.

BICS_Prompts

 Want to Learn More?

Click here for more A-Team OAC Blogs

Click here for more A-Team BICS Blogs

Click here for more A-Team OBIEE Blogs

 Summary

This blog explains how to reference SOAP/REST API's using a pipelined table function.

Pipelined table functions may be useful in situations where it is not feasible or desirable to physically save data to the OAC database.

The data-source example provided was for Oracle Transactional Business Intelligence (OTBI). However, this article can easily be modified to apply to other data sources.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha