Pipelined Table Functions in Oracle Business Intelligence Cloud Service (BICS)

Introduction

 

This article outlines how to use a pipelined table function in Oracle Business Intelligence Cloud Service (BICS).


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


Two possible use cases for pipelined table functions in BICS 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 Social Data in Oracle Social Data and Insight Cloud Service as the data source. Data is retrieved using the Social Data and Insight REST APIs. That said any data source accessible by either SOAP or REST web services may be referenced in the pipelined table function.


Since the current version of BICS does not support opaque views; it is not possible to reference variables in the model view. This means any parameters that need to be passed to the pipelined table function must be table driven. Therefore, in order to pass the selected value of a prompt to the pipelined table function, it must be first saved to a physical table. Unfortunately, this adds extra complexity and overhead to the solution when using prompts.


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


1)    Create Pipelined Table Function

2)    Display Pipelined Data in BICS

3)    Pass Dashboard Prompt

4)    Consume Dashboard


Due to the sensitive nature of the Social Data and Insight Cloud Service data limited screens shots of output are available.

In the code snippet examples, DUNS number is used in the Dashboard Prompt to retrieve the Social Data. The Data Universal Numbering System or DUNS Number is assigned and maintained by Dun & Bradstreet (D&B’s). A DUNS number is a unique nine-character number used to identify each physical location of a business. Additionally, the US federal government uses this number to track how federal money is allocated.


An appendix has been added to this article, that provides a code snippet on how to use a pipeline table function on an OTBI report.


Main Article

 

Step 1 – Create Pipelined Table Function

 

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


Five artifacts are created:


Object Type: TYPE DUNS_OT

Table Type: DUNS_TYPE

Table: SELECTED_DUNS

Function: FUNC_DUNS_PIPELINE

View: DUNS_VIEW


Steps:


From Oracle Application Express -> SQL Workshop -> SQL Commands


For a text file containing ALL the code snippets click here.

 

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

CREATE TYPE DUNS_OT AS OBJECT
(
FIRST_NAME VARCHAR(500),
LAST_NAME VARCHAR(500),
TITLE VARCHAR(500)
);

b)    Create Table Type DUNS_TYPE based on DUNS_OT.

CREATE TYPE DUNS_TYPE AS TABLE OF DUNS_OT;

c)    Create table to store values selected from Dashboard Prompts. In this case the Prompt is a DUNS Number.

CREATE TABLE SELECTED_DUNS(
COMPANY_DUNS_NUMBER VARCHAR2(9)
);

d)    Insert a sample DUNS Number into the table (to test with).

INSERT INTO SELECTED_DUNS(COMPANY_DUNS_NUMBER)
VALUES(‘123456789’);

e)    Create Pipelined Table Function.

Code Snippet Breakdown:

Gold: Retrieves Social Data via Rest API. For more info see: Integrating Oracle Social Data and Insight Cloud Service with Oracle Business Intelligence Cloud Service (BICS).

Green: Pipelines the results from the Rest API and parses them into DUNS_OT [created in step a]. It may help to compare the syntax used in this snippet to that used in Integrating Oracle Social Data and Insight Cloud Service with Oracle Business Intelligence Cloud Service (BICS) which inserts into a physical table vs. the virtual select shown here.

Aqua: Defines RETURN type DUNS_TYPE [created in step b].

Purple: Selects DUNS Number from table driven prompt [created in step c and populated in step d].

create or replace FUNCTION FUNC_DUNS_PIPELINE
RETURN DUNS_TYPE PIPELINED AS
l_ws_response_clob CLOB;
l_num_contacts NUMBER;
l_selected_duns VARCHAR2(9);
l_pad_duns VARCHAR2(9);
l_body CLOB;

SELECT MAX(COMPANY_DUNS_NUMBER) INTO l_selected_duns FROM SELECTED_DUNS;
l_pad_duns := LPAD(l_selected_duns,9,’0′);
l_body := ‘{“objectType”:”People”,”limit”:”10″,”filterFields”:[{“name”:”company.gl_ult_dun”,”value”:”‘ || l_pad_duns || ‘”},{“name”:”person.management_level”,”value”:”0″},{“name”:”person.department”,”value”:”3″}],”returnFields”:[“person.first_name”,”person.last_name”,”person.title”]}’;
apex_web_service.g_request_headers(1).name := ‘Content-Type’;
apex_web_service.g_request_headers(1).value := ‘application/json’;
apex_web_service.g_request_headers(2).name := ‘X-ID-TENANT-NAME’;
apex_web_service.g_request_headers(2).value := ‘TenantName’;
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => ‘https://SocialDataURL/data/api/v2/search’,
p_username => ‘User’,
p_password => ‘Pwd’,
p_body => l_body,
p_http_method => ‘POST’
);
–parse the clob as JSON
apex_json.parse(l_ws_response_clob);
–get total hits
l_num_contacts := CAST(apex_json.get_varchar2(p_path => ‘totalHits’) AS NUMBER);
–loop through total hits and insert JSON data into database
IF l_num_contacts > 0 THEN
for i in 1..l_num_contacts LOOP
PIPE ROW ( DUNS_OT (apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[1].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[2].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[3].value’)
));
end loop;
RETURN;
END IF;
–;

f)    Test the function from SQL Workshop.

CREATE VIEW DUNS_VIEW AS
SELECT * FROM TABLE(FUNC_DUNS_PIPELINE);

SELECT * FROM DUNS_VIEW;

 

Step 2 – Display Pipelined Data in BICS


Step 2 outlines how to display the data retrieved from the pipelined table function in BICS.


This is a two part process:


1)   Reference the pipelined table function in a data modeler view.

2)   Build an Analysis based on the view.


Steps:


a)    Lock to Edit the Model.

b)    Click on the cog / wheel next to “Database” then “Create View” to create a new View.

Untitled

c)    Click on SQL Query

Use the example below as a starting point for the syntax needed to call the pipelined table function from the view.

Confirm that your data is returned in the data tab.

SELECT
FIRST_NAME,
LAST_NAME,
TITLE
FROM
(
SELECT
*
FROM TABLE(FUNC_DUNS_PIPELINE)
)

Snap4

d)    Create an Analysis based on the custom view. Confirm that the Analysis returns results.

Snap5

e)    Place the Analysis on a Dashboard. Confirm results are viable.


Step 3 – Pass Dashboard Prompt


Step 3 outlines how to use a dashboard prompt on a dashboard to pass a user selected value from BICS to the pipelined table function in the database.

Currently BICS does not support opaque views; therefore, it is not possible to pass variables to the pipelined table function. At this stage the best approach is to use a physical table to store the prompt selection, and drive the pipelined table function from that table.

Remember the pipelined table function has no parameters. It is driven from the values contained in the SELECTED_DUNS table.

i.e. SELECT MAX(COMPANY_DUNS_NUMBER) INTO l_selected_duns FROM SELECTED_DUNS

The diagram below shows the steps required to save the selected dashboard prompt to the SELECTED_DUNS table. (Detailed steps follow.)

BICS_Prompts

Steps:


a)    Create Procedure PROCEDURE SP_LOAD_SELECTED_DUNS

create or replace PROCEDURE SP_LOAD_SELECTED_DUNS(p_selected_duns VARCHAR2)
IS
BEGIN
DELETE FROM SELECTED_DUNS;
INSERT INTO SELECTED_DUNS(COMPANY_DUNS_NUMBER)
VALUES(p_selected_duns);
END;

b)    Create Function FUNCTION FUNC_EXEC_SELECTED_DUNS

create or replace FUNCTION FUNC_EXEC_SELECTED_DUNS
(p_selected_duns VARCHAR2) RETURN INTEGER
IS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SP_LOAD_SELECTED_DUNS(p_selected_duns);
COMMIT;
RETURN 1;
END;

c)    Test Function

SELECT FUNC_EXEC_SELECTED_DUNS(‘123456789’) from DUAL;

SELECT * FROM SELECTED_DUNS;

d)    Create a dummy table that will be used in the trigger analysis. Insert any descriptive text into the table.

This should be a one row only table – as the trigger will run for every record in the table.

CREATE TABLE DUMMY_REFRESH(REFRESH_TEXT VARCHAR2(100));

INSERT INTO DUMMY_REFRESH(REFRESH_TEXT)
VALUES (‘Hit Return once complete’);

e)    In the Modeler create session variable r_selected_duns.

Snap7

f)     Add the DUMMY_REFRESH table in the Modeler as a Dimension table and join it to a fact table.

Add a Column Expression called UPDATE_DUNS.

This Expression calls the FUNC_EXEC_SELECTED_DUNS and passes the r_selected_duns request variable to it.

Snap8

EVALUATE(‘FUNC_EXEC_SELECTED_DUNS(%1)’,VALUEOF(NQ_SESSION.”r_selected_duns”))

Snap9

g)   Create an Analysis based on the DUMMY_REFRESH table that containing REFRESH_TEXT field & UPDATE_DUNS.

Snap10

h)    Add an Action Link to trigger the Analysis from the Dashboard (through Navigate to BI Content).

Set Action Options. Run Confirmation may be useful.

Snap11

Snap12 Snap13

i)     Add a Prompt to the Dashboard for DUNS Number.

Either manually add choice list DUNS Numbers or drive your Prompt from a table.

Set a Request Variable on the Prompt. This will be used to pass the selected value to the pipelined table function.

Due to BICS not supporting VALUELISTOF “Enable user to select multiple values” has been unchecked.

A workaround for passing multiple values to session variables has been previously discussed in Integrating Oracle Social Data and Insight Cloud Service with Oracle Business Intelligence Cloud Service (BICS).

Snap6

Step 4 – Consume Dashboard


Step 4 outlines to use an action link to trigger a data refresh to reflect the selection made on the dashboard prompt.


Steps: 


The final dashboard will contain thee components: A Dashboard Prompt, The Action Link, Analysis Request to display results.

Snap14
Snap15

In order to prevent accidental data refreshes, the Dashboard Consumer must confirm the action.

To refresh results; it may be necessary to clear BI Server Cache (through the modeler) or hit Refresh on the Dashboard (to clear Presentation cache).

Snap16

 

 

Appendix

OTBI Pipelind Table Function Code Snippet

For a text version of the code snippet click here.

CREATE TYPE OTBI_OT AS OBJECT
(
CUSTOMER_NAME VARCHAR(500),
BALANCE_AMT NUMBER
);

CREATE TYPE OTBI_TYPE AS TABLE OF OTBI_OT;

CREATE OR REPLACE FUNCTION FUNC_OTBI_PIPELINE RETURN 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>’||’casey.brown‘||'</v7:name>
<v7:password>’||’Pwd‘||'</v7:password>
</v7:logon>
</soapenv:Body>
</soapenv:Envelope>’;
l_xml := apex_web_service.make_request(
p_url => ‘https://abc1-fap1234-crm.oracledemos.com/analytics-ws/saw.dll?
SoapImpl=nQSessionService’
,p_envelope => l_envelope
,p_proxy_override => ‘www-proxy.us.oracle.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/GSE – Financials/US Demo OTBI Adhoc Query Reports/DEMO FINS
ADHOC OTBI Reports/BICSCustomerBalanceAnalysis‘ ||'</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://abc1-fap1234-crm.oracledemos.com/analytics-ws/saw.dll?
SoapImpl=xmlViewService’
,p_envelope => l_envelope
,p_proxy_override => ‘www-proxy.us.oracle.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
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’)
) LOOP
PIPE ROW (OTBI_OT (r_curr.Column0, r_curr.Column1));
END LOOP;
RETURN;
END;

SELECT * FROM TABLE(FUNC_OTBI_PIPELINE);

CREATE VIEW OTBI_VIEW AS
SELECT * FROM TABLE(FUNC_OTBI_PIPELINE);

SELECT * from OTBI_VIEW;

 

Want to Learn More?

 

Click here for more A-Team OACS Blogs

Click here for more A-Team BICS Blogs

Click here for more A-Team OBIEE Blogs

Click here for the Application Express API Reference Guide – MAKE_REST_REQUEST Function.

Click here for the SRM Developer Platform API Guide.

 

Summary


This article described how to create a pipelined table function in BICS. Additionally, it described how to display the results and pass a dashboard prompt to the pipelined function.

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

The data-source example provided was for Social Data and Insight Cloud Service data. However, this article can easily be modified to apply to any other data-source accessible via REST or SOAP Web Service API’s.

Add Your Comment