* This blog was last tested on OAC 6.1 + ADW 18c *

 Background

This feature works in OAC 5.9 and higher. In OAC 5.9 an enhancement was made to add SAPurgeAllCache('ALL') to clear the cache on all bi server instances to support multi-node configurations. 

This article describes how to use the Oracle Analytics Cloud (OAC) REST API dbcache method to clear the BI Server cached data.

For more information on this topic see: How To Clear Cache On A Scheduled Basis In Oracle Analytics Cloud(OAC) and BI Cloud Service(BICS) When Using Thin Client Modeler(TCM) (Doc ID 2406210.1). The support doc also covers the syntax for OAC Gen2 (OAC Native). 

The article has been divided up into three steps:

Step One:    Run dbcache in Curl

Step Two:    Call dbcache from PL/SQL

Step Three: Trigger OAC 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 OAC REST API / Stored Procedure use cases.

For example this article could be used to assist with:

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

2)    Running any other OAC REST or non-OAC REST API commands through PL/SQL

3)    Triggering any other Stored Procedures from OAC.

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

 Run dbcache in Curl

Download curl from here.

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

Gen 1

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

Gen2

curl -i -k -u user:password -X DELETE -H "X-ID-TENANT-NAME: idcs-xxx" https://bitrial123-oracletrial123.analytics.us.oraclecloud.com/api/tcm/v1/dbcache

         On completion, the first line should return:

HTTP/1.1 200 OK

 Call dbcache from PL/SQL

For a text version of SQL Scripts click here

Run from SQLDeveloper or SQL client of choice:

2.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.2 - Test Stored Procedure.

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

2.3 - Confirm Stored Procedure runs successfully.

Status:200

2.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;

2.5 - Test Function.

SELECT FUNC_CLEAR_CACHE('Hello')
FROM DUAL;

 Trigger OAC REST API from Analysis - Return status to Dashboard.

Follow the steps in Executing a Stored Procedure from Oracle Analytics Cloud (OAC) to trigger the OAC REST API from Analysis.

3.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');

3.2 - Add the DUMMY_REFRESH table to the RPD Business Layer.
 In the Business Model join the DUMMY_REFRESH table to a Fact Table.

3.3 - In the Business Model right click on DUMMY_REFRESH -> New Object -> Logical Column
Name the Logical Column Run_Func

3.4 - Click on Column Source tab -> check "Derived from existing columns using an expression".

3.5 - Enter the below expression. For a text version of the expression (to copy and paste from) click here

EVALUATE('FUNC_CLEAR_CACHE(%1)','Hello')

3.6 - Add both REFRESH_TEXT and Run_Fun to the Presentation Layer

3.7 - Publish the RPD to the Cloud

3.8 - Create a new Analysis

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

3.10 - Add both columns

3.11 - 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

3.12 - Hide the descriptive text field.

3.13 - Test the Analysis runs successfully.

3.14 - Add the Analysis to a Dashboard

3.15 - Set custom Report Links

3.16 - Customize -> Refresh

3.17 - Final Dashboard

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

Manually Clearing Cache

 Want to Learn More?

Click here for more A-Team OAC Blogs

Click here for more A-Team BICS Blogs

Click here for more A-Team OBIEE Blogs

 Summary

This article described how to use the Oracle Analytics Cloud (OAC) 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 OAC REST API dbcache method to clear the BI Server Cache.

2)    Call the OAC 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 OAC Analysis - displaying return values on a Dashboard.