[OBIEE 11g] Javascript Integration Using Action Framework (Browser Script)

 

Javascript Integration using Action Framework (Browser Script)

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).

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

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, passing data to the ERP source system, integrate a data set with Google Maps, or all of the above.

[OBIEE 11g] Impersonate Or Act As Other Users

 

 

Impersonate or Act As Other Users in OBIEE 11g

In a typical Oracle Business Intelligence (OBIEE) 11g environment, potential defects or data discrepancies arise that will require the production support team to investigate or research the potential issue. A useful way to research the issue is by having the developer 'act as' the specific end user who is encountering the defect, usually by logging in as their account or an account with similar security rights.

For security reasons, the developer or support team is usually never given the end users login credentials so unless they re-create an account with the same privileges, they're unable to 'see what the end user sees'.

In OBIEE 11g Oracle outlines a detailed method for acting as another user through a 'proxy', outlined here .

This process requires pre-configuration and deployment of key tables to facilitate acting as another user, and is not set up 'out of the box'.

Although there is value in setting up the Act As / Proxy functionality (for example, a supervisor wants to delegate some of his work to his direct report), from a development and troubleshooting perspective, impersonation can be achieved with the HTTP header and and the following parameters:

• NQUser
• NQPassword
• Impersonate

For example:

http://hostname:9704/analytics/saw.dll?Logon&NQUser=Administrator&NQPassword=Administrator&Impersonate=usernametoimpersonate

Before you can use this feature, the appropriate privilege must be added to your Administrator role (usually BIAdministrator Application Role):

• oracle.bi.server.impersonateUser
• oracle.bi.server.queryUserPopulation

Step 1: Navigate to Application Policies in Fusion Middleware (:7001/em/)

Farm_BIFoundation_domain -> Business Intelligence -> coreapplication -> security -> Application Roles

Step 2: Add required roles to Administrator's Application Policies

In the obi_ strip, click the edit button for the Administration Application Policies and search for

• oracle.bi.server.impersonateUser
• oracle.bi.server.queryUserPopulation

No restart is required. You can now use:

 http://hostname:9704/analytics/saw.dll?Logon&NQUser=Administrator&NQPassword=Administrator&Impersonate=usernametoimpersonate

to act as other end users. You will inherit all application roles as if you were the actual user.

[TALEND*] Import An Excel File Into Ms Access

 

Import an excel file into ms access

Integrating Talend with Excel data files can be particularly challenging, in this we are going to walk you through the basic steps required to get started on build a job to load a MS Excel file into an Access database.

You can download the example excel file used in this tutorial from here:

Excel example file

Lets assume we are trying to load data from an excel file that contains the following fields:

• Salesman Name
• Salesman Id
• Account Id
• Number of Units

The file would look something like this:

We want to load this file into a MS Access database table with a schema as follows:

Our Talend job will need to load only the fields as required in the database table, here are the steps we will take to build our Talend Open Studio solution:

Step 1:  Open Talend

Open Talend and create or open an existing project

Step 2: Create a new job

• Right click on Job Designs in the Repository window and select “Create job”
• Name the job “excel_load_example”

Step 3: Create a Ms Excel file repository element

Now we need to create a repository item for our excel file. To do this click the arrow next to “Metadata”  in the Repository window and right click on “File Excel” and select “Create file Excel”.

Enter a name for your example file schema and click next:

Select your example file that we saw earlier (or use your own) by clicking the “Browse” button.

Then select which work sheets in the Excel file contain the data we want to use for our schema.

Click Next

On the next screen select the checkbox for “Set heading row as column names”, then click “Refresh Preview”. Click Next.

Talend has now generated an estimated schema, review this schema and make any changes as you would like, then click Finish.

Step 4: Create an Access Database Table Repository Element

In this next step we are going to create a repository element for the Access database table schema so that we can use this in our job design.

Right click on “DB Connections” under “Metadata” in the Repository window and select “Create connection”

Name your database then click next:

Select DB Type as “Access” and DB Version as “Access 2007″ or “Access 2003″ if you are using that version.

Click on the “Browse” button and navigate to your Access database file.

Once selected then click on the “Check” button to make sure that Talend is able to connect to your DB.

If the connection works then click “Finish”

Now the DB Connection should appear under “Repository” -> “Metadata” -> “Db Connections” in the panel on the left

Right click on the DB name in this panel and and select “Retrieve schema”

On the pop up screen, click “Next”

On the following screen, select the “sales” table from our database and click “Next”

The final window will display the schema of your database table, which can now be used in the repository for future job design. Review the schema and make any changes if needed, then click “Finish”.

Step 5:  Design your job

In this step we are are going to design our job to connect our MS Excel file to our MS Access DB.

Open the job we created in Step 2 by double clicking the name of the job under Job Designs in the Repository window.

In the palette window on the right hand side type “fileinputexcel” into the search box, then drag and drop the component “tFileInputExcel” into the job window in the center of the screen.

Select the tFileInputExcel Component in the job window and then select the “Component” tab near the bottom middle of the screen.

Click the drop down box next to “Property type” and select “Repository”

Then click the button with three dots that appears and select the Excel file from the repository that we created in Step 3.

If the file is in Excel 2007 format then click the check box “Read excel 2007 file format (xlsx)”

Select a tMap component from the palette on the right hand side of the screen and drag it into the job design window

Right click on the tFileInputExcel component, select Row->Main and connect a row to the tMap component.

Select a tAccessOutput component from the Palette and drag it over to the job design window.

Click on the tAccessOutput component to select it, then navigate to the Component tab in the lower middle of the screen

Under Property Type, select Repository and then select the access database from the repository that we created in Step 4

Under Schema, select Repository and then select the “sales” table from our database in the repository

Next right click the tMap component, Select “Row” then “New Output”.

Connect this new output to the tAccessOutput component

Name the output row- “output1″

A popup window will appear asking you: “Do you want to get the schema of the target component” – click “Yes”.

Now your tMap is connected to the tAccessOutput component,  double click the tMap component to open up the tMap editor.

Click and drag the data fields from the “row1″ panel to the left of the screen to the corresponding “output1″ data fields. This tMap editor enables you to map to fields from the Excel input to the correct output fields in Access.

Click “Apply” then “OK” to save the changes and return to the Job Design window

Now your job design is complete and we just need to run the job to load the file into Access

 Step 6: Run your Job

In the group of tabs in the lower middle of the screen, select the “Run” tab

 

Under Execution, click the “Run” button

Your job will load and then run and you will see how many rows were processed in the job design window.

Now loaded your data into your Access DB table.