Integrating Oracle Social Data and Insight Cloud Service with Oracle Business Intelligence Cloud Service (BICS)

Introduction

 

This article outlines how to integrate Oracle Social Data and Insight Cloud Service with Oracle Business Intelligence Cloud Service (BICS). Two primary data movement patterns are described:

(a) JSON data is retrieved from an external cloud source using REST Web Services.

(b) Data is inserted into the Schema Service database with Apex PL/SQL functions.

The above topics have previously been discussed in past A-Team BICS Blogs. What makes this article unique, is that it retrieves and displays the results real-time. These results are stored temporary in the database while viewed via a Dashboard. This data could then be permanently archived if desired.

The eighteen steps below have been broken into two parts. Part A follows a similar to pattern to that covered in “Integrating Oracle Service Cloud (RightNow) with Oracle Business Intelligence Cloud Service (BICS) – Part 2”. Part B incorporates ideas covered in “Executing a Stored Procedure from Oracle Business Intelligence Cloud” Service” and “Using the Oracle Business Intelligence Cloud Service (BICS) REST API to Clear Cached Data“.


PART A – Retrieve & Load Data


1)    Review REST API Documentation

2)    Build REST Search Endpoint URL

3)    Run apex_web_service.make_rest_request

4)    Formulate JSON Path

5)    Create BICS Tables

6)    Parse JSON

7)    Execute PL/SQL

8)    Review Results


PART B – Trigger Results Real-Time


9)    Add Clear BI Server Cache Logic

10)  Create Function – to execute Stored Procedure

11) Test Function – that executes Stored Procedure

12)  Create Dummy Table (to reference the EVALUATE function)

13)  Create Repository Variables

14)  Create Expression in Data Modeler (that references EVALUATE function)

15)  Create Analysis – that executes EVALUATE function

16)  Create Analysis – to display results

17)  Create Dashboard Prompt

18)  Create Dashboard

Main Article

 

Part A – Retrieve & Load Data

 

Step 1 – Review REST API Documentation

 

Begin by reviewing the REST APIs for Oracle Social Data and Insight Cloud Service documentation. This article only covers using the /v2/search end point. The /v2/search is used to retrieve a list of companies or contacts that match a given criteria. There are many other end points available in the API that may be useful for various integration scenarios.

Step 2 – Build REST Search Endpoint URL

 

Access Oracle Social Data and Insight Cloud Service from Oracle Cloud My Services.

The Service REST Endpoint (Company and Contact Data API) will be listed.

For Example: https://datatrial1234-IdentityDomain.data.us9.oraclecloud.com/data/api

Append v2/search to the URL.

For Example: https://datatrial1234-IdentityDomain.data.us9.oraclecloud.com/data/api/v2/search

Step 3 – Run apex_web_service.make_rest_request

 

1)    Open SQL Workshop from Oracle Application Express

Snap2

2)    Launch SQL Commands

Snap3

3)    Use the code snippet below as a starting point to build your PL/SQL.

Run the final PL/SQL in the SQL Commands Window.

Replace the URL, username, password, identity domain, and body parameters.

For a text version of the code snippet click here.

For detailed information on all body parameters available click here.

l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := ‘YourURL/data/api/v2/search’;
l_body CLOB;

l_body := ‘{“objectType”:”People”,”limit”:”100″,”filterFields”:
[{“name”:”company.gl_ult_dun”,”value”:”123456789″},
{“name”:”person.management_level”,”value”:”0″},
{“name”:”person.department”,”value”:”3″}],”returnFields”:
[“company.gl_ult_dun”,”person.parent_duns”, “person.first_name”,
“person.last_name”,”person.department”,”person.management_level”,”person.gen
der_code”,”person.title”,”person.standardized_title”,”person.age_range”,”per
son.company_phone”,”person.company_phone_extn”,”name.mail”,”person.co_offica
l_id”]}‘;
–use rest to retrieve the Data Service Cloud – Social Data
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 := ‘Identity Domain’;
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_username => ‘Username‘,
p_password => ‘Password‘,
p_body => l_body,
p_http_method => ‘POST’
);
dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,12000,1));
dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,12000,12001));
dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,12000,24001));

4)    Run the query. A subset of the JSON results should be displayed in the Results section of SQL Commands.

Additional dbms_output.put_line’s may be added should further debugging be required.

It is not necessary at this stage to view the entire result set. The key to this exercise is to prove that the URL is correct and can successfully be run through apex_web_service.make_rest_request.

5)    Currently the body parameter filterFeilds only accepts “value” and not “DisplayValue”; thus, it may be necessary to create dimension lookup tables. For this exercise two dimension look-up tables are used.

Look-up values may change over time and should be re-confirmed prior to table creation.

“Step 5 –  Create the BICS Database Tables” describes how to create the two look-up tables below.

Department – Lookup Values

Value    DisplayValue
0        Administration
1        Consulting
2        Education
3        Executive
4        Facilities
5        Finance
6        Fraternal Organizations
7        Government
8        Human Resources
9        Operations
10       Other
11       Purchasing
12       Religion
13       Research & Development
14       Sales & Marketing
15       Systems

Management Level – Lookup Values

Value    DisplayValue
0        C-Level
1        Vice-President
2        Director
3        Manager
4        Other

 

Step 4 – Formulate JSON Path


1)    When formulating the JSON path expression, it may be useful to use an online JSON Path Expression Tester.

There are many different free JSON tools available online. The one below is: https://jsonpath.curiousconcept.com

2)    For this exercise the below values will be exacted from the JSON.

Each path was tested in the JSON Path Expression Tester.

The attribute numbers 1-12 are associated with the order in which returnFields have been specified in the body parameter. Thus, attribute numbers may differ from the example if:

a) Fields are listed in a different sequence.

b) An alternative number or combination of fields is defined.

Value                             JSON Path Expression 
totalHits                         ‘totalHits’
company.gl_ult_dun                 parties[*].attributes[1].value
person.parent_duns                 parties[*].attributes[2].value
person.first_name                  parties[*].attributes[3].value
person.last_name                   parties[*].attributes[4].value
person.department                  parties[*].attributes[5].value
person.management_level            parties[*].attributes[6].value
person.gender_code                 parties[*].attributes[7].value
person.title                       parties[*].attributes[8].value
person.standardized_title          parties[*].attributes[9].value
person.age_range                   parties[*].attributes[10].value
person.company_phone               parties[*].attributes[11].value
person.company_phone_extn          parties[*].attributes[12].value
name.mail                          parties[*].email
person.co_offical_id               parties[*].id

Step 5 –  Create BICS Tables

 

1)    Open SQL Workshop from Oracle Application Express

Snap2

2)    Launch SQL Commands

Snap3

3)    Create the SOCIAL_DATA_CONTACTS table in the BICS database.

To view the SQL in plain text click here.

CREATE TABLE SOCIAL_DATA_CONTACTS(
COMPANY_DUNS_NUMBER VARCHAR2(500),
CONTACT_DUNS_NUMBER VARCHAR2(500),
FIRST_NAME VARCHAR2(500),
LAST_NAME VARCHAR2(500),
DEPARTMENT VARCHAR2(500),
MANAGEMENT_LEVEL VARCHAR2(500),
GENDER VARCHAR2(500),
JOB_TITLE VARCHAR2(500),
STANDARDIZED_TITLE VARCHAR2(500),
AGE_RANGE VARCHAR2(500),
COMPANY_PHONE VARCHAR2(500),
COMPANY_PHONE_EXT VARCHAR2(500),
EMAIL_ADDRESS VARCHAR2(500),
INDIVIDUAL_ID VARCHAR2(500));

4)    Create and populate the DEPARTMENT_PROMPT look-up table in the BICS database.

CREATE TABLE DEPARTMENT_PROMPT(DEPT_NUM VARCHAR(500), DEPT_NAME VARCHAR2(500));

INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘0′,’Administration’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘1′,’Consulting’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘2′,’Education’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘3′,’Executive’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘4′,’Facilities’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘5′,’Finance’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘6′,’Fraternal Organizations’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘7′,’Government’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘8′,’Human Resources’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(‘9′,’Operations’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(’10’,’Other’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(’11’,’Purchasing’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(’12’,’Religion’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(’13’,’Research & Development’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(’14’,’Sales & Marketing’);
INSERT INTO DEPARTMENT_PROMPT(DEPT_NUM, DEPT_NAME) VALUES(’15’,’Systems’);

5)    Create and populate the MANAGEMENT_LEVEL_PROMPT look-up table in the BICS database.

CREATE TABLE MANAGEMENT_LEVEL_PROMPT(ML_NUM VARCHAR(500), ML_NAME VARCHAR2(500));

INSERT INTO MANAGEMENT_LEVEL_PROMPT(ML_NUM, ML_NAME) VALUES(‘0′,’C-Level’);
INSERT INTO MANAGEMENT_LEVEL_PROMPT(ML_NUM, ML_NAME) VALUES(‘1′,’Vice-President’);
INSERT INTO MANAGEMENT_LEVEL_PROMPT(ML_NUM, ML_NAME) VALUES(‘2′,’Director’);
INSERT INTO MANAGEMENT_LEVEL_PROMPT(ML_NUM, ML_NAME) VALUES(‘3′,’Manager’);
INSERT INTO MANAGEMENT_LEVEL_PROMPT(ML_NUM, ML_NAME) VALUES(‘4′,’Other’);

Step 6 – Parse JSON


For a text version of the PL/SQL snippet click here.

Replace URL, username, password, identity domain, and body parameters.

The code spinet has been highlighted in different colors grouping the various logical components.

Blue

Rest Request that retrieves the data in JSON format as a clob.

Yellow

Lookup “Value” codes based on users selection of “DisplayValue” descriptions.

Purple

Logic to handle ‘All Column Values’ when run from BICS. (This could be handled in many other ways … and is just a suggestion.)

Green

Convert JSON clob to readable list -> Parse JSON values and insert into database.

Code advice: Keep in mind that p_path is expecting a string. Therefore, it is necessary to concatenate any dynamic variables such as the LOOP / Count ‘i’ variable.

Red

Array to handle entering multiple duns numbers.

Grey

Left pad Duns numbers with zeros – as this is how they are stored in Oracle Social Data and Insight Cloud Service.

CREATE OR REPLACE PROCEDURE SP_LOAD_SOCIAL_DATA_CONTACTS(
p_company_duns varchar2
,p_department varchar2
,p_management_level varchar2
) IS
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := ‘YourURL/data/api/v2/search’;
l_body CLOB;
l_num_contacts NUMBER;
v_array apex_application_global.vc_arr2;
l_filter_fields VARCHAR2(500);
l_pad_duns VARCHAR2(9);
l_department_num VARCHAR2(100);
l_management_level_num VARCHAR2(100);

DELETE FROM SOCIAL_DATA_CONTACTS;
–lookup department code
IF p_department != ‘All Column Values’ THEN
SELECT MAX(DEPT_NUM) into l_department_num
FROM DEPARTMENT_PROMPT
WHERE DEPT_NAME = p_department;
END IF;
–lookup management level code
IF p_management_level != ‘All Column Values’ THEN
SELECT MAX(ML_NUM) into l_management_level_num
FROM MANAGEMENT_LEVEL_PROMPT
WHERE ML_NAME = p_management_level;
END IF;
–loop though company duns numbers
v_array := apex_util.string_to_table(p_company_duns, ‘,’);
for j in 1..v_array.count LOOP
–pad duns numbers with zeros – as they are stored in the system this way
l_pad_duns := LPAD(v_array(j),9,’0′);
–logic to handle All Column Values
IF p_department != ‘All Column Values’ AND p_management_level != ‘All Column Values’ THEN
l_filter_fields := ‘”filterFields”:[{“name”:”company.gl_ult_dun”,”value”:”‘ || l_pad_duns || ‘”},{“name”:”person.department”,”value”:”‘|| l_department_num || ‘”},{“name”:”person.management_level”,”value”:”‘|| l_management_level_num || ‘”}]’;
ELSE
IF p_department = ‘All Column Values’ AND p_management_level != ‘All Column Values’ THEN
l_filter_fields := ‘”filterFields”:[{“name”:”company.gl_ult_dun”,”value”:”‘ || l_pad_duns || ‘”},{“name”:”person.management_level”,”value”:”‘|| l_management_level_num || ‘”}]’;
ELSE
IF p_department != ‘All Column Values’ AND p_management_level = ‘All Column Values’ THEN
l_filter_fields := ‘”filterFields”:[{“name”:”company.gl_ult_dun”,”value”:”‘ || l_pad_duns || ‘”},{“name”:”person.department”,”value”:”‘|| l_department_num || ‘”}]’;
ELSE
l_filter_fields := ‘”filterFields”:[{“name”:”company.gl_ult_dun”,”value”:”‘ || l_pad_duns || ‘”}]’;
END IF;
END IF;
END IF;
–build dynamic body
l_body := ‘{“objectType”:”People”,”limit”:”100″,’ || l_filter_fields || ‘,”returnFields”:[“company.gl_ult_dun”,”person.parent_duns”, “person.first_name”, “person.last_name”,”person.department”,”person.management_level”,”person.gender_code”,”person.title”,”person.standardized_title”,”person.age_range”,”person.company_phone”,”person.company_phone_extn”,”name.mail”,”person.co_offical_id”]}’;
–use rest to retrieve the Data Service Cloud – Social Data
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 := ‘identity domain’;
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_username => ‘UserName’,
p_password => ‘Password’,
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

INSERT INTO SOCIAL_DATA_CONTACTS(COMPANY_DUNS_NUMBER, CONTACT_DUNS_NUMBER,FIRST_NAME,LAST_NAME,DEPARTMENT,MANAGEMENT_LEVEL,GENDER,JOB_TITLE,STANDARDIZED_TITLE,AGE_RANGE,COMPANY_PHONE,COMPANY_PHONE_EXT,EMAIL_ADDRESS,INDIVIDUAL_ID)
VALUES
(
v_array(j),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[2].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[3].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[4].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[5].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[6].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[7].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[8].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[9].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[10].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[11].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].attributes[12].value’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].email’),
apex_json.get_varchar2(p_path => ‘parties[‘|| i || ‘].id’)
);
end loop; –l_num_contacts
END IF;    –greater than 0

end loop; –v_array.count
commit;

Step 7 – Execute PL/SQL

 

Run the PL/SQL in Apex SQL Commands. Test various combinations. Test with duns numbers with less than 9 digits.

SP_LOAD_SOCIAL_DATA_CONTACTS(‘123456789′,’All Column Values’,’All Column Values’);

SP_LOAD_SOCIAL_DATA_CONTACTS(‘123456789′,’Administration’,’All Column Values’);

SP_LOAD_SOCIAL_DATA_CONTACTS(‘123456789′,’All Column Values’,’Manager’);

SP_LOAD_SOCIAL_DATA_CONTACTS(‘123456789′,’Administration’,’Manager’);

SP_LOAD_SOCIAL_DATA_CONTACTS(‘1234567′,’All Column Values’,’All Column Values’);

Step 8 – Review Results

 

Confirm data was inserted as expected.

SELECT * FROM SOCIAL_DATA_CONTACTS;

Part B – Trigger Results Real-Time

 

Oracle Social Data and Insight Cloud Service data will be retrieved by the following sequence of events.

 

a)    A BICS Consumer selects required input parameters via a Dashboard Prompt.

b)    Selected Dashboard Prompt values are passed to Request Variables.

(The Request Variable temporary changes the state of the Repository Variable for that Session.)

c)    Session Variables (NQ_SESSION) are read by the Modeler Expression using the VALUEOF function.

d)    EVALUATE is used to call a Database Function and pass the Session Variable values to the Database Function.

e)    The Database Function calls a Stored Procedure – passing parameters from the Function to the Stored Procedure

f)    The Stored Procedure uses apex_web_service to call the Rest API and retrieve data in JSON format as a clob.

g)    The clob is parsed and results are returned and inserted into a BICS database table.

h)    Results are displayed on a BICS Analysis Request, and presented to the BICS Consumer via a Dashboard.


Step 9 – Add Clear BI Server Cache Logic


For a text version of the PL/SQL snippets in step 9-11 click here.

This step is optional depending on how cache is refreshed / recycled.

Replace BICS URL, BICS User, BICS Pwd, and BICS Identity Domain.

Add cache code after insert commit (after all inserts / updates are complete).

Repeat testing undertaken in “Step 7 – Execute PL/SQL”.

DECLARE
l_bics_response_clob    CLOB;

–clear BICS BI Server Cache
apex_web_service.g_request_headers(1).name := ‘X-ID-TENANT-NAME’;
apex_web_service.g_request_headers(1).Value := ‘BICS Identity Domain‘;
l_bics_response_clob := apex_web_service.make_rest_request
(
p_url => ‘https://BICS_URL/bimodeler/api/v1/dbcache’,
p_http_method => ‘DELETE’,
p_username => ‘BICS UserName‘,
p_password => ‘BICS Pwd
);
–dbms_output.put_line(‘Status:’ || apex_web_service.g_status_code);


Step 10 – Create Function – to execute Stored Procedure

 

CREATE OR REPLACE FUNCTION LOAD_SOCIAL_DATA_CONTACTS
(
p_company_duns IN VARCHAR2,
p_department IN VARCHAR2,
p_management_level VARCHAR2
) RETURN INTEGER
IS PRAGMA AUTONOMOUS_TRANSACTION;

SP_LOAD_SOCIAL_DATA_CONTACTS(p_company_duns,p_department,p_management_level);
COMMIT;
RETURN 1;

Step 11 – Test Function – that executes Stored Procedure

 

SELECT LOAD_SOCIAL_DATA_CONTACTS(‘123456789′,’All Column Values’,’All Column Values’) FROM DUAL;

 

Step 12 – Create Dummy Table – to reference the EVALUATE function


For a text version of the PL/SQL snippet click here


1)    Create Table

CREATE TABLE DUMMY_REFRESH
(REFRESH_TEXT VARCHAR2(255));

2)    Insert descriptive text into table

INSERT INTO DUMMY_REFRESH (REFRESH_TEXT)
VALUES (‘Hit Refresh to Update Data’);

3)    Confirm insert was successful

SELECT * FROM DUMMY_REFRESH;

 

Step 13 – Create Repository Variables


Create a Repository Variable in the BICS Modeler tool for each parameter that needs to be passed to the function and stored procedure.

Snap12

Snap13

Snap14

 

Step 14 –  Create Expression in Data Modeler – that references EVALUATE function

 

Create the expression in the BICS Modeler tool using EVALUATE to call the function and pass necessary parameters to the function and stored procedure.

EVALUATE(‘LOAD_SOCIAL_DATA_CONTACTS(%1,%2, %3)’,VALUEOF(NQ_SESSION.”r_company_duns”),VALUEOF(NQ_SESSION.”r_department”),VALUEOF(NQ_SESSION.”r_management_level”))

 

Snap2

 

Step 15 – Create Analysis – that executes EVALUATE function


Create an Analysis and add both field from the DUMMY_REFRESH table. Hide both field so that nothing is returned.

 Snap4

Snap5

Snap6

Step 16 – Create Analysis – to display results

 
Add all or desired fields from SOCIAL_DATA_CONTACTS table.

 Snap16

Step 17 – Create Dashboard Prompt


For each Prompt set the corresponding Request Variable.

*** These must exactly match the names of the repository variables created in “Step 13 – Create Repository Variables” ***

Snap9

Snap10

Snap11

Snap15

For each prompt manually add the text for ‘All Columns Values’ and exclude NULL’s.

Snap19

Snap20

The Dashboard also contains a workaround to deal multiple Dun’s numbers. Currently VALUELISTOF is not available in BICS. Therefore, it is not possible to pass multiple values from a Prompt to a request / session variable; since, VALUEOF can only handle a single value.

A suggested workaround is to put the multi-section list into a single comma delimiter string – using LISTAGG. The single string can then be read by VALUEOF and logic in the stored procedure can read through the array.

CAST(EVALUATE_AGGR(‘LISTAGG(%1,%2) WITHIN GROUP (ORDER BY %1 DESC)’,”DUNS_NUMBERS”.”COMPANY_DUNS_NUMBER”,’,’) AS VARCHAR(500))

 
Step 18 – Create Dashboard


There are many ways to design the Dashboard for the BICS Consumer. One suggestion is below:

The Dashboard is processed in seven clicks.

1)    Select Duns Number(s).

2)    Select Confirm Duns Number (only required for multi-select workaround described in Step 17).

3)    Select Department or run for ‘All Column Values’.

4)    Select Management Level or run for ‘All Column Values’.

5)    Click Apply. *** This is a very important step as Request Variables are only read once Apply is hit ***

6)    Click Refresh – to kick off Refresh Analysis Request (built in Step 15).

7)    Click Get Contact Details to display Contact Analysis Request (built in Step 16).

Snap7

Ensure the Refresh Report Link is made available on the Refresh Analysis Request – to allow the BICS Consumer to override cache.

Snap17

Optional: Make use of Link – Within the Dashboard on Contact Analysis Request to create “Get Contact Details” link.

Snap18

Further Reading


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

Click here for the Application Express API Reference Guide – APEX_JSON Package.

Click here for the REST APIs for Oracle Social Data and Insight Cloud Service guide.

Click here for more A-Team BICS Blogs.

Summary


This article provided a set of examples that leverage the APEX_WEB_SERVICE_API to integrate Oracle Social Data and Insight Cloud Service with Oracle Business Intelligence Cloud Service (BICS) using the Connect REST API web services.

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

Similarly, the Apex MAKE_REST_REQUEST and APEX_JSON examples could be easily modified to integrate BICS or standalone Oracle Apex with any other REST web service that is accessed via a URL and returns JSON data.

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

Key topics covered in this article include: Oracle Business Intelligence Cloud Service (BICS), Oracle Social Data and Insight Cloud Service, Oracle Apex API, APEX_JSON, apex_web_service.make_rest_request, PL/SQL, BICS Variables (Request, Repository, Session), BICS BI Server Cache, BICS Functions (EVALUATE, VALUEOF, LISTAGG), and Cloud to Cloud integration.

Add Your Comment