* This blog was last tested on OAC 6.1 + ADW 18c *
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.
Download curl from here.
Replace: User Name, Password, Tenant Name, Analytics URL.
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
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
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_url VARCHAR2(500) := 'https://bitrial123-oracletrial123.analytics.us.oraclecloud.com/bimodeler/api/v1/dbcache';
apex_web_service.g_request_headers(1).name := 'X-ID-TENANT-NAME';
apex_web_service.g_request_headers(1).Value := 'usoracletrial123';
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));
2.2 - Test Stored Procedure.
2.3 - Confirm Stored Procedure runs successfully.
2.4 - Create Function.
CREATE OR REPLACE FUNCTION FUNC_CLEAR_CACHE (
) RETURN VARCHAR2
IS PRAGMA AUTONOMOUS_TRANSACTION;
2.5 - Test Function.
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
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
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.
Click here for more A-Team OAC Blogs
Click here for more A-Team BICS Blogs
Click here for more A-Team OBIEE Blogs
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.