Integrating Oracle Transaction Business Intelligence (OTBI) Soap API
May 1, 2015 | 7 minute read
Background
This blog was written in 2015 for Oracle BI Cloud Service (BICS) + Oracle Application Express (Oracle Apex). Since Oracle Analytics Cloud (OAC) now supports direct connectivity to Oracle Fusion Sales Cloud, the BICS component of this blog has been removed and has not been converted to OAC. This is not a recommended method to integrate Fusion and Oracle Analytics Cloud (OAC). This blog has been left active to illustrate how PL/SQL may be used to integrate Oracle Transaction Business Intelligence (OTBI) Soap API.
* This blog was last tested on ADW 18c + Fusion 20D (11.13.20.10.0) *
This blog contains a sample stored procedure that extracts data from OTBI and loads it into ADW.
Three artifacts are created in the process:
1) OTBI Answers / Analysis Request
Sales Cloud data is retrieved via an Oracle Transaction Business Intelligence (OTBI) Answers / Analysis request created within Sales Cloud Analytics.
2) Database Table
The table is created within ADW to load / store the Sales Cloud data.
3) Stored Procedure
A dynamic stored procedure is created within ADW. The stored procedure calls an external SOAP web service that runs and returns the results from the OTBI Answers / Analysis request. These results are then inserted into the ADW table created in step 2.
The steps below detail how to create these three artifacts and call the data load integration process.
Creating the OTBI Answers / Analysis Request
1.1 - Create or use an existing OTBI Answers / Analysis
1.2 - Depending on the design of the OTBI report will may need to disable cache - to force it to refresh each time.
From the Advanced tab
a) Check "Bypass Oracle BI Presentation Services Cache"
b) Choose Partial Update = Entire Report
c) Apply XML
d) In Prefix: SET VARIABLE DISABLE_CACHE_HIT=1;
e) Click Apply SQL
f) Click Save
g) Close report and re-open
h) Go to Advance tab to confirm cache changes have stuck
1.3 - Confirm the request returns the desired results
Create Database Table
2.1 Create table in SQLDeveloper or SQL query tool of choice.
CREATE TABLE OAC_USER.CUSTOMER_PIPELINE
(
SALES_STAGE_NAME VARCHAR(250),
OPEN_OPPORTUNITY_REVENUE NUMBER,
NUM_OPEN_OPPORTUNITIES NUMBER,
LOAD_DTTM DATE,
LOADED_BY_USER VARCHAR2(32 BYTE)
)
*** IMPORTANT ***
The table below has 2 audit columns to track data load date and loaded by user. These columns must be appended to the table if using the sample stored procedure provided.
"LOAD_DTTM" DATE,
"LOADED_BY_USER" VARCHAR2(32 BYTE)
If the required audit columns are excluded - "ORA-00947: not enough values" error will be encountered.
Creating the Stored Procedure
Create and run the stored procedure that calls the web service to pull the Sales Cloud data and load the Pipeline data into the CUSTOMER_PIPELINE table.
The stored procedure leverages the APEX_WEB_SERVICE API. In this example we invoke a SOAP style web service. However, the APEX_WEB_SERVICE API can also be used to invoke RESTful style Web services. The web service returns the results in XML, these results are then parsed, and then loaded into the database table into separate columns.
For a text version of the SQL click here.
create or replace procedure oac_user.sp_load_otbi
(
p_session_url varchar2
,p_data_url varchar2
,p_report_folder varchar2
,p_au varchar2
,p_ap varchar2
,p_target_table varchar2
,p_col_def varchar2
,p_proxy varchar2 default 'www-proxy.us.oracle.com'
) is
l_envelope CLOB;
l_xml XMLTYPE;
v_session_id varchar2(128) ;
v_xml xmltype ;
v_clob clob ;
v_cdata clob ;
p_pos number ;
p_prev_pos number ;
f_more_available boolean ;
f_index number ;
f_max_index number ;
v_col_spec varchar2(128) ;
v_col_name varchar(64);
v_col_type varchar2(64) ;
v_comma varchar2(2) := '' ;
v_sel_list varchar2(4000) ;
v_cdef_list varchar2(4000) ;
v_curr_db_user varchar2(32) ;
BEGIN
select user into v_curr_db_user from dual ;
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>'||p_au||'</v7:name>
<v7:password>'||p_ap||'</v7:password>
</v7:logon>
</soapenv:Body>
</soapenv:Envelope>';
l_xml := apex_web_service.make_request(
p_url => p_session_url
,p_envelope => l_envelope
,p_proxy_override => p_proxy
);
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>'||p_report_folder||'</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 => p_data_url
,p_envelope => l_envelope
,p_proxy_override => p_proxy
);
select (cdata_section)
into v_cdata
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:executeXMLQueryResult/sawsoap:return'
passing l_xml
columns
cdata_section clob path '//sawsoap:rowset/text()'
) dat
where rownum = 1;
f_more_available := true ;
p_prev_pos := 0 ;
f_index := 0 ;
f_max_index := 3 ;
while f_more_available
loop
f_index := f_index + 1 ;
p_prev_pos := p_prev_pos + 1 ;
p_pos := instr(p_col_def, '|', p_prev_pos, 1) ;
if ( p_pos = 0 )
then
-- this is the only column or is the last
-- dbms_output.put_line('pos :'||p_pos) ;
-- dbms_output.put_line('prev_pos :'||p_prev_pos) ;
v_col_spec := substr(p_col_def, p_prev_pos) ;
-- dbms_output.put_line('pos :'||p_pos) ;
f_more_available := false ;
else
v_col_spec := substr(p_col_def, p_prev_pos, p_pos - p_prev_pos) ;
p_prev_pos := p_pos;
end if ;
p_pos := instr(v_col_spec, ':') ;
v_col_name := substr(v_col_spec, 1, p_pos - 1) ;
v_col_type := substr(v_col_spec, p_pos +1) ;
dbms_output.put_line('Col spec :'||v_col_spec) ;
dbms_output.put_line('Col name :'||v_col_name) ;
dbms_output.put_line('Col spec :'||v_col_type) ;
v_sel_list := v_sel_list || v_comma || v_col_name ;
v_cdef_list := v_cdef_list || v_comma || v_col_name ||' '||v_col_type ||' PATH '''||v_col_name||'''';
v_comma := ',';
end loop;
dbms_output.put_line('sel list: '||v_sel_list) ;
dbms_output.put_line('cdef list: '||v_cdef_list) ;
execute immediate 'delete ' || p_target_table ;
execute immediate 'insert into '||p_target_table||' '||
'SELECT '||v_sel_list ||', sysdate, '''||v_curr_db_user||'''
FROM XMLTABLE(
XMLNamespaces(default ''urn:schemas-microsoft-com:xml-analysis:rowset''),
''$aaa/rowset/Row'' PASSING XMLTYPE(:cdata) AS "aaa"
COLUMNS '||v_cdef_list||'
)' using v_cdata;
commit ;
END;
/
Execute Stored Procedure
4.1 - Replace the below parameters:
a) Sales Cloud Analytics Server Name
b) Path and name of (OTBI) Answers / Analysis Request
c) User with permissions to run OTBI Answers / Analysis Request
d) Password of user running the request
e) Column number and column data type of each field returned by the OTBI report
f) Name of ADW table loading data into
For a text version of the SQL click here
declare
v_row_count number ;
begin
select count(*) into v_row_count from OAC_USER.CUSTOMER_PIPELINE;
dbms_output.put_line('Original count: '||v_row_count) ;
delete OAC_USER.CUSTOMER_PIPELINE;
commit ;
select count(*) into v_row_count from OAC_USER.CUSTOMER_PIPELINE;
dbms_output.put_line('Deleted count: '||v_row_count) ;
oac_user.sp_load_otbi
(
'https://fusion.oraclecloud.com/analytics-ws/saw.dll?SoapImpl=nQSessionService'
,'https://fusion.oraclecloud.com/analytics-ws/saw.dll?SoapImpl=xmlViewService'
,'/shared/Sales/Analytic Library/Customers/Customer Pipeline'
,'user'
,'pwd'
,'OAC_USER.CUSTOMER_PIPELINE'
,'Column0:VARCHAR2(250)|Column1:NUMBER|Column2:NUMBER'
,''
);
select count(*) into v_row_count from OAC_USER.CUSTOMER_PIPELINE;
dbms_output.put_line('Refreshed count: '||v_row_count) ;
end;
/
4.2 - Confirm data loaded correctly
SELECT * FROM OAC_USER.CUSTOMER_PIPELINE
Schedule
Sample PL/SQL on how to schedule jobs:
BEGIN
cloud_scheduler.create_job(
job_name => 'LOAD_SALES_CLOUD_PIPELINE',
job_type => 'STORED_PROCEDURE',
job_action => 'run_bics_scs_integration',
start_date => '04-MAY-15 07.00.00.000000 AM -05:00',
repeat_interval => 'FREQ=DAILY',
enabled => TRUE,
comments => 'Loads SALES_CLOUD_PIPELINE');
END;
View Jobs:
SELECT * FROM user_scheduler_jobs;
Delete Job:
BEGIN
cloud_scheduler.drop_job(
job_name => 'LOAD_SALES_CLOUD_PIPELINE');
END;
Run Job Now:
BEGIN
cloud_scheduler.run_job('LOAD_SALES_CLOUD_PIPELINE');
END;
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
Click here for more information on using the APEX_WEB_SERVICE API.
Summary
This blog has provided a set of sample artifacts that leverage the APEX_WEB_SERVICE API to integrating OTBI with ADW. The example uses SOAP style web services. This blog may be a good starting point for those wanting to perform similar integrations using RESTful style web services.
Jay Pearson
Consulting Solutions Architect, Business Intelligence
Next Post