Pages

Sunday, June 13, 2010

Squarified Treemap for OBIEE Dashboard

In one of my earlier post (here), I showed how to show OBIEE data on calendar. In this post I am going to show yet another way to use power of OBBIE Narrative Views to visualize OBIEE data - Squarified Treemap.

Squarified treemap is one of the most effective way to visualize tree structured data, be it a finance or any other domain. I will be using javascript toolkit from http://thejit.org/ to create the squarified treemap. I will suggest you download the toolkit and go over the example code that comes along.

Here's tree structured report which shows the sales data of a store by Product Class, Product Subclass, Product Item. It has a "Rank" column which ranks product based on its sales value and % Share show its share in the total sales value. We will be using Rank to determine color and % Share to determine size of the rectangle in the squarified treemap.

Create a folder name "jit" on OBIEE server under Drive:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\ folder and copy jit.js and Treemap.css from the toolkit to this folder.

Next we create narrative view and write javascript to use treemap control and pass the report data do it. Note: When I was designing it, preview under narrative was showing a black rectangle. However, I could save the report and view treemap in the result / dashboard view.

Here's the script from the narrative view.
Prefix:



<!-- JIT Library File -->

<script language="javascript" type="text/javascript" src="./res/jit/jit.js"></script>

<!-- JIT Stylesheet File -->

<link type="text/css" href="./res/jit/Treemap.css" rel="stylesheet" />

<script language="javascript" type="text/javascript">

function init() {
function itemdata() { this.rank = ""; this.share = ""; }
var rdata = [];

In Prefix section we include jit.js and Treemap.css files. Note the path for the files is ./res/jit. The init function will be called on load event of document body. It will build treemap using report data. The multidimensional object rdata will be used to capture report data. We will use instance of itemdata to store Rank and % Shares .

Narrative:

            if(typeof(rdata["@1"]) == "undefined")

rdata["@1"]=[];

if(typeof(rdata["@1"]["@2"]) == "undefined")
rdata["@1"]["@2"]=[];

if(typeof(rdata["@1"]["@2"]["@3"]) == "undefined")
rdata["@1"]["@2"]["@3"]=new itemdata();

rdata["@1"]["@2"]["@3"].rank=@4;
rdata["@1"]["@2"]["@3"].share=@5;

Here we store report data to rdata. Being tree structure, there could be multiple rows for the same Product Class or Sub Class, so we first do a check if the element with the name is defined or not and then add it. At Product item level we use instance of itemdata and store Rank and % Share of product.

Postfix:




function nonleafclass() {
this.children = new Array();
this.data = new function() { this.$area = "" };
this.id = "";
this.name = "";
}

function leafclass() {
this.children = new Array();
this.data = new function() { this.$area = ""; this.$color = ""; };
this.id = "";
this.name = "";
}

json = new nonleafclass();
json.name = "Product Performance";
json.id = "Product Performance";
json.data.$area = 100;
var i = 0;


for (var cls in rdata) {
json.children[i] = new nonleafclass();
json.children[i].name = cls;
json.children[i].id = cls;
var j = 0;
var iarea = 0;

for (var subcls in rdata[cls]) {
json.children[i].children[j] = new nonleafclass();
json.children[i].children[j].name = subcls;
json.children[i].children[j].id = subcls;
var k = 0;
var jarea = 0;

for (var item in rdata[cls][subcls]) {
json.children[i].children[j].children[k] = new leafclass();
json.children[i].children[j].children[k].name = item;
json.children[i].children[j].children[k].id = item;
json.children[i].children[j].children[k].data.$area = rdata[cls][subcls][item].share;
json.children[i].children[j].children[k].data.$color = rdata[cls][subcls][item].rank;
jarea = jarea + rdata[cls][subcls][item].share;
k++
}

json.children[i].children[j].data.$area = jarea;
iarea = iarea + jarea;
j++

}
json.children[i].data.$area = iarea;
i++;
}


var infovis = document.getElementById('infovis');
var w = infovis.offsetWidth, h = infovis.offsetHeight;
infovis.style.width = w + 'px';
infovis.style.height = h + 'px';

The jit toolkit requires the data in a specific object format. In their example code they have used json syntax to define the data. In postfix section we create same object in bit different way. Note use of Rank and % Share for $color and $area properties. For nonleaf level we calculate area by summing up area of children (i.e. item).

Postfix Continue..

            //init tm

var tm = new TM.Squarified({
//Where to inject the treemap.
rootId: 'infovis',

//Add click handlers for
//zooming the Treemap in and out
addLeftClickHandler: true,
addRightClickHandler: true,

//When hovering a node highlight the nodes
//between the root node and the hovered node. This
//is done by adding the 'in-path' CSS class to each node.
selectPathOnHover: true,

Color: {
//Allow coloring
allow: true,
//Set min value and max value constraints
//for the *$color* property value.
//Default's to -100 and 100.
minValue: 1,
maxValue: 29,
//Set color range. Default's to reddish and greenish.
//It takes an array of three
//integers as R, G and B values.
minColorValue: [0, 255, 50],
maxColorValue: [255, 0, 50]
},

//Allow tips
Tips: {
allow: true,
//add positioning offsets
offsetX: 20,
offsetY: 20,
//implement the onShow method to
//add content to the tooltip when a node
//is hovered
onShow: function(tip, node, isLeaf, domElement) {
tip.innerHTML = "<div class=\"tip-title\">" + node.name + "</div>" +
"<div class=\"tip-text\">" + this.makeHTMLFromData(node.data) + "</div>";
},

//Build the tooltip inner html by taking each node data property
makeHTMLFromData: function(data) {
var html = '';
html += "Share in Sales" + ': ' + data.$area + '%<br />';
if ("$color" in data)
html += "Rank" + ': ' + data.$color + '<br />';
return html;
}
},

//Remove all element events before destroying it.
onDestroyElement: function(content, tree, isLeaf, leaf) {
if (leaf.clearAttributes) leaf.clearAttributes();
}
});
//load JSON and plot
tm.loadJSON(json);
//end
} //init

document.body.onload=function() { init() };
</script>

<div id="center-container" style="width:700px;background-color:#1a1a1a;color:#ccc;height:500px;">
<div id="infovis" style="width:700px;height:500px;margin:auto;overflow:hidden;position:relative;"></div>
</div>
<div id="log">
</div>

The above code is picked from the example. It initializes the squrified treemap from the toolkit and setup necessary property. The name of DIV tag where treemap will be rendered is infovis. I changed maxvalue property of color to 29, since I have 29 rows on my report. You can even calculate this value dynamically if required. Also, I modified makeHTMLFromData to use information from the report. This will be shown as tooltip on the treemap. The line tm.loadJSON(json) passes the data we prepared to treemap control. As mentioned earlier we bind call to init function on load event of the page. Finally, we create required DIV tags.

Don't miss-out to select "Contains HTML Markup" checkbox. Here is how report looks once saved and placed on the dashboard. When you hover treemap, it show tooltip for specific item. Note the color change based on rank from bright green (1) to bright red (29). Also, note the size of rectangle is according to % Share in sales value.
You can drilldown to any level in the treemap to analyze specific area using left mouse click. For example, I drilled-down here to leaf level member that has rank of 26.


Right mouse click will drill one level up. When I, right clicked above I get following.

And then one more level up.

You can also jump to any specific level in the tree. For example, below I can click on Product Class - Cereal and it will present the data under it.

I hope this post will be helpful for those who are looking to implement squarified treemap visualization over OBIEE dashboard.

The jit toolkit has many other visualization controls. I will try to cover them in my future post.

18 comments:

  1. Hi,
    can i have your Firefox Soource Code please?

    I always have a black rectangle in my narrative view....why??

    Thanks a lot

    Andrew (andrea.spanu@nous.it)

    ReplyDelete
  2. just change the line

    document.body.onload=function() { init() };

    with

    document.body.onload=new function() { init() };

    Notice "new" keyword. This will work both in IE and Firefox. I will update the article.

    ReplyDelete
  3. i changed as you said but it'doesn't work yet. It gives me "TM is not defined" error. What can I Do?

    ReplyDelete
  4. There were series of messages between me and Andrea. Here's solution that worked with firefox.

    Replace line

    document.body.onload=function() { init() };

    with

    if(typeof document.body.addEventListener != "undefined")
    {
    document.body.addEventListener("load", init, true);
    }
    else
    {
    document.body.onload=init;
    }

    ReplyDelete
  5. Hello I would like to know if we can implement something similar to a ORG chart using OBIEE for showing Position Hierarchy

    ReplyDelete
  6. Yes. It is possible. Watch out for my upcoming post.

    ReplyDelete
  7. Hi Hitesh
    I am on OBIEE 10.2 and try to implement this on my local using sample report given in sample sales and facing the same issue TM not defined. I tried alll the above options but didnt help...any clue

    ReplyDelete
  8. Hi, thanks a lot for posting this , it helped a lot,but could you help me with a cgi I've been doing to test this out. I manage to get the graph coding and the javascript right(if i substitute mine into the example file, it works) but when I run my cgi on the browser the graph just stays black. I know it probably has to do withmy html but I cant seem to get it.

    ReplyDelete
    Replies
    1. Am happy that its working for you... Can you please give solution for black rectangle issue? or Please post code

      Thanks in Advance

      Delete
  9. HI Hitesh, I'm also seeing a Black Rectangle. I've checked the location of the Jar and CSS file. I dont understand why its not working. Can you advise?

    ReplyDelete
  10. hey me too see the black rectangle..pls give an advice soon.I am stuck with it

    ReplyDelete
  11. i have the black rectangle even if i have followed all the change that u write above
    i dont know what is ur java versione do u use?

    ReplyDelete
  12. Put me in the black rectangle group, too, for both ie and ff

    ReplyDelete
    Replies
    1. Same problem... black rectangle!!! can u help me out?

      Delete
  13. Not working getting black rectangle ???

    ReplyDelete
  14. Very helpful post, great idea. Thanks!

    ReplyDelete
  15. Hi Hitesh,

    could you please provide the jit.js and treemap.css file.

    Regards,
    Rushi

    ReplyDelete
  16. Hi,

    how abt this one work with OBIEE 11g, could you please the path where to create the folder jit incase of 11g and also please provide the jit.js and treemap.css file which u had with you.

    Please reply back as it is more imp requirement in m y case.

    Thanks in Advance,
    Regards,
    Rushi.

    ReplyDelete