- 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)
This comment has been removed by the author.
ReplyDeleteHey 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:
ReplyDeletehttp://wiki.pentaho.com/display/ServerDoc1x/06.+Adding+Row+Level+Security+to+a+Pentaho+Metadata+Model
Hello Will.
ReplyDeleteYou'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 ;)
ReplyDeleteBoas 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!
ReplyDeleteOi pessoal,
ReplyDeleteEstou tentando criar um dashboard com o pentaho e não estou conseguindo.
Quem me pode ajudar?
Hello Pedro,
ReplyDeleteThanks for this.
I face a problem where the number of roles is too high to use Mondrian roles definitions in the cube XML.
Please advise how to handle such high number of roles (1000s, based on customer ID level - one manager for a couple of customers, so need manager level security for thousands of managers)
Heres what I have come to at the moment...
Looks like it is possible to have one generic role and pass on Pentaho user ID as the restricted value in a dimension which maps authorized user id to dimension value.
Eg: Customer1 - joe ---in dimension record
Then in the cube define a view alias with a SQL definition:
customerdimension.userID = %{userName}
Will this apply row level security for PUC logged in user to see only customer records mapped in customer dimension to his name?
If this works, idea is to create Pentaho Analyzer reports over cubes defined as above with SQL restricting cube results to the userName logged in to PUC such as "joe".
Then publish the Analyzer reports to end users each of whom will see only his specific data in the Analyzer report.
Also expect that the user while adding filters/removing on the Analyzer reports cannot somehow see unauthorized data. I am hoping the cube definition prevents that.
Please help with your views as to how to achieve this.