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)