A Dashboard for every role

Common scenario in a project:

• We need to make a dashboard for the General Manager.
• And then a dashboard for the EMEA Sales representative.
• Don't forget the Portuguese Chef, he also needs information on his stocks of codfish.
  

This means a lot of work; What if we could define just one dashboard without worrying about row level security?

Fortunately for this blog entry, we can. Provided you're using CDF - and of course you are - the answer is to use MDX.

MDX is God's gift to business language; When God created Adam and Eve he just spoke [Humanity].[All Members].Children . That's how powerful MDX is. And Julian Hyde allowed to use it without being bound to microsoft.

In a lot of CDF components we can use MDX directly to get our result sets. But there's a somewhat "hidden" feature in there. If we have the session variable role defined, that role will be passed to mondrian.

Take this for example: Let's picture our Joe as a EMEA representative and our Suzy for US. I'll add the following code to the SteelWheels schema:

<role name="suzy">
<schemagrant access="none">
    <cubegrant cube="SteelWheelsSales" access="all">
        <hierarchygrant hierarchy="Markets" access="custom" 
          toplevel="[Markets].[Territory]" rolluppolicy="partial">
            <membergrant member="[Markets].[All Markets].[NA]" access="all">
        </membergrant>
    </hierarchygrant>
</cubegrant>
</schemagrant>
<role name="joe">
<schemagrant access="none">
    <cubegrant cube="SteelWheelsSales" access="all">
        <hierarchygrant hierarchy="Markets" access="custom" 
          toplevel="[Markets].[Territory]" rolluppolicy="partial">
            <membergrant member="[Markets].[All Markets].[EMEA]" access="all">
        </membergrant>
    </hierarchygrant>
</cubegrant>
</schemagrant>
</role>

I also changed the startup action sequence session-region-list.xaction to store in the username in the session variable role. As a result, here's the MetaLayer home dashboard seen by Joe:




And the same one seen by Suzy:


This is the most simplistic approach; Other things must be taken into account, but really eases the way it works as a whole. Added bonus, all our pivot tables will share the same definitions.
I definitely recommend MDX for every dashboard (or report... or anything, really)

Revenues of open source contributions

A lot of people ask me:

Why do you waste so many time - and money - with community contributions?

I don't have a definite answer; It's not by some "revolutionary principle", in the end I want the same thing as everyone - get rich, retire, and move to some beach in Brazil and sell coconuts for the rest of my life.

There are a lot of logical reasons why I should *not* contribute:

• It requires huge ammount of time to maintain a project / make contributions
• I'm giving away knowledge - for free
  
• I'm allowing others to make profit out of my work with 0 return
• Could basically... sell it / commercialize it in some way

I don't believe in arguments like "if everyone does like you do, the work will be a better place" - most of the users out there are just "leechers" that when it comes to information sharing just pretend to be busy (and I bow to the exceptions that makes the engines run).

Still - I do it cause I feel it's the right thing to do. For a living, I benefit from the work others did - namely the work done by Pentaho guys. Commercial Open Source is still a relatively unknown area and there's a difficult balance between knowledge-sharing and kid-feeding ;)

But sometimes we get some very unexpected revenues; I had a meeting with a potential client that asked me if we could make a demonstration of our work; I was taken by surprise and was not ready to do so, but then I remembered asking if they had a downloaded version of Pentaho version 3. They did. "Great, now open the CDF samples under the bi-developers solution. We did that". Quoting mastercard... Priceless!


ps: I believe this kind of permanent debate inside me is exactly the same as happens daily in the Pentaho Towers between sales and marketing and o-s defenders; So I'm definitely not one of the voices that rises in complains when Pentaho HQ (or any other vendor, for what it matters) decides to relase some subscription module; what ever the decision is, it's a hell of a tough balance to make...

BSHExpressions - when we need control over reporting

Following pentaho forums is a great way to find topics for a blog. This time I used Gunter's question.

I need to make a simple report that show the duration of an event and sum it per day. The type of duration in the database is time (MySQL).

I have tried summing the duration values, but apparently the ItemSumFunction cannot handle the summing. So i converted the time to an integer, made the sum using the ItemSumFunctio, but now i don't know how i can reformat it to show like 'hh:mm:ss'

I'm a very big fan of the latest approach; I think that using longs in the fact table just simplifies everything. Then we only need to solve the formatting aspect of it. Since it's a report we're talking about, we'll use a BSHExpression. (JFreeReport is awesome, there's always more than one solution available for each problem, this is just one of them).

We can't use a simple date formatter because we want dates also over 24h. So I created the following BSHExpression:

import java.text.SimpleDateFormat;
SimpleDateFormat formatter = new SimpleDateFormat("mm:ss");

String secondsToFormattedDate(n){
d = new Date(n * 1000);
return ( d.getTime()/3600000 + ":" + formatter.format(d));
}

String getValue(){
long l = dataRow.get("CategoryAmountExpression").longValue();
return secondsToFormattedDate(l);
}

There are a lot of options to do this code, obviously; we could easily avoid the date object, but it's a way to show how we can invoke other classes.




The final result of this is what we expected: