Oracle Analytics Cloud (OAC) - Using Pipelined Table Functions in Answers (Analysis) Dashboards - SOAP/REST API's
December 19, 2015 | 5 minute read
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.
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.