OBIEE - Aggregation Rules

Talking of aggregation rules in OBIEE, people uses Sum, Average and Count most commonly. Some of them are not even aware in what scenario the other aggregation rules can be used. In this post I am going to show a real-life business scenario for use of some of the other aggregation rules.


Take a look at following accounting data. There's a fact holding Opening Balance, Debit Amount, Credit Amount and Closing balance for accounts on daily basis. We have a Time dimension with hierarchy Year-Month-Week-Day.





What aggregation rule will apply to "Opening Balance" measure? Ideally if I look at "Wk2", it should give me value $2,359 i.e. opening balance of first day of "Wk2". Similary, if I look at Jan 2010, it should give me value $1,000 i.e opening balance of the month. This cannot be achieved with commonly used Sum or Average aggregation rules.


Some people suggests, we can create Year, Month and Week level aggregate tables and design aggregate navigation. Technically this is right, but what if a new level (Quarter) gets introduce at later point. You will require changes at many places.


Now let see how we can solve this scenario using aggregation rules. On the aggregation tab select "Based on dimnsions" checkbox.

A list will show up with "Other" dimension with "Sum" aggregation for it. Leave this as is. Cilck on "New" button at the bottom of the dailog.

In the dimension list select Time dimension.






In the formula dropdown select formula with aggregation rule of "FIRST".






Defining "FIRST" aggregation formula on Time dimension ensures that, for the non-granular members (such as Year, Month, Week), value of its first granular member is returned.


Why "Sum" aggregation needed for "Other" dimension? The example data above just shows information of one account, but ideally we will have more such accounts and we want to sum up value of opening balance of all account to know the total opening balance at a particular time.


Finally, we can handle closing balance too in similar way by choosing "LAST" aggregation formula.

OBIEE - Schedule Purge and Re-build of cache programmatically

In a standard OBIEE implementation, we generally want to purge and re-build the cache after every ETL run. This post deals with how to programmatically purge and re-build cache and also schedule it.

Purging Cache
OBIEE provides ODBC-extension functions that can be used to purge cache programmatically. These functions can be called using NQCMD commandline tool. There are four different functions that can be used to purge cache.

• SAPurgeAllCache - Purges all cache
• SAPurgeCacheByDatabase - Purges all cache for a specific physical database
• SAPurgeCacheByTable - Purges cache for a specific physical table
• SAPurgeCacheByQuery - Purges cache for a specific query

Here's how to use the command in windows environment.

Create a text file using notepad with name "PurgeCache.txt" and add following line to it. Save the file to local disk (Let's assume to drive C:\)

Call SAPurgeAllCache();

Start command prompt and run following command. Note, there are four parameters in the command. You must replace this with values relavent to your implementation.

-d = ODBC datasource name
-u = Admin user name
-p = Admin Password
-s = sql file

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt

Since we used SAPurgeAllCache function, it will clear all cache on the data source . Refer this link for syntax of other functions.

Re-building Cache
Once the cache is purged, you may want to re-build cache for some of the frequently used reports to avoid delay in response to the users. Re-Building cache requires executing the "logical" SQL query against the data source. We can use NQCMD same way we used for purging cache. Instead of purge function, we have to include logical SQL statements in the text file.

Create a text file using notepad with name "BuildCache.txt" and add logical SQL commands to it. You can include more than one command separated by semicolon(;). Save the file to local disk (Let's assume to drive C:\). Below is example of the text file content.

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."SalesAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

SELECT Product."Department" saw_0, Product."Style" saw_1, Sales."CostAmt" saw_2 FROM MYCATALOG ORDER BY saw_0, saw_1;

Start command prompt and execute the command same way we did for purging the cache.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

Scheduling Purge and Re-build
You may not want to run purge/rebuild cache manually after each ETL run, so you can create a batch file using notepad, "Caching.bat" and add the two command that we ran manually to it. Save the file.

NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\PurgeCache.txt
NQCMD -d AnalyticsWeb -u Administrator -p Administrator -s C:\BuildCache.txt

We can schedule this file to run automatically using windows scheduling using following steps.

Go to Control Panel and double click "Schduled Tasks"

In the Schduled Tasks window, double click "Add Scheduled Task".

The Scheduled Task Wizard screen appears.
Click Next to continue.

Click Browse button on next screen. Navigate to the C:\Caching.bat and click Open button.
Next, you give the task a name and choose when to run the task.

Name the scheduled task.
Pick a name that will make sense to you later when you want to check the list or make any changes.

Choose when to run the scheduled task.
There are a number of choices as to when you would like to run the task:

Daily – once a day
Weekly – once a week
Monthly – once a month
One time only
When computer starts
When I log in

Click "Next" to continue.
Specify the day and time to run the scheduled task.

Click "Next" to continue once you have made your time selections.
Enter the name and password of the user who is authorized to run the task. Generally Administrator.

Click Next to continue.

Click Finish to complete scheduling the task.

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.