Saturday, January 12, 2013

How-to: OBIEE 11g Javascript Integration using Action Framework (Browser Script)

3:20 PM

Share it Please

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

10 comments:

  1. Hi John
    I have requirement to update essbase datase from OBIEE 11.1.1.7. So in order to achive that I have refered the sample app essbase integration writeback report.So implemeneted the browser script as mentioned.But I am getting error while I execute the action.
    here is the js. the output when i execute is "FAiled"
    USERSCRIPT.execute_lsql = function(aParams){
    var strLSQL ="";
    for( args in aParams )
    {
    var argName = args;
    var argValue = aParams[argName];

    if(argName=="lsql"){
    strLSQL = aParams[argName];
    } else {
    argName = "@{" + argName + "}";
    strLSQL = strLSQL.replace(argName,argValue);
    }
    }
    /*alert(strLSQL);*/
    if(strLSQL.length<2) {
    alert("Incorrect LSQL: '"+ strLSQL + "'");
    return;
    }

    xmlhttp=getXMLHTTPObject();
    if(!xmlhttp) {
    alert("Failed: Unable to cannot create HTTP Object, Check user script");
    return;
    }
    xmlhttp.open("GET", "saw.dll?IssueRawSQL&_scid=" + obips_scid + "&SQL=" + strLSQL,true);
    xmlhttp.onreadystatechange=function() {
    if (xmlhttp.readyState==4) {
    if(xmlhttp.responseText.indexOf("class=\"ResultsTable\"")>0)
    alert("Invoked Successly");
    else alert("Failed");
    }
    }
    xmlhttp.send(null)
    };

    USERSCRIPT.execute_lsql.publish =
    {
    parameters :
    [
    new USERSCRIPT.parameter( 'lsql', 'Enter Logical SQL', '' ),
    new USERSCRIPT.parameter( 'p1', 'Enter value for Param 1', '' ),
    new USERSCRIPT.parameter( 'p2', 'Enter value for Param 2', '' ),
    new USERSCRIPT.parameter( 'p3', 'Enter value for Param 3', '' )
    ]
    };
    please let me know wht could be wrong.As I am layman in javascript.

    ReplyDelete
  2. As you mention I have altered userscript.js
    under
    /data1/OBIEE/Middleware_7/Oracle_BI1/bifoundation/web/appv2/res/b_mozilla/actions



    USERSCRIPT.customPortalPageNav = function(arg_array){
    var str="parent.PortalPageNav(1";
    for(args in arg_array){
    var arg_name=args;
    var value=arg_array[arg_name];
    str+=",'"+value+"'";
    }
    str+=")";
    eval(str);
    };
    USERSCRIPT.customPortalPageNav.publish={parameters:[
    new USERSCRIPT.parameter("1","Dashboard","/shared/FOLDERNAME/_portal/DASHBOARDNAME"),
    new USERSCRIPT.parameter("2","Page","Insert your Page"),
    new USERSCRIPT.parameter("3","Table","Insert Table"),
    new USERSCRIPT.parameter("4","Column","Insert Column"),
    new USERSCRIPT.parameter("5","Value","Insert Value")]};

    but I can not see this fucntion on analysis, will you please let me know what setting I need to do on browser to see.

    ReplyDelete
  3. As you mention I have altered userscript.js
    under
    /data1/OBIEE/Middleware_7/Oracle_BI1/bifoundation/web/appv2/res/b_mozilla/actions



    USERSCRIPT.customPortalPageNav = function(arg_array){
    var str="parent.PortalPageNav(1";
    for(args in arg_array){
    var arg_name=args;
    var value=arg_array[arg_name];
    str+=",'"+value+"'";
    }
    str+=")";
    eval(str);
    };
    USERSCRIPT.customPortalPageNav.publish={parameters:[
    new USERSCRIPT.parameter("1","Dashboard","/shared/FOLDERNAME/_portal/DASHBOARDNAME"),
    new USERSCRIPT.parameter("2","Page","Insert your Page"),
    new USERSCRIPT.parameter("3","Table","Insert Table"),
    new USERSCRIPT.parameter("4","Column","Insert Column"),
    new USERSCRIPT.parameter("5","Value","Insert Value")]};

    but I can not see this fucntion on analysis, will you please let me know what setting I need to do on browser to see.

    ReplyDelete
  4. Hi

    We are doing a POC between HP Voltage and OBIEE.
    We have transactional data which is being stored in datawarehouse and accessed via OBIEE reports.
    Now the card numbers from the transactional data is going to be stored in an encrypted format. Voltage is being used by ETL tool to encrypt data and store in datawarehouse.
    Now we need to use voltage decryption logic in OBIEE.
    We have been provided with sample voltage decryption java code.

    What would be the best way to approach this situation ? How can I decrypt a column values and present in the report ?

    ReplyDelete
  5. Credit rating score reporting isn't always something however oracle fusion procurement way wherein viable avail with alternatives to provide the client or the economic enterprise agency with all of the credit score and debt facts
    Oracle Fusion procurement Online Training
    Oracle Fusion cloud procurement Online Training

    ReplyDelete
  6. Hi John,

    I have a requirement where I need to upload .dwg file, and it has to be viewed in the dashboard.

    How can I achieve it.

    please guide me.

    Regards
    Akshay Bhan

    ReplyDelete