Using the Oracle Business Intelligence Cloud Service (BICS) REST API to Clear Cached Data

Introduction

 

This article describes how to use the Oracle Business Intelligence Cloud Service (BICS) REST API dbcache method to clear the BI Server cached data. Prior to dbcache being added to the BICS REST API, the cache could be cleared by unlocking the Model (in Modeler) and running “Clear All Cached Data”. For DataSync version 1.2 and higher, the BI Server cache is automatically purged at the end of the data load. For all other data loading methods, the BI Server cache must be cleared once the data load is complete. The manual method to unlock and clear the cache through the Modeler is still available and valid. However, with most things ETL/BI related – avoiding manual intervention is generally preferred.

The article has been divided up into three steps:

Step One:    Run dbcache in Curl

Step Two:    Call dbcache from Apex Web Services

Step Three: Trigger BICS REST API from Analysis – Return status to Dashboard

Although the title to this article is very cache specific; concepts covered can be applied to many other BICS REST API / Apex Web Services / Stored Procedure use cases.

For example this article could be used to assist with:

1)    Running any other BICS REST API commands in curl (or though other methods).

2)    Running any other BICS REST or non-BICS REST API commands through Apex Web Services.

3)    Triggering any other Stored Procedures from BICS.

4)    Returning values from any other Stored Procedure to an Analysis.

Each step of the article provides an individual solution. It is not necessary to implement all three together. In some use cases only one step may be required.

The final Dashboard created in this article is displayed below. The BICS consumer clicks the refresh link, that calls a database function (using EVALUATE), which in turn executes a stored procedure. The stored procedure uses apex_web_service.make_rest_request to run the BICS REST API dbcache method that clears the BI Server cached data. The dbcache method returns a status of 200 for successful completion. All other status indicate an error of some sort. For example status 403 indicates an authentication error. The status is returned to the database function / stored procedure and displayed on an Analysis presented on a Dashboard.

Snap1

Main Article

Updated August 28th, 2017: If  you run this procedure in a DBCS instance, you may receive an ORA-29024: Certificate validation failure error. This is because a DBCS wallet probably does not have the same set of default Web Service certificates as a BICS DB Schema Service database does. This blog describes how to add Web Service certificates to a DBCS wallet: http://www.ateam-oracle.com/adding-web-service-trusted-certificates-to-a-wallet-in-oracle-database-cloud-service/

 

Step One: Run dbcache in Curl

 

Download curl from here.

Replace: User Name, Password, Tenant Name, Analytics URL.

***  Analytics URL … not Apex URL ***

curl -i -k -u user:password -X DELETE -H “X-ID-TENANT-NAME: oracletrial123” https://bitrial123-oracletrial123.analytics.us.oraclecloud.com/bimodeler/api/v1/dbcache

First line should return:

HTTP/1.1 200 OK

Step Two: Call dbcache from Apex Web Services

 

It is recommended to review Executing a Stored Procedure from Oracle Business Intelligence Cloud Service (BICS) prior to reading this section.

 

For a text version of SQL Scripts click here

All SQL Statements have been run through Apex -> SQL Workshop -> SQL Commands

Snap4

Snap5

1)    Create Stored Procedure.

Replace: User Name, Password, Tenant Name, Analytics URL.

CREATE OR REPLACE PROCEDURE CLEAR_CACHE(p_status OUT VARCHAR2) IS
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := ‘https://bitrial123-oracletrial123.analytics.us.oraclecloud.com/bimodeler/api/v1/dbcache’;
BEGIN
apex_web_service.g_request_headers(1).name := ‘X-ID-TENANT-NAME’;
apex_web_service.g_request_headers(1).Value := ‘usoracletrial123‘;
l_ws_response_clob :=
apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_http_method => ‘DELETE’,
p_username => ‘UserName‘,
p_password => ‘Pwd
);
p_status := apex_web_service.g_status_code;
dbms_output.put_line(‘Status:’ || dbms_lob.substr(p_status));
END;

2)    Test Stored Procedure.

DECLARE
p_status VARCHAR2(500);
BEGIN
CLEAR_CACHE(p_status);
END;

3)    Confirm Stored Procedure runs successfully.

Status:200

4)    Create Function.

CREATE OR REPLACE FUNCTION FUNC_CLEAR_CACHE (
p_input_value VARCHAR2
) RETURN VARCHAR2
IS PRAGMA AUTONOMOUS_TRANSACTION;
p_status VARCHAR2(500);
BEGIN
CLEAR_CACHE(p_status);
COMMIT;
RETURN p_status;
END;

5)    Test Function.

SELECT FUNC_CLEAR_CACHE(‘Hello’)
FROM DUAL;

Snap3

Step Three: Trigger BICS REST API from Analysis – Return status to Dashboard.

 

It is recommended to review Executing a Stored Procedure from Oracle Business Intelligence Cloud Service (BICS) prior to reading this section.

1)    In the database create a dummy table that will be used to define the function.

The table should have one column and one row.

Populate table with descriptive text.

CREATE TABLE DUMMY_REFRESH
(REFRESH_TEXT VARCHAR2(255));

INSERT INTO DUMMY_REFRESH (REFRESH_TEXT)
VALUES (‘Dummy Table Used to Clear Cache’);

2)    Go to Modeler -> Lock to Edit the Data Model

3)    Add the dummy table as a Dimension table.

4)    Join the dummy table to a Fact Table. This does not have to be a true join. However, the data types must match.

5)    Click on the dummy table in the Dimension Table list

6)    Click on Add Column

7)    In the Expression Builder type:

EVALUATE(‘FUNC_CLEAR_CACHE(%1)’,’Hello’)

Snap4

8)    In Name and Description type: Run_Func

9)    Validate the Expression

10)  Click -> Done -> Done -> Publish the Model

11)  Create a new Analysis

12)  It may be necessary to Refresh -> Reload Server Metadata in order to see the new expression created in the previous step

Snap17

13)  Add both columns

Snap18

14)  Edit the Run_Func Column to contain the following CASE statement.

Rename the column to “Cache Status”.

CASE WHEN “DUMMY_REFRESH”.”Run_Func” = ‘200’ THEN ‘Successful’ ELSE ‘Failed with Status: ‘ ||  “DUMMY_REFRESH”.”Run_Func” END

Snap6

15)  Hide the descriptive text field.

16)  Test the Analysis runs successfully.

Snap7

 

17)  Add the Analysis to a Dashboard

18)  Set custom Report Links

Snap23

 

 

19)  Customize -> Refresh

Snap24

20)  Final Dashboard

Snap1

 

21)  Experiment by changing the username or password in the Stored Procedure to be incorrect. Confirm it fails and the status is returned to the Dashboard.

Snap2

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 Application Express API Reference Guide –  MAKE_REST_REQUEST Function

Click here for the REST API Reference for Oracle Business Intelligence Cloud Service

Click here for more information on the EVALUATE function. This link is for the “Logical SQL Reference” – for “Oracle® Fusion Middleware Metadata Repository Builder’s Guide for Oracle Business Intelligence Enterprise Edition”. Therefore, not all commands in this guide are applicable to BICS. The relevant section on the EVALUATE function has been provided below. For BICS environments EVALUATE_SUPPORT_LEVEL is enabled by default.

Snap26

Summary

This article described how to use the Oracle Business Intelligence Cloud Service (BICS) REST API dbcache method to clear the BI Server cached data.

Upon completion of the article the reader would have learned how to:

1)    Use the BICS REST API dbcache method to clear the BI Server Cache.

2)    Call the BICS REST API through the Apex API using the MAKE_REST_REQUEST function.

3)    Use EVALUATE in the Data Modeler to execute a database function.

4)    Trigger a database function from a BICS Analysis – displaying return values on a Dashboard.

Add Your Comment