Mondrian: OLAP power at your USERS fingertips
As promised, todays followup on how to limit the complexity of calculated MDX measures from your users using Mondrian.
If you haven’t already, start with yesterdays blog, entitled Mondrian: OLAP power at your fingertips. We leveraged the power of MDX to quickly build a calculated MDX member. While in and of itself not all that powerful (% of total is not that tough) it provides with a quick and easy way for users to start exploring the power of MDX without having to do any XML editing, etc. You can just type it into the web browser and “try before you buy.”
Ok, so let’s say you’re sold. You’ve got your calculated measure exactly how you want; great. There’s no WAY that you’d want your users to have to see that and copy and paste that in themselves. It also leaves room for people changing formulas, which in the day and age of SOX is just a bad idea. What you want to do is make this MDX part of your Cube so that a user can just reference it, and disregard the complexity of the expression.
- I’m assuming that we’ve already downloaded and started the Pentaho Demo. Refer to yesterdays blogs for instructions.
- Edit the file $PROOT/pentaho-demo/pentaho-solutions/samples/analysis/SampleData.mondrian.xml.
- Add the following XML fragment right before the ending Cube tag.
<CalculatedMember name=”Position Percent of Total” dimension=”Measures” formula=”([Measures].[Actual] / ([Measures].[Actual], [Positions].CurrentMember.Parent))” />
What we’re doing here is tell Mondrian that we want to add a new member named Position Percent of Total to the Measures dimension (a special dimension) using the forumla we worked out yesterday. In theory, this can be any calculated member you’ve sorted out in MDX which means the full power and expressiveness of MDX can be included here. - You should stop and start the server so that mondrian can pick up the schema changes.
$PROOT/pentaho-demo/stop-pentaho.bat
$PROOT/pentaho-demo/start-pentaho.bat - Return to the analysis samples (refer to former blog to find it) and launch the slicer Slice and Dice example. Click on the Cube Navigator and then Measures to see if your new “CalculatedMember” is present:
It should be there so that you can just uncheck the rest of the measures and only have Actual and Position Percent of Total. This is now the experience that your users would have, if they use JPivot here in the web or say the Pentaho Spreadsheet Services (a blog on that next week). - If you expand the All Positions you should see your calculated measure displayed alongside the Actual measure
That’s it! This is really useful for providing your users a bunch of reusable measures (YTD, Versus Period Prior, etc) without having them have to hack it all day in Excel. Enjoy!
June 16th, 2006 at 3:11 pm
Great tutorial.
We need to have medians and other statictical measures in our cubes (mdx). Interesting that you use variance. Where are things like median, variariance documented?
/Niels
June 16th, 2006 at 5:30 pm
These are MDX specific calculations. Mondrian is a “plain jane” implementation of MDX which is used by several vendors. I’d use the quick
WITH MEMBER [Measures].[WHATEVER YOUR MEASURE NAME IS AS ‘YourMDXFunction(definitions(values))’ to build your function and then use the method above for making it available to your users.
I’d suggest checking out the following resources for more on the MDX language and functions:
http://mondrian.sourceforge.net/mdx.html
http://www.databasejournal.com/features/mssql/article.php/1495511 (is Microsoft specific so not everything applies)
“MDX Solutions” a book co-authored by fellow blogger Chris Webb http://cwebbbi.spaces.msn.com/blog/
“Fast Track to MDX” by Mosha P. et al
Let me know how you get on with it! I’d love to hear how Mondrian is being leveraged by our open source/customer community!
October 30th, 2006 at 12:18 pm
[…] Remember, this isn’t trivial (ie, writing MDX fragments) but it’s VERY VERY powerful. Check out the Mondrian MDX reference here for some of the powerful analytic calculations available. Remember, once you’ve got your MDX member working properly HIDE that complexity from your users by adding it to the Mondrian OLAP schema definition. […]
October 31st, 2006 at 10:33 pm
[…] Ok… Now it’s time to build our Calculated Member. This is kind of hairy: it requires some technical prowess to get the MDX calculation correct. Just remember, once you’ve got the calcuation working properly you can include it as part of the Cube so your business users (using JPivot or Pentaho Spreadsheet Services) don’t see that complexity. […]
February 19th, 2007 at 4:30 am
What you forgot is to either multiply by 100, or to add a format with a percent display (which has the multiplication built-in):
Insert something like the following as content into the CalculatedMember tag (I hope the markup will stay intact):
February 19th, 2007 at 4:31 am
Well, it did not, so I’ll try again, this time without the surrounding less-than and greater-than chars:
CalculatedMemberProperty name=”FORMAT_STRING” expression=”‘|#.00%|’”
June 12th, 2007 at 1:53 am
Thanks for your tutorial, but I have a little (or big) problem.
I have a created join, and I need access to this join and concatenate two columns. Can I solve this problem using MDX?
I have tried with:
CONCAT(`persona`.`nombre`, ” “,
`persona`.`apellidos`)
“nombre”
Thanks.
June 12th, 2007 at 1:55 am
Thanks for your tutorial, but I have a little (or big) problem.
I have a created join, and I need access to this join and concatenate two columns. Can I solve this problem using MDX?
I have tried with:
CONCAT(`persona`.`nombre`, ” “, `persona`.`apellidos`)
“nombre”
Thanks.
June 12th, 2007 at 2:03 am
Sorry :’( this is the code:
<Join leftKey=”id_persona” rightKey=”id_persona”>
<Table name=”oficio”>
</Table>
<Table name=”persona”>
</Table>
</Join>
<Level name=”Nombre Oficio” uniqueMembers=”true”>
<KeyExpression>
<SQL dialect=”mysql”>CONCAT(`persona`.`nombre`, ” “, `persona`.`apellidos`)
<SQL dialect=”generic”>”nombre”
</KeyExpression>
</Level>
September 14th, 2008 at 1:15 pm
Hi!
I had a problem , when i wrote
Everything worked ok!, but the “parent” appears “Infinity” . I dont know why and how to resolve …
I hope your answers
Txs!
Pamela