Custom Writeback in OBIEE using JS and JSP

First we will look into how to write into a database table using only JS and also the limitations.

Then how to overcome those limitations.

Writeback using JS:

Create a JS function to write into a database table as below:
eg: In this example I am using a Oracle 11g R2 database

function <function_name>(<parameter1,parameter2,...>)
{
var conObj = new ActiveXObject('ADODB.Connection');
var connectionString = "Provider=OraOLEDB.Oracle;Data Source=<tns-entry>;User Id=<schema name>;Password=<schema password>;";
conObj.Open(connectionString);
var rs = new ActiveXObject("ADODB.Recordset");
var sql = "INSERT INTO DUMMY_TABLE(X1,X2,...) VALUES ("+parameter1+","+parameter2+",...)";
rs.Open(sql,conObj);
var sq = "commit";
rs.Open(sq,conObj);
//rs.close;
conObj.close;
location.reload();
//close();
}

Append the above code after modification in common.js file located at the below location:

<middleware_home>/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/b_mozilla

Now calling this function from analytics providing input parameters to the function to be inserted:

Use narrative view to call this function in a button.

Eg: I have used a button function to trigger the JS function

<button onclick="<function_name>(<parameter1,parameter2,...>)">Set Responsibility</button>

You can use presentation variables or populated values in a report as a single line and pass these values as input parameters to the narrative view to pass it to JS function.

Check Contains HTML Markup option in the narrative view and save.

Now when you click this button then the parameters passed from the narrative view to the JS writes back to the database.

Now the problem here is the JS gives pop-ups as we are using ADODB connection (making it compatible with only IE)

To overcome this we will now use JSP as well to suppress the popups and allow this writeback to be generic across browsers.

To start with this we would need to create a JSP file with proper folder structure as shown below:

Use the below JSP code to write into the database table and return to the dashboard page:

<html>

<body>

<%@ page language="java" import="java.sql.*" %>

Connection conn = null;

String PARAMETER3 = request.getParameter("parameter3");

String PARAMETER4 = request.getParameter("parameter4");

PreparedStatement pstatement = null;

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection("jdbc:oracle:thin:@<database_hostname>:<port>:<sid>", "<schema>", "<password>");

pstatement = conn.prepareStatement("INSERT INTO DUMMY_TABLE(COLUMN1,COLUMN2) VALUES ("+PARAMETER3+","+PARAMETER4+")");

pstatement.executeUpdate();

window.location.href = 'https://localhost:11557/analytics';

</script>

</body>

</html>

The WEB-INF folder should contain the same copy of .jsp file

The lib folder should contain the library file corresponding to the java version.
In this scenario it is ojdbc6.jar

Now select the 4 elements show in earlier screenshots and zip them and create a war file to deploy it on to weblogic.

Deploy the war file on all managed servers.

How deploy the war file follow the below link:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/wls/12c/03-DeployApps/deployapps.htm

Once the web application/war file is deployed in weblogic console, start it.

Now click on the deployed application and navigate to Testing tab and note down Testing Point URL.

Now we need to modify the JS function to call this JSP web application as below:

function <function_name>(<parameter1,parameter2,...>)

{

var jspcall = "http://localhost:11553/dummy/Dummy.jsp&parameter3="+ parameter1 +"&parameter4="+ parameter2;

window.location.href = jspcall;

}

Here the text in green color (http://localhost:11553/dummy/) is to be taken from Testing Point under Testing tab of the web application deployed earlier under weblogic deployments and the orange one is the .jsp file name.

Now after all the changes bounce entire OBIEE.

Thank you Folks!

Please let me know in case of any doubts.

WLST scripts

Here I will be covering few most commonly used WLST topics as below: Creating application roles Deleting application roles Assigning users to the application roles Revoking users from the application roles Listing out users in an application role(s) Creating users In all these scripts I will be using a csv file as an input source (as it is more convenient). You may also prefer to user various input sources for the WLST scripts which are indeed python scripts, but you would need to have those relevant modules installed as well. csv module comes with the OS package. WLST scripts are python scripts (.py). All the scripts are preferred to be kept under the below location: <middleware_home>/oracle_common/common/bin 1. Creating application roles: Here the source is Resp.csv file; this file contains the list of Application roles to be created as below. Note: The source file in this scenario is placed at the same location of the file. We use th...

BI Architecting & Solutions

Search This Blog