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

 user_projectsdomainsbifoundation_domainserversbi_server1tmp_WL_useranalytics_11.1.17dezjlwarresb_mozillacommon

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_projectsdomainsbifoundation_domainserversbi_server1tmp_WL_useranalytics_11.1.17dezjlwarresb_mozillacommond3examplescalendarcalendar.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:

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

Step 6: View Narratives in Answers