Integrating Oracle Sales Cloud with Oracle Business Intelligence Cloud Service (BICS) – Part 1

Introduction

This blog describes how to integrate Oracle Sales Cloud with Business Intelligence Cloud Service (BICS). The blog outlines how to programmatically load Sales Cloud data into BICS, making it readily available to model and display on BICS dashboards.

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)    BICS Database Table

The table is created within the BICS database to load / store the Sales Cloud data.

3)    Stored Procedure – within BICS Database

A dynamic stored procedure is created within the BICS database (either Database Schema Service or Database as a Service). 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 BICS Database Table created in step 2.

The steps below detail how to create these three artifacts and call the data load integration process.

Main Article

Creating the OTBI Answers / Analysis Request

1) Create the OTBI Answers / Analysis report in Sales Cloud Analytics

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

10

 

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

11

3) Confirm the request returns the desired results

2

Create the BICS Database Table

(This example uses Oracle Database Schema Service)

1) Open SQL Workshop from Oracle Application Express

3

 

2)    Launch SQL Commands

4

 

 

 

 

 

 

 

3)    Run the CREATE_TABLE SQL Command to create the SALES_CLOUD_PIPELINE table in the BICS database

*** 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.

To view SQL in plain text click here: CREATE_TABLE

CREATE TABLE “SALES_CLOUD_PIPELINE”
   (    “OPPORTUNITY_ID” NUMBER,
    “OPPORTUNITY_NAME” VARCHAR2(100 BYTE),
    “OPTY_DATE” DATE,
    “CUSTOMER_NAME” VARCHAR2(100 BYTE),
    “CUSTOMER_STATE” VARCHAR2(50 BYTE),
    “PRODUCT_NAME” VARCHAR2(100 BYTE),
    “CATALOG” VARCHAR2(50 BYTE),
    “PRODUCT_FAMILY” VARCHAR2(50 BYTE),
    “QTY” NUMBER,
    “OPPORTUNITY_REVENUE” NUMBER,
    “OWNER_LAST_NAME” VARCHAR2(10 BYTE),
    “TERRITORY_OWNER” VARCHAR2(50 BYTE),
    “TERRITORY” VARCHAR2(50 BYTE),
     “LOAD_DTTM” DATE,
    “LOADED_BY_USER” VARCHAR2(32 BYTE)
   );

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Creating the Stored Procedure – within BICS Database

Create and run the bics_scs_integration stored procedure that calls the web service to pull the Sales Cloud data and load the Pipeline data into the SALES_CLOUD_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.

~

To view SQL in plain text click here: bics_scs_integration

create or replace procedure bics_scs_integration
    (
         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;
/

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Execute stored procedure BICS_SCS_INTEGRATION

Replace the below parameters:

1)    Sales Cloud Analytics Server Name

2)    Path and name of (OTBI) Answers / Analysis Request

3)    User with permissions to run OTBI Answers / Analysis Request

4)    Password of user running the request

5)    Column number and column data type of each field returned by the OTBI report

6)    Name of BICS Database table loading data into

To view SQL in plain text click here: run_bics_scs_integration

declare
v_row_count number ;
begin
select count(*) into v_row_count from SALES_CLOUD_PIPELINE;
dbms_output.put_line(‘Original count: ‘||v_row_count) ;
delete SALES_CLOUD_PIPELINE;
commit ;
select count(*) into v_row_count from SALES_CLOUD_PIPELINE;
dbms_output.put_line(‘Deleted count: ‘||v_row_count) ;
bics_scs_integration
(
‘https://servername.oraclecloud.com/analytics-ws/saw.dll?SoapImpl=nQSessionService’
,’https://servername.oraclecloud.com/analytics-ws/saw.dll?SoapImpl=xmlViewService’
,’/shared/Custom/Sales/Pipeline/Sales_Cloud_Extract
,’username
,’password
,’SALES_CLOUD_PIPELINE
,’Column0:NUMBER|Column1:VARCHAR2(100)|Column2:DATE|Column3:VARCHAR2(100)|Column4:VARCHAR2(50)|Column5:VARCHAR2(100)|Column6:VARCHAR2(50)|Column7:VARCHAR2(50)|Column8:NUMBER|Column9:NUMBER|Column10:VARCHAR2(10)|Column11:VARCHAR2(50)|Column12:VARCHAR2(50)’
);
select count(*) into v_row_count from SALES_CLOUD_PIPELINE;
dbms_output.put_line(‘Refreshed count: ‘||v_row_count) ;
end;
/

Confirm data loaded correctly

7

 

Confirm Sales Cloud Data is now available in BICS

8

Cache

Once the data is loaded the cache must be manually cleared in Data Modeler. In June 2015 purge of cache via the BICS REST API will be made available. Until then manually clear the cache from the Data Modeler by clicking on the cog to the right of the table and selecting “Clear Cached Data”.

9

Schedule

To schedule data loading use the Cloud Scheduler.

Create Job:

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;

Further Reading

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 Oracle Sales Cloud with Oracle Business Intelligence Cloud Service (BICS). The example uses SOAP style web services. That said, the blog may also be a good starting point for those wanting to perform similar integrations using RESTful style web services. Likewise, the blog focuses on integration with Sales Cloud and BICS. However, this methodology could also be used to integrate BICS with other Oracle and third-party applications.

Add Your Comment