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

Introduction

 

This article provides a fresh approach on the subject of integrating Oracle Sales Cloud with Oracle Business Intelligence Cloud Service (BICS).


Integrating Oracle Sales Cloud with Oracle Business Intelligence Cloud Service (BICS) – Part 1,
showcased how to use Oracle Transactional Business Intelligence (OTBI) to extract data from Sales Cloud and load it into BICS.


This article tackles the reverse data movement pattern – loading data from a BICS dashboard into Sales Cloud.


Data is inserted into Sales Cloud using the REST API for Oracle Sales Cloud. This is the more conventional part of the solution, using similar concepts covered in past BICS integration blogs such as:


1)    PL/SQL is used for the ETL.


2)    A Database Stored Procedure is triggered by a Database Function.


3)    The Database Function is referenced in the Modeler using EVALUATE.


4)    The data-load is triggered from a Dashboard using an Action Link.


5)    Dashboard Prompts are used to pass selected values from the Dashboard

       to the Stored Procedure using Request and Session Variables.

sales_cloud_blog

The more ambitious component of this article is replicating the user experience of scraping data from a dynamically filtered Dashboard Analysis Request. Write-back is emulated by replicating what the user views on the Dashboard in a stored procedure SQL SELECT.


1)    The Dashboard Consumer refines the results on the Dashboard with a Prompt that represents a unique record identifier.


2)    The Dashboard Prompt Sections are passed to the Stored Procedure SELECT and used in a WHERE CLAUSE to replicate the refinement that the Dashboard Consumer makes on the Dashboard.

Snap16


Note: When replicating Dashboard SQL in the stored procedure, be cautious of data that has had row level security applied in the Modeler. To avoid erroneous results, all customized permissions must be manually enforced through the stored procedure SQL WHERE clause.


The following steps walk-through the creation of the necessary BICS and PL/SQL artifacts needed to load data from a BICS Dashboard into Sales Cloud. The example provided interprets the contact information from the Dashboard and creates a new matching contact in Sales Cloud. This example could be easily modified to support other REST API methods.


Part A – Configure BICS Dashboard


1)    Create BICS Table

2)    Insert Records into BICS Table

3)    Create Analysis Request

4)    Create Dashboard


Part B – Configure PL/SQL


5)    Review REST API Documentation

6)    Test POST Method

7)    Create Stored Procedure

8)    Test Stored Procedure

9)    Create Function

10)  Test Function


PART C – Configure Action Link Trigger


11)  Create DUMMY_PUSH Table

12)  Create Variable

13)  Reference Variable

14)  Create Model Expression

15)  Create DUMMY_PUSH Analysis Request

16)  Create Action Link

17)  Execute Update

 

Main Article


Part A – Configure BICS Dashboard


Step 1 – Create BICS Table


Create a simple “contacts” table in Oracle Application Express (Apex) -> SQL Workshop -> SQL Commands.

Where CONTACT_KEY is the unique record identifier that will be used to refine the data on the Dashboard. This must be something that the Dashboard Consumer can easily recognize and decipher.

CREATE TABLE BICS_CONTACTS(
FIRST_NAME VARCHAR2(500),
LAST_NAME VARCHAR2(500),
ADDRESS1 VARCHAR2(500),
CITY VARCHAR2(500),
COUNTRY VARCHAR2(500),
STATE VARCHAR2(500),
CONTACT_KEY VARCHAR2(500));


Step 2 – Insert Records into BICS Table


Insert a selection of sample contact records into the contacts table.

For a text version of both SQL snippet click here.

INSERT INTO BICS_CONTACTS(FIRST_NAME,LAST_NAME,ADDRESS1,CITY,COUNTRY,STATE,CONTACT_KEY)
VALUES (‘Jay’,’Pearson’,’7604 Technology Way’,’Denver’,’US’,’CO’,’Pearson-Jay’);

Snap17


Step 3 – Create Analysis Request


Add the BICS_CONTACTS table to the Model and join it to another table.

Create an Analysis Request based on the BICS_CONTACTS table.

Add a filter on CONTACT_KEY where Operator = “is prompted”.

Snap5

 Snap2

Snap3

Step 4 – Create Dashboard


Create a Dashboard. Add the BICS_Contacts Analysis and a Prompt on CONTACT_KEY. To keep the example simple, a List Box Prompt has been used. Additionally, “Include All Column Values” and “Enable user to select multiple values” are disabled. It is possible to use both these options, with extra manual SQL in the stored procedure. 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).

Snap7

Part B – Configure PL/SQL


Step 5 – Review REST API Documentation


Begin by reviewing the REST API for Oracle Sales Cloud documentation. This article only covers using:

Task: Create a contact
Request: POST
URI: crmCommonApi/resources/<version>/contact

That said there are many other tasks / requests available in the API that may be useful for various integration scenarios.

Step 6 – Test POST Method


From Postman:

Snap14

Snap15

Snap13

From Curl:

For a text version of the curl click here.

curl -u user:pwd -X POST -v -k -H “Content-Type: application/vnd.oracl
e.adf.resourceitem+json” -H “Cache-Control: no-cache” -d@C:\temp\contact.json ht
tps://abcd-fap1234-crm.oracledemos.com:443/crmCommonApi/resources/11.1.10/contacts

Where C:\temp\contact.json is:

For a text version of the JSON click here.

{
“FirstName”: “John Barry”,
“LastName”: “Smith”,
“Address”: [
{
“Address1”: “100 Oracle Parkway”,
“City”: “Redwood Shores”,
“Country”: “US”,
“State”: “CA”
}
]
}

Confirm Contact was created in Sales Cloud.

Snap9 Snap10

Snap11

Snap12

Step 7 – Create Stored Procedure


Replace Sales Cloud Server Name, Username, and Password.

For a text version of the code snippet click here.

create or replace PROCEDURE PUSH_TO_SALES_CLOUD(p_selected_records varchar2, o_status OUT varchar2) IS
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := ‘https://abc1-fap1234-crm.oracledemos.com:443/crmCommonApi/resources/11.1.10/contacts’;
l_body CLOB;
v_array apex_application_global.vc_arr2;
v_first_name VARCHAR2(100);
v_last_name VARCHAR2(100);
v_address1 VARCHAR2(100);
v_city VARCHAR2(100);
v_country VARCHAR2(100);
v_state VARCHAR2(100);
v_status VARCHAR2(100);

v_array := apex_util.string_to_table(p_selected_records, ‘,’);
FOR j in 1..v_array.count LOOP
SELECT FIRST_NAME,LAST_NAME,ADDRESS1,CITY,COUNTRY,STATE
INTO v_first_name, v_last_name, v_address1, v_city, v_country, v_state
FROM BICS_CONTACTS
WHERE CONTACT_KEY = v_array(j);
l_body := ‘{
“FirstName”: “‘ || v_first_name ||
‘”,”LastName”: “‘ || v_last_name ||
‘”,”Address”: [{“Address1”: “‘ || v_address1 ||
‘”,”City”: “‘ || v_city ||
‘”,”Country”: “‘ || v_country ||
‘”,”State”: “‘ || v_state ||
‘”}]}’;
–dbms_output.put_line(‘Body:’ || dbms_lob.substr(l_body));
apex_web_service.g_request_headers(1).name := ‘Content-Type’;
apex_web_service.g_request_headers(1).value := ‘application/vnd.oracle.adf.resourceitem+json’;
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_body => l_body,
p_username => ‘User‘,
p_password => ‘Pwd‘,
p_http_method => ‘POST’
);
v_status := apex_web_service.g_status_code;
–dbms_output.put_line(‘Status:’ || dbms_lob.substr(v_status));
END LOOP;
o_status :=v_status;
COMMIT;
–;

Step 8 – Test Stored Procedure


For a text version of the code snippet click here.

declare
o_status integer;

PUSH_TO_SALES_CLOUD(‘Pearson-Jay’,o_status);
dbms_output.put_line(o_status);
–;

RETURNS: 201 – indicating successful creation of contact

Step 9 – Create Function


For a text version of the code snippet click here.

create or replace FUNCTION FUNC_PUSH_TO_SALES_CLOUD
(
p_selected_records IN VARCHAR2
) RETURN VARCHAR
IS PRAGMA AUTONOMOUS_TRANSACTION;
o_status VARCHAR2(100);

PUSH_TO_SALES_CLOUD(p_selected_records,o_status);
COMMIT;
RETURN o_status;
–;

Step 10 – Test Function


For a text version of the SQL click here.

select FUNC_PUSH_TO_SALES_CLOUD(‘Pearson-Jay’)
from dual;

RETURNS: 201 – indicating successful creation of contact


Part C – Configure Action Link Trigger


Quick Re-Cap:


It may be useful to revisit the diagram provided in the intro to give some context to where we are at.

“Part A” covered building the Dashboard show in #4

“Part B” covered building items #1 & #2.

“Part C” will now cover the remaining artifacts show in #3, and #4.


Step 11 – Create DUMMY_PUSH Table


This tables main purpose is to trigger the database function. It must have a minimum of one column and maximum one row. It is important that this table only has one row as the function will be trigger for every row in this table.

For a text version of the SQL click here.

CREATE TABLE DUMMY_PUSH (REFRESH_TEXT VARCHAR2(255));

INSERT INTO DUMMY_PUSH(REFRESH_TEXT) VALUES (‘Status:’);

 

Step 12 – Create Variable


From the Modeler create a variable called “r_selected_records”. Provide a starting Value and define the SQL Query.

Snap19

Step 13 – Reference the Variable


On the Dashboard Prompt (created in Part A – Step 4) set a “Request Variable” matching the name of the Variable (created in Part C – Step 12). i.e. r_selected_records

 Snap32

 

Step 14 – Create Model Expression


Add the DUMMY_PUSH table to the Model. Join it to another table.

Add an Expression Column called PUSH_TO_SALES_CLOUD.

Use EVALUATE to call the database function “FUNC_PUSH_TO_SALES_CLOUD” passing through the variable “r_selected_records”.

Snap20

For a text version of the EVALUATE statement click here.

EVALUATE(‘FUNC_PUSH_TO_SALES_CLOUD(%1)’,VALUEOF(NQ_SESSION.”r_selected_records”))

Snap18


Step 15 – Create DUMMY_PUSH Analysis Request


Add both fields to the Analysis – hiding column headings if desired.

Snap21

Snap30

Snap31

Step 16 – Create Action Link


Add an Action Link to the Dashboard. Choose Navigate to BI Content.

Snap24

 Check “Run Confirmation” and customize message if needed.

Snap26

Customize the Link Text and Caption (if desired).

Snap27

17)  Execute Update


Select the user to insert into Sales Cloud.

*** Important ***

CLICK APPLY

Apply must be hit to set the variable on the prompt!

Snap28

Click the “Push to Sales Cloud” Action Link”.

Confirm the Action

Snap29

Status 201 is returned indicating the successful creation of the contact.

Snap35

Confirm contact was created in Sales Cloud.

Snap34

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 REST API for Oracle Sales Cloud guide.

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


Summary


This article provided a set of examples that leverage the APEX_WEB_SERVICE_API to integrate Oracle Sales Cloud with Oracle Business Intelligence Cloud Service (BICS) using the REST API for Oracle Sales Cloud.

The use case shown was for BICS and Oracle Sales Cloud integration. However, many of the techniques referenced could be used to integrate Oracle Sales Cloud with other Oracle and non-Oracle applications.

Similarly, the Apex MAKE_REST_REQUEST example could be easily modified to integrate BICS or standalone Oracle Apex with any other REST web services.

Techniques referenced in this blog could be useful for those building BICS REST ETL connectors and plug-ins.

Add Your Comment