- 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.
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)
5 comments:
Hey Pedro, great write up. I also want to point out that you can use Pentaho Metadata's row level security feature for this type of dashboard:
http://wiki.pentaho.com/display/ServerDoc1x/06.+Adding+Row+Level+Security+to+a+Pentaho+Metadata+Model
Hello Will.
You're right, indeed. It probably all goes down to personal preference, but IMO MDX is the best approach for this kind of challenges
Nice post! Thanks ;)
Boas Pedro , eu sou um novato nisto do pentaho, e ainda ando na fase de pesquisa e experimentar de tudo um pouco, já passei pelo ETL, Reporting, e agora queria começar a criar Dashboards, mas estou com um pequeno problema: pretendo criar charts no design studio, que me vão buscar dados a uma mdx query, mas, quando testo, só me aparece "Action Successful" , e não me aparece o chart, se eu criar o chart no Report Design, quando faço Preview aparece me o Chart perfeito, mas, se faço Publish, quando o abro no servidor ele n vai buscar os dados à base de dados. Podias me dar umas dicas? Abraço e obrigado!
Post a Comment