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

Introduction

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

PLEASE NOTE this article is intended to provide the reader with some ideas of how to start an integration with Service Cloud.  The reader would also need to consider other factors such as Limits, Fault Tolerance, Scalability, Efficiency, Latency, etc which are common with any enterprise integration.  This article is intended to be part of that overall integration, and not the only piece.  Please consult with an integration expert.

Two artifacts are created in the process:

1)    BICS Database Table

The table is created within the BICS database to load / store the Service Cloud (RightNow) data.

2)    Stored Procedure – within BICS Database

A stored procedure is created within the BICS Schema Service Database. The stored procedure uses the apex_web_service.make_request function to call an external SOAP web service; that runs a ROQL query returning the results from Service Cloud (RightNow) in XML format. The apex_web_service.parse_xml function is used to parse the XML output to a delimited list. The results are then separated into columns and inserted into the BICS Database Table created in step 1.

The ten steps below are covered in detail to assist in replicating and testing the integration solution. Those already very familiar with “Cloud Service (RightNow) web services”, “ROQL”, and the “Apex API”, may prefer to jump straight to step five. That said, steps one to four are very useful for constructing, comprehending and debugging the apex_web_service.make_request and apex_web_service.parse_xml functions referenced in the stored procedure.

1)    Obtain the Standard WSDL

2)    Construct the ROQL Query

3)    Build the Soap Envelope

4)    Formulate the XPath

5)    Create the BICS Database Table

6)    Create the Stored Procedure

7)    Execute the Stored Procedure

8)    Refresh the Results

9)    Display the Results

10)  Schedule

 

Main Article

Step One – Obtain the Standard WSDL

1)    Collect and make note of the “host name” and the “interface name” from the Oracle Service Cloud (RightNow) environment.

2)    Open the web browser and enter the following two URL’s, testing the below standard WSDL’s, replacing the host name and interface values.

First Test

URL: https://<host_name>/cgi-bin/<interface>.cfg/services/soap?wsdl

Sample URL: https://integration-test.rightnowdemo.com/cgi-bin/integration_test.cfg/services/soap?wsdl

Second Test

URL: https://<host_name>/cgi-bin/<interface>.cfg/services/soap?wsdl=typed

Sample URL: http://integration-test.rightnowdemo.com/cgi-bin/integration_test.cfg/services/soap?wsdl=typed

Where host_name is integration-test.rightnowdemo.com and interface is integration_test

For both tests the following should be returned to the browser. If instead an error is displayed, the Oracle Service Cloud (RightNow) WSDL is incorrect and must be rectified before continuing.

Snap1

Step Two – Construct the ROQL Query

CXDev Toolbox can be used to build and test ROQL.

It can be downloaded from: http://toolbox.cxdeveloper.com

Launch CXDev, then enter the connection information below to login.

1)    Soap URL (obtained from pervious steps) – without the wsdl:

URL: https://<host_name>/cgi-bin/<interface>.cfg/services/soap

URL Sample: https://integration-test.rightnowdemo.com/cgibin/integration_test.cfg/services/soap

2)    Username

3)    Password – Note the password is in a strange white box that sits above the Soap URL.

4)    Click Login

Snap6

5)    Click on the ROQL Tester icon at the bottom of the screen.

Snap7

6)    Type in the ROQL

For Example:

Select  ID, Name.First,Contact.Address.City
From Contact
Where ID Between 1 and 100;

7)    Click Run Query. Results will be returned in a tabular format. Ensure the query returns results and runs error free before continuing.

Snap8

Step Three – Build the Soap Envelope

1)    Use SoapUI http://www.soapui.org to test the envelope.

Create a New SOAP Project. Give it any name. The Initial WSDL is what was tested in the previous step (remember to add wsdl-typed at the end).

URL: https://<host_name>/cgi-bin/<interface>.cfg/services/soap?wsdl=typed

For example: http://integration-test.rightnowdemo.com/cgi-bin/integration_test.cfg/services/soap?wsdl=typed

Snap2

2)    Double Click on QueryCSV -> Request

Snap3

3)    Delete everything from the request and replace with the following.

Click Here for text version of Soap Envelope.

Replace Username and Password with the Oracle Service Cloud (RightNow) Username / Password.

Replace the SQL if the ROQL Query has been customized.

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:v1=”urn:messages.ws.rightnow.com/v1_2″>
<soapenv:Header>
<v1:ClientInfoHeader>
<v1:AppID>Basic Create</v1:AppID>
</v1:ClientInfoHeader>
<wsse:Security xmlns:wsse=”http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd” mustUnderstand=”1″>
<wsse:UsernameToken>
<wsse:Username>Username</wsse:Username>
<wsse:Password Type=”http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText”>Password</wsse:Password>
</wsse:UsernameToken>
</wsse:Security>
</soapenv:Header>
<soapenv:Body>
<v1:QueryCSV>
<v1:Query>Select  ID, Name.First,Contact.Address.City from Contact where ID between 1 and 100</v1:Query>
<v1:PageSize>100</v1:PageSize>
<v1:Delimiter>~</v1:Delimiter>
</v1:QueryCSV>
</soapenv:Body>
</soapenv:Envelope>

4)    Submit the request. It should return the ID, First Name and City details in XML. For example

Click Here for text version of XML Output

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/”>
<soapenv:Header/>
<soapenv:Body>
<n0:QueryCSVResponse xmlns:n0=”urn:messages.ws.rightnow.com/v1_2″ xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<n0:CSVTableSet>
<n0:CSVTables>
<n0:CSVTable>
<n0:Name>Contact</n0:Name>
<n0:Columns>ID~First~City</n0:Columns>
<n0:Rows>
<n0:Row>1~Cliente~</n0:Row>
<n0:Row>2~Thiago~</n0:Row>
<n0:Row>3~Contato~</n0:Row>
<n0:Row>4~Teste~</n0:Row>
<n0:Row>5~Thiago~</n0:Row>
<n0:Row>6~Marcelle~Rio de Janeiro</n0:Row>
<n0:Row>7~Novo CRM~Rio de Janeiro</n0:Row>
<n0:Row>9~OI~</n0:Row>
<n0:Row>10~Teste~</n0:Row>
<n0:Row>11~Contato do Cliente~</n0:Row>
<n0:Row>12~Contato do Cliente 2~</n0:Row>
<n0:Row>37~TEste~</n0:Row>
<n0:Row>38~John~</n0:Row>
<n0:Row>39~John1~</n0:Row>
<n0:Row>40~John2~</n0:Row>
<n0:Row>41~rrrrrr~</n0:Row>
<n0:Row>58~Jane601~Redwood Shores</n0:Row>
<n0:Row>59~Jane602~Redwood Shores</n0:Row>
<n0:Row>60~Jane603~Redwood Shores</n0:Row>
<n0:Row>61~Jane604~Redwood Shores</n0:Row>
<n0:Row>62~Jane605~Redwood Shores</n0:Row>
<n0:Row>63~Jane606~Redwood Shores</n0:Row>
<n0:Row>64~Jane607~Redwood Shores</n0:Row>
<n0:Row>65~Jane608~Redwood Shores</n0:Row>
<n0:Row>66~Jane609~Redwood Shores</n0:Row>
<n0:Row>67~Jane610~Redwood Shores</n0:Row>
<n0:Row>68~Jane701~Redwood Shores</n0:Row>
<n0:Row>69~Jane611~Redwood Shores</n0:Row>
<n0:Row>70~Jane612~Redwood Shores</n0:Row>
<n0:Row>71~Jane702~Redwood Shores</n0:Row>
<n0:Row>72~Jane613~Redwood Shores</n0:Row>
<n0:Row>73~Jane703~Redwood Shores</n0:Row>
<n0:Row>74~Jane614~Redwood Shores</n0:Row>
<n0:Row>75~Jane704~Redwood Shores</n0:Row>
<n0:Row>76~Jane615~Redwood Shores</n0:Row>
<n0:Row>77~Jane705~Redwood Shores</n0:Row>
<n0:Row>78~Jane616~Redwood Shores</n0:Row>
<n0:Row>79~Jane706~Redwood Shores</n0:Row>
<n0:Row>80~Jane617~Redwood Shores</n0:Row>
<n0:Row>81~Jane707~Redwood Shores</n0:Row>
<n0:Row>82~Jane618~Redwood Shores</n0:Row>
<n0:Row>83~Jane708~Redwood Shores</n0:Row>
<n0:Row>84~Jane619~Redwood Shores</n0:Row>
<n0:Row>85~Jane709~Redwood Shores</n0:Row>
<n0:Row>86~Jane620~Redwood Shores</n0:Row>
<n0:Row>87~Jane710~Redwood Shores</n0:Row>
<n0:Row>88~Jane711~Redwood Shores</n0:Row>
<n0:Row>89~Jane621~Redwood Shores</n0:Row>
<n0:Row>90~Jane712~Redwood Shores</n0:Row>
<n0:Row>91~Jane622~Redwood Shores</n0:Row>
<n0:Row>92~Jane713~Redwood Shores</n0:Row>
<n0:Row>93~Jane623~Redwood Shores</n0:Row>
<n0:Row>94~Jane714~Redwood Shores</n0:Row>
<n0:Row>95~Jane624~Redwood Shores</n0:Row>
<n0:Row>96~Jane715~Redwood Shores</n0:Row>
<n0:Row>97~Jane625~Redwood Shores</n0:Row>
<n0:Row>98~Jane716~Redwood Shores</n0:Row>
<n0:Row>99~Jane626~Redwood Shores</n0:Row>
<n0:Row>100~Jane717~Redwood Shores</n0:Row>
</n0:Rows>
</n0:CSVTable>
</n0:CSVTables>
</n0:CSVTableSet>
</n0:QueryCSVResponse>
</soapenv:Body>
</soapenv:Envelope>

Step Four – Formulate the XPath

1)    Free Formatter XPath Tester can be used to build and test the XPath. This is particularly helpful for debugging.

http://www.freeformatter.com/xpath-tester.html

Copy the XML output returned by SOAPUI into the XML Input box.

Click Here for text version of XML Output

Copy the following XPath in the XPath expression box.

Click Here for XPath’s in text format.

//n0:QueryCSVResponse/n0:CSVTableSet/n0:CSVTables/n0:CSVTable/n0:Rows/n0:Row/text()

2)    Click: TEST XPATH

The below “Namespace with prefix ‘n0’ has not been declared” error may be encountered.

Snap4

3)    To resolve this error copy the n0 name space definition. Copy from Here (as the quotes do not copy correctly from the browser).

xmlns:n0=”urn:messages.ws.rightnow.com/v1_2″

4)    And place it after:

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/”

5)    It should look like this (confirm quotes copied / pasted correctly and are in the correct positions to avoid errors).

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:n0=”urn:messages.ws.rightnow.com/v1_2″>

6)    Re-Click TEST XPATH

The results will be returned in a delimited list. If an error has occurred, the XPath may need correcting.

Snap5

Step Five – Create the BICS Database Table

1)    Open SQL Workshop from Oracle Application Express

3

 

2)    Launch SQL Commands

4

 

 

 

 

 

 

 

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

To view SQL in plain text click here: CREATE_TABLE

CREATE TABLE “RIGHTNOW”
(“ID” NUMBER,
“FIRST_NAME” VARCHAR2(100 BYTE),
“CITY” VARCHAR2(100 BYTE)
);

Snap9

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step Six – Create the Stored Procedure

Create and run the BICS_RN_INTEGRATION stored procedure that calls the web service to pull the Service Cloud (RightNow) data and load’s the data into the RIGHTNOW table.

The stored procedure leverages the APEX_WEB_SERVICE API (using Apex_web_service.make_request). This example invokes 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. Apex_web_service.parse_xml is used to extract the data set. It is returned as a delimiter list – separated by “~”. The results are then extracted into columns and then loaded into the database table.

Replace the following highlighted values:

1)    l_envelope – If changed in “Step Three – Build the Soap Envelope” then replace with custom envelope.

2)    Username

3)    Password

4)    ROQL Query – The ROQL Query constructed in “Step Two – Construct the ROQL Query“.

5)    p_url – The Standard WSDL obtained in “Step One – Obtain the Standard WSDL” excluding the endpoint. Should end in /soap.

6)    p_xpath – If changed it in “Step Four – Formulate the XPath” then replace with custom Xpath.

7)    Table Name –  The name of the table created in “Step Five – Create the BICS Database Table

8)    Insert Values – Duplicate for each column. Increase by +1 in the second regexp_substr parameter / position.

~

To view SQL in plain text click here: BICS_RN_INTEGRATION

CREATE OR REPLACE PROCEDURE “BICS_RN_INTEGRATION”
IS
l_envelope CLOB;
l_xml XMLTYPE;
f_index number ;
l_str varchar2(1000);
BEGIN
l_envelope := ‘<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:v1=”urn:messages.ws.rightnow.com/v1_2″>
<soapenv:Header>
<v1:ClientInfoHeader>
<v1:AppID>Basic Create</v1:AppID>
</v1:ClientInfoHeader>
<wsse:Security xmlns:wsse=”http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd” mustUnderstand=”1″>
<wsse:UsernameToken>
<wsse:Username>Username</wsse:Username>
<wsse:Password Type=”http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText”>Password</wsse:Password>
</wsse:UsernameToken>
</wsse:Security>
</soapenv:Header>
<soapenv:Body>
<v1:QueryCSV>
<v1:Query>Select ID, Name.First,Contact.Address.City From Contact Where ID Between 1 and 100</v1:Query>
<v1:PageSize>100</v1:PageSize>
<v1:Delimiter>~</v1:Delimiter>
</v1:QueryCSV>
</soapenv:Body>
</soapenv:Envelope>’;
l_xml := apex_web_service.make_request(
p_url => ‘https://integration-test.rightnowdemo.com/cgibin/integration_test.cfg/services/soap
,p_envelope => l_envelope
,p_proxy_override => ‘www-proxy.us.oracle.com’
);
–dbms_output.put_line ( l_xml.getCLOBVal() );
delete from rightnow;
f_index := 0;
LOOP
f_index := f_index +1;
l_str := apex_web_service.parse_xml(
p_xml => l_xml,
p_xpath => ‘//n0:QueryCSVResponse/n0:CSVTableSet/n0:CSVTables/n0:CSVTable/n0:Rows/n0:Row[‘||to_char(f_index)||’]/text()‘,
p_ns => ‘xmlns:n0=”urn:messages.ws.rightnow.com/v1_2″‘ );
— dbms_output.put_line(dbms_lob.substr(l_str,24000,1));
insert into rightnow
values (
regexp_substr(l_str, ‘[^~]+’, 1, 1),
regexp_substr(l_str, ‘[^~]+’, 1, 2),
regexp_substr(l_str, ‘[^~]+’, 1, 3)
);
exit WHEN l_str is null;
end loop;
commit;
END;
/

Snap10

Step Seven – Execute the Stored Procedure

1)    Execute the Stored Procedure in Oracle Apex

begin
BICS_RN_INTEGRATION();
end;
/

Snap11

2)    Confirm data loaded correctly.

Snap15

Step Eight – Refresh the Results

Once the data is loaded the cache must be manually cleared in Data Modeler. In July 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”.

Snap12

Step Nine – Display the Results


Snap13

Snap14

Step Ten – Schedule

To schedule data loading use the Cloud Scheduler.

Create Job:

BEGIN
cloud_scheduler.create_job(
job_name => ‘LOAD_RIGHTNOW’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘BICS_RN_INTEGRATION’,
start_date => ’01-JUN-30 07.00.00.000000 AM -05:00′,
repeat_interval => ‘FREQ=DAILY’,
enabled => TRUE,
comments => ‘Loads RIGHTNOW’);
END;

View Jobs:

SELECT * FROM user_scheduler_jobs;

Delete Job:

BEGIN
cloud_scheduler.drop_job(
job_name => ‘LOAD_RIGHTNOW’);
END;

Run Job Now:

BEGIN
cloud_scheduler.run_job(‘LOAD_RIGHTNOW’);
END;

Further Reading

Click here for the Application Express API Reference Guide – Chapter 22 – APEX_WEB_SERVICE.

Click here for the Oracle Service Cloud Connect Web Services for SOAP Developers Guide.

Click here for the Oracle Service Cloud Connect REST API Developers Guide.

Summary

This article has provided a set of example artifacts that leverage the APEX_WEB_SERVICE_API to integrate Oracle Service Cloud (RightNow) with Oracle Business Intelligence Cloud Service (BICS) using SOAP style web services. This article may also be a good starting point for those wanting to perform similar integrations using RESTful style web services. This method could also be used to integrate BICS with other Oracle and third-party applications.

Add Your Comment