{"id":178,"date":"2006-06-09T11:59:07","date_gmt":"2006-06-09T18:59:07","guid":{"rendered":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2006\/06\/09\/mondrian-olap-power-at-your-users-fingertips\/"},"modified":"2006-06-09T11:59:07","modified_gmt":"2006-06-09T18:59:07","slug":"mondrian-olap-power-at-your-users-fingertips","status":"publish","type":"post","link":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2006\/06\/09\/mondrian-olap-power-at-your-users-fingertips\/","title":{"rendered":"Mondrian: OLAP power at your USERS fingertips"},"content":{"rendered":"

As promised, todays followup on how to limit the complexity of calculated MDX measures from your users using Mondrian.<\/p>\n

If you haven’t already, start with yesterdays blog, entitled Mondrian: OLAP power at your fingertips<\/a>. 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.”<\/p>\n

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.<\/p>\n

    \n
  1. I’m assuming that we’ve already downloaded and started the Pentaho Demo. Refer to yesterdays blogs for instructions.<\/li>\n
  2. Edit the file $PROOT\/pentaho-demo\/pentaho-solutions\/samples\/analysis\/SampleData.mondrian.xml.<\/li>\n
  3. Add the following XML fragment right before the ending Cube <\/strong>tag.
    \n<CalculatedMember name=”Position Percent of Total” dimension=”Measures” formula=”([Measures].[Actual] \/ ([Measures].[Actual], [Positions].CurrentMember.Parent))” \/>
    \n<\/strong>
    \nWhat we’re doing here is tell Mondrian that we want to add a new member named Position Percent of Total <\/em><\/strong>to the Measures <\/strong> 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.<\/li>\n
  4. You should stop and start the server so that mondrian can pick up the schema changes.
    \n$PROOT\/pentaho-demo\/stop-pentaho.bat
    \n$PROOT\/pentaho-demo\/start-pentaho.bat<\/strong><\/li>\n
  5. 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:
    \n
    \nIt should be there so that you can just uncheck the rest of the measures and only have Actual <\/strong>and Position Percent of Total<\/strong>. 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).<\/li>\n
  6. If you expand the All Positions<\/strong> you should see your calculated measure displayed alongside the Actual<\/strong> measure
    \n<\/li>\n<\/ol>\n

    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!<\/p>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[16,11],"tags":[],"_links":{"self":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/178"}],"collection":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/comments?post=178"}],"version-history":[{"count":0,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/178\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/media?parent=178"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/categories?post=178"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/tags?post=178"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}