One of the powerful features of Oracle's Business Intelligence 11g platform is a concept called 'Action Framework' or 'Actionable Intelligence'. It's useful because for the first time in OBIEE you can integrate external applications, functions or code and invoke it using the front end user interface (Answers).
Although I have seen 'javascript or jquery integration' in OBIEE 10g, the implementation was always 'hacked' together, and of course, was
never supported or endorsed by Oracle. In this guide we'll show how you can take
any javascript or jquery function and by using Oracle's supported 'external systems framework', integrate it seamlessly with OBIEE 11g.
Consider the scenario where your source data warehouse or ERP stores employee numbers in an encoded format of base 64. For example, employee number '123456789' is 'MTIzNDU2Nzg5' in base 64. The requirement you have is to display
the decoded employee number in a report. How do we implement this requirement?
Luckily, base 64 encode/decode functions are easily accessible via the internet, so we'll use the code from
Stackoverflow.com
The encode function will ultimately end up in the UserScripts.js file located at:
- <middleware home>/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1.2.0/<installation dependent folder>/war/res/b_mozilla/actions/UserScripts.js
But we can't just copy & paste, so let's get started.
Step 1: Understand how OBIEE 11g uses action framework to invoke custom javascript functions
OBIEE 11g stores custom javascript functions in Userscripts.js. In order to integrate a javascript function into userscript.js your function must have:
- a USERSCRIPT.publish function which is required to pass the parameters to the target javascript function
- a USERSCRIPT.parameter function out of the box function which is used by the Action Framework to define parameters in custom JavaScript functions for use when creating an action to Invoke a Browser Script. Each parameter object includes a name, a prompt value holding the text to be displayed against the parameter when creating an action, and a default value.
- a USERSCRIPT.encode function - the actual function we're going to implement
Step 2: Create USERSCRIPT.encode.publish function
As described above, the userscript.encode.publish function needs to take the parameters from the USERSCRIPT.parameter file and create a new encode object:
USERSCRIPT.encode.publish=
{
parameters:
[
new USERSCRIPT.parameter("employeenumber","Employee Number","")
]
}
Step 3: Create the actual encode functions
The encode function from stackoverflow is actually comprised of two functions: 1) the public method for encoding and 2) the private method used for UTF8 encoding
USERSCRIPT.encode function:
USERSCRIPT.encode=function(b)
{
var cz="";
for(args in b)
{ // this for function is needed to store the 3rd value in the array - the actual employee number
var d=args;
var a=b[d];
cz = a;
}
var output = "";
var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
var i = 0;
var _keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";
var input = USERSCRIPT.UTF8Encode(cz);
while (i < input.length)
{
chr1 = input.charCodeAt(i++);
chr2 = input.charCodeAt(i++);
chr3 = input.charCodeAt(i++);
enc1 = chr1 >> 2;
enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
enc4 = chr3 & 63;
if (isNaN(chr2)) {
enc3 = enc4 = 64;
} else if (isNaN(chr3)) {
enc4 = 64;
}
output = output +
_keyStr.charAt(enc1) + _keyStr.charAt(enc2) +
_keyStr.charAt(enc3) + _keyStr.charAt(enc4);
}
alert(output)
}
;
USERSCRIPT.UTF8ENCODE function
USERSCRIPT.UTF8Encode=function(b)
{
var str = b.replace(/\r\n/g,"\n");
var str = b;
var utftext = "";
for (var n = 0; n < str.length; n++) {
var c = str.charCodeAt(n);
if (c < 128)
{
utftext += String.fromCharCode(c);
} else if((c > 127) && (c < 2048)) {
utftext += String.fromCharCode((c >> 6) | 192);
utftext += String.fromCharCode((c & 63) | 128);
} else {
utftext += String.fromCharCode((c >> 12) | 224);
utftext += String.fromCharCode(((c >> 6) & 63) | 128);
utftext += String.fromCharCode((c & 63) | 128);
}
}
return utftext;
};
After this, make sure to restart Admin Service, Managed Server and OPMN prior to creating the Action in Answers
Step 4: Create the Action in Answers
In Answers, navigate to New -> Actionable Intelligence -> Action -> Invoke -> Invoke a Browser Script
1)
2)
Click browse and select the USERSCRIPT.encode function:
Since the USERSCRIPT.parameter function specified 3 parameters, we will need to populate the three fields as follows: Object Name, prompt value, and default value.
After saving the action, execute it and populate it with a number, or leave it as default 123456789.
And as expected, the encoded base 64 number 123456789 is - 'MTIzNDU2Nzg5'
This example only scratches the surface of what's possible with Action Framework and OBIEE 11g. Correctly implemented, you can invoke 3rd party applications or functions (*cough* ETL on demand *cough*), pass data to the ERP source system, integrate a data set with Google Maps, or all of the above.
In future guides we will explain the advanced functionality of Action Framework.
keywords: action framework, obiee action framework, obiee javascript, obiee actions, actionable intelligence
