How-to: Data Visualization with External Javascript Libraries (D3 )

One of the great features of Oracle's Business Intellgience 11g foundation is the ability to integrate external applications via an API call or through the use of javascript libraries. In a previous article I discussed how to utilize javascript functions using OBIEE 11g's native UserScripts.js. Today we're going to expand on this functionality by integrating third party data visualization scripts. One popular javascript library used for data manipulation is 'Data-Driven Documents' . This open source scripting library gives users the ability to manipulate data using methods not available in OBIEE 11g.  Kevin McGinley first wrote about this in 2012 and the guys over at Rittman Mead recently posted an overview of D3 / OBIEE integration.  Below we're going to cover all the steps required to implement a D3 visualization technique.

Before we get started, you can view all of the D3 visualization methods at their github. In the example below we're going to use airline data to and D3's Calendar View to visualize average flight delays. You will need OBIEE 11.1.1.6.2 or higher (this example uses OBIEE 11.1.1.7.0) and IE 9+.

 

Step 0: Create an Answers Report

This report should contain a year dimension, a date dimension and an aggregate fact column. In the airline example I've selected 'Date', 'Year' and 'Average Departure Delay'. Take note of the column order as you will have to reference the column number in a narrative.

Step 1: Download the D3 Javascript Library from github

This is going to download a 'd3-master.zip' file that contains all of the javascript libraries needed for integration. You will unzip all of these files into OBIEE 11g's analytics ear deployment under Weblogic's Domain Home  located at :

 user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\b_mozilla\common

Step 2:  Create css file for Calendar Formatting

The Calendar view's javascript code is basically one script, with one function and one css file. These 'chunks of code' are all stored in the index.html using the example located on github, but in order for this view to play nice with OBIEE 11g, we're going to need to dissect components of the code into isolated narratives and css files. The first step is to take the css code:

#chart {

  font: 10px sans-serif;

  shape-rendering: crispEdges;

}

.day {

  fill: #fff;

  stroke: #ccc;

}

.month {

  fill: none;

  stroke: #000;

  stroke-width: 2px;

}

and save it to its own css file (calendar.css) located at:

user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\b_mozilla\common\d3\examples\calendar\calendar.css (you will need to create the directory as this doesn't exist)

Step 3: Create an Answers Narrative to Execute the Javascript Library

Now that we've laid the groundwork for calling the D3 library, the next step is to integrate the Calendar View code into an Answers narrative.

First create the script headers and link type to call the javascript library. This code will be stored in the pre-fix of the narrative:

<script type="text/javascript" src="/analytics/res/b_mozilla/common/d3/d3.js"></script>

<link type="text/css" rel="stylesheet" href="/analytics/res/b_mozilla/common/d3/lib/colorbrewer/colorbrewer.css"/>

<link type="text/css" rel="stylesheet" href="/analytics/res/b_mozilla/common/d3/examples/calendar/calendar.css"/>

Next we're going to take the calendar view code and copy the entire code block from the start of the width variable delcaration to the end of the call to the selectAll function. Your code should look similar to:

<script type="text/javascript" src="/analytics/res/b_mozilla/common/d3/d3.js"></script>

    <link type="text/css" rel="stylesheet" href="/analytics/res/b_mozilla/common/d3/lib/colorbrewer/colorbrewer.css"/>

    <link type="text/css" rel="stylesheet" href="/analytics/res/b_mozilla/common/d3/examples/calendar/calendar.css"/>

    <div id="my_chart"></div>

    <script type="text/javascript">

var margin = {top: 19, right: 20, bottom: 20, left: 19},

    width = 720- margin.right - margin.left, // width

    height = 136 - margin.top - margin.bottom, // height

    cellSize = 12; // cell size

var day = d3.time.format("%w"),

    week = d3.time.format("%U"),

    percent = d3.format(".1%"),

    format = d3.time.format("%Y-%m-%d");

var color = d3.scale.quantize()

    .domain([5,30])

    .range(d3.range(9));

var svg = d3.select("#my_chart").selectAll("svg")

    .data(d3.range(year_range1, year_range2))

  .enter().append("svg")

    .attr("width", width + margin.right + margin.left)

    .attr("height", height + margin.top + margin.bottom)

    .attr("class", "RdYlGn")

  .append("g")

    .attr("transform", "translate(" + (margin.left + (width - cellSize * 53) / 2) + "," + (margin.top + (height - cellSize * 7) / 2) + ")");

svg.append("text")

    .attr("transform", "translate(-6," + cellSize * 3.5 + ")rotate(-90)")

    .attr("text-anchor", "middle")

    .text(String);

var rect = svg.selectAll("rect.day")

    .data(function(d) { return d3.time.days(new Date(d, 0, 1), new Date(d + 1, 0, 1)); })

  .enter().append("rect")

    .attr("class", "day")

    .attr("width", cellSize)

    .attr("height", cellSize)

    .attr("x", function(d) { return week(d) * cellSize; })

    .attr("y", function(d) { return day(d) * cellSize; })

    .datum(format);

rect.append("title")

    .text(function(d) { return d; });

svg.selectAll("path.month")

    .data(function(d) { return d3.time.months(new Date(d, 0, 1), new Date(d + 1, 0, 1)); })

  .enter().append("path")

    .attr("class", "month")

    .attr("d", monthPath);

    var csv =[];

Notes About this Code

Although this code does most of the heavily lifting and can be left unmodified, there are specific lines that can be changed and updated dynamically via the use of presentation variables.

Color Thresholds:

The color variable specifies the thresholds for red/yellow/green. In this case I deem the min and max ranges of an airline delay to be between 5 minutes and 30 minutes:

var color = d3.scale.quantize()

    .domain([5,30])

Chart Size Adjustment:

By modifying the code for the margin variable:

var margin = {top: 19, right: 20, bottom: 20, left: 19},

    width = 720- margin.right - margin.left, // width

    height = 136 - margin.top - margin.bottom, // height

    cellSize = 12; // cell size

  The height/width/cell size can be adjustable by changing the hardcoded values to presentation variables such as:

• @{Width}
• @{Height}
• @{CellSize}

Date Formatting:

The 'day' variable responsible for date formatting:

var day = d3.time.format("%w"),

    week = d3.time.format("%U"),

    percent = d3.format(".1%"),

    format = d3.time.format("%Y-%m-%d");

Requires that the format of the date be specified.  The Calendar View script by default uses a 'YYYY-MM-DD' format. If your OBIEE data is a MM-YY-DD format or has a timestamp, you will need to modify the column data format to the following:

Modifying the Date Range:

The Calendar View code by default hard codes a date range of 1990 to 2011. You will most likely need to modify these values for your data set create a presentation variable that allows the users to change the date range dynamically:

var svg = d3.select("body").selectAll("svg")
    .data(d3.range(1990, 2011))

Could be modified to:

var svg = d3.select("#my_chart").selectAll("svg")
    .data(d3.range(year_range1, year_range2))

In the upcoming steps I will show how these variables can be called.

 Step 4: Populate the Narrative and Post-Fix

In the narrative you will need to specify the Date and Metric you want to pass to the javascript function using the corresponding column number (see step 0 if you forgot!)

The Post-Fix should contain the remainder of the Calendar View code. This can remain unmodified:

var data = d3.nest()
    .key(function(d) { return d.Date; })
    .rollup(function(d) { return d[0].Metric; })
    .map(csv);
  rect.filter(function(d) { return d in data; })
      .attr("class", function(d) { return "day q" + color(data[d]) + "-9"; })
    .select("title")
      .text(function(d) { return d + ": " + (data[d]); });
function monthPath(t0) {
  var t1 = new Date(t0.getFullYear(), t0.getMonth() + 1, 0),
      d0 = +day(t0), w0 = +week(t0),
      d1 = +day(t1), w1 = +week(t1);
  return "M" + (w0 + 1) * cellSize + "," + d0 * cellSize
      + "H" + w0 * cellSize + "V" + 7 * cellSize
      + "H" + w1 * cellSize + "V" + (d1 + 1) * cellSize
      + "H" + (w1 + 1) * cellSize + "V" + 0
      + "H" + (w0 + 1) * cellSize + "Z";
}
</script>

Your narrative should be similar to:

Step 5: Create a Second Narrative for the Date Range

This narrative is optional, but assuming you want to give the user the ability to modify the date range, you would take the variables you referenced in the 'Modifying the Date Range' section (in my case year_range1 and year_range2)  and set both of them equal to two presentation variables like below:

Step 6: View Narratives in Answers

Adding both narratives to a single view, your end result should look similar to:

This guide barely scratches the surface of D3-OBIEE integration but serves as a great example of how 3rd party APIs and javascript libraries can be integrated into OBIEE 11g. I encourage all BI Architects to look through the entire D3 library and see how D3 can be integrated into their current engagement.

 

 keywords: OBIEE 11g, Data-Driven Documents, OBIEE 11.1.1.7.0, UserScripts.js, Answers, javascript

How-to: OBIEE 11g 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).

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