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!
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
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!
Pingback: Nicholas Goodman on Business Intelligence » Sales Percent increase month to month, qtr to qtr
Pingback: Nicholas Goodman on BI - Musings on reporting, OLAP, ETL, open source » Trend Lines in Mondrian
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):
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%|'”
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.
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.
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>
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
Is it possible to have more than one calculated member both in MDX query or in XACTION.
So far, always appear error when put more than one calculated member