Background

* This blog was last tested on OAC 105.8.0-133 (Version 5.8) + ADW 18c *

The article describes how to configure a link on a Oracle Analytics Cloud (OAC) Dashboard that allows a OAC consumer to execute a stored procedure from a OAC Dashboard.

The sames steps can be followed for Oracle Business Intelligence Enterprise Edition (OBIEE/OBI).

In the example a stored procedure inserts a record into a table located into an Oracle database.

Following the steps in this article it is possible to create custom write-back functionality using INSERT and UPDATE SQL statements that are triggered from the OAC Dashboard.

From the below Dashboard, the OAC consumer clicks the refresh link, that calls a database function (using EVALUATE), which in turn executes the stored procedure. The stored procedure contains the logic to update / refresh the given table. The example provided inserts a single row into a one column table. However, this solution can be easily modified for much more complex use cases.

The article is divided into seven steps:

Step One: Create Table (to store data)

Step Two: Create Stored Procedure (to load data)

Step Three: Create Function (to execute Stored Procedure)

Step Four: Create Dummy Table (to reference the EVALUATE function)

Step Five: Create Expression in Developer Client Tool for OAC (that references EVALUATE function)

Step Six: Create Analysis (that executes EVALUATE function)

Step Seven: Create Dashboard (to display Analysis)

Extra!

Step Eight: How to Create an Annotations Write-Back Dashboard

 Create Table (to store data)

For a text version of SQL Scripts in Steps One, Two, and Three click here

Run SQL statements in SQLDeveloper or SQL query tool of choice.

1.1 - Create Table

CREATE TABLE STORE_DATA
(DATA_FIELD TIMESTAMP);

 Create Stored Procedure (to load data)

2.1 - Create Stored Procedure

CREATE OR REPLACE PROCEDURE LOAD_DATA AS
BEGIN
INSERT INTO STORE_DATA (DATA_FIELD)
VALUES(SYSDATE);
END;

2.1 - Test that executing the stored procedure inserts into the table without errors.

BEGIN
LOAD_DATA();
END;

2.3 - Confirm that data loaded as expected.

SELECT * FROM STORE_DATA;

 Create Function (to execute Stored Procedure)

3.1 - The Functions main purpose is to execute the stored procedure.

PRAGMA AUTONOMOUS_TRANSACTION, and return value are required.

CREATE OR REPLACE FUNCTION FUNC_LOAD_DATA (
p_input_value VARCHAR2
) RETURN INTEGER
IS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
LOAD_DATA();
COMMIT;
RETURN 1;
END;

3.2 - Confirm that the function can be ran successfully.

SELECT FUNC_LOAD_DATA('Hello')
FROM DUAL;

3.3 - Confirm that each time the function is referenced the table is updated.

SELECT * FROM STORE_DATA;

 Create Dummy Table (to reference EVALUATE function)

*** For Cloud environments EVALUATE support is activated from: Console -> Service Administration -> Configure System Settings ***

For a text version of SQL Scripts in Step Four click here

4.1 - Create Table

CREATE TABLE DUMMY_REFRESH
(REFRESH_TEXT VARCHAR2(255));

4.2 - Insert descriptive text into table

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

4.3 - Confirm insert was successful

SELECT * FROM DUMMY_REFRESH;

 Create Expression in Developer Client Tool for OAC (that references EVALUATE function)

5.1 - Download and install the Developer Client Tool for OAC from here.

Click Open in the Cloud to connect to the Cloud RPD.

5.2 - Add the DUMMY_REFRESH table to the RPD Business Layer.

5.3 - In the Business Model join the DUMMY_REFRESH table to a Fact Table.

5.4 - In the Business Model right click on DUMMY_REFRESH -> New Object -> Logical Column

5.5 - Name the Logical Column Run_Func

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

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

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

5.7 - Add both REFRESH_TEXT and Run_Fun to the Presentation Layer

5.8 - Publish the RPD to the Cloud

 Create Analysis (that executes EVALUATE function)

6.1 - Create a new Analysis

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

6.3 - Add both columns

6.4 - From the Results tab set the Run_Func column to be hidden

6.5 - Remove the Title

6.6 - Go to the Column Properties of DUMMY_REFRESH and "Click Custom Headings".

Type a space for the heading name.

6.7 - Save the  Analysis. It should look something like below.

 Step Seven: Create Dashboard (to display Analysis)

7.1 - Add the Analysis to a Dashboard

7.2 - Set custom Report Links

7.3 - Customize -> Refresh

7.4 - The Analysis should look something like the below on the Dashboard

7.5 - To run the function that updates the data - Hit Refresh.

7.6 - Confirm data was updated in the STORE_DATA table.

SELECT * FROM STORE_DATA;

7.7 - For certain use cases it may also be beneficial to display the table that is being updated in a separate Analysis

on the same dashboard. In this example the STORE_DATA results. This allows the OAC consumer to view the new

results immediately after they are refreshed.

Extra!!!

 How to Create an Annotations Write-Back Dashboard

This newly added section explains how to extend the previous example to create an Annotations Write-Back Dashboard.

These steps assume that the previous SEVEN steps of the main article have already been completed.

The end result is a Dashboard that allows the consumer to select a row displayed on a table (via a Dashboard Prompt) and add comments for it.

The comments are saved back to the database and displayed real-time on the Dashboard.

Much of the logic is similar to that covered in the previous SEVEN steps. The primary additional concept covered is the use of Dashboard Prompts to set request variables that are selected / entered by the consumer then passed to the database function and stored procedure.

Write-Back Annotations Dashboard

For a text version of all commands in the extra section click here

8.1 - Add a Comments field to STORE_DATE

ALTER TABLE
STORE_DATA
ADD COMMENTS VARCHAR2(200);

8.2 - Create the UPDATE procedure

CREATE OR REPLACE PROCEDURE UPDATE_DATA
(p_comments VARCHAR2,
p_date TIMESTAMP
) IS
BEGIN
UPDATE STORE_DATA
SET COMMENTS = p_comments
WHERE DATA_FIELD = p_date;
END;

8.3 - Test the UPDATE procedure

BEGIN
UPDATE_DATA('ABC','11-SEP-15 10.43.27.000000 PM');
END;

8.4 - Create the UPDATE function

CREATE OR REPLACE FUNCTION FUNC_UPDATE_DATA
(
p_comments IN VARCHAR2,
p_date IN TIMESTAMP
) RETURN INTEGER
IS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE_DATA(p_comments,p_date);
COMMIT;
RETURN 1;
END;

8.5 - Test the UPDATE function

SELECT FUNC_UPDATE_DATA('XYZ','11-SEP-15 10.43.27.000000 PM')
FROM DUAL;

8.6 - Add the STORE_DATA table to the Physical Layer of the RPD

8.7 - In the Business Model join the STORE_DATA table to a Fact Table

8.8 - Create two Session Initialization Blocks with corresponding Session Variables: default_string/r_comments & default_date/r_date

8.9 - From the RPD Business Model, add a new logical column, "derived from an existing column using an expression", using the below expression:

EVALUATE('FUNC_UPDATE_DATA(%1,%2)' as CHAR,VALUEOF(NQ_SESSION."r_comments"),VALUEOF(NQ_SESSION."r_date"))

8.10 - Click, Done -> Done -> Publish the Model

8.11 - Add all three columns: "COMMENTS", "DATA_FIELD", and "update_func" to the Analysis

8.12 - Hide "update_func". Output should look as follows:

8.13 - Create two Dashboard Prompts called "Date" and "Enter Comments".

8.14 - Date Prompt: Based on "STORE_DATA"."DATA_FIELD". Set "User Input" to "Choice List". Set "Request Variable to r_date.

8.15 - Enter Comments Prompt: Based on "DUMMY_REFRESH"."Run_Func". Set "User Input" to "Text Field". Set "Request Variable" to r_comments.

8.16 - Place the Dashboard Prompt and the Analysis on a Dashboard

8.17 - Add "Report Links" -> Refresh (and other required links)

8.18 -  Run the Dashboard.

a)    Select a Date

b)    Type in comments (annotations)

c)    Click Apply

d)    Click Refresh

 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 configure a link on an Oracle Analytics Cloud (OAC) Dashboard that allows a OAC consumer to execute a stored procedure from OAC Dashboard.

The key components to the solution are the use of the EVALUATE function and referencing PRAGMA AUTONOMOUS_TRANSACTION within the function.

In this example the stored procedure is executed by clicking the refresh link. An alternative approach would be to invoke the stored procedure through a Dashboard Prompt.

The example shown can be easily modified to:

1) Pass values from the Dashboard to the Stored Procedure.

2) Return output values such as "number of records inserted" or "number of records failed".

When using this solution to load / refresh data in OAC, it is important to also remember to add logic to clear the cache.

Note: An extra section was added post publishing explaining how to build an Annotations Write-Back Dashboard in OAC.