Beginners Guide To Mondrian And MDX

Skip to end of metadata
Go to start of metadata

I'm finding that, while there is a reasonable amount of documentation for getting started, there isn't much in the way of "Here's where you go" to get away from the newb category.

I'll assume here you've a working installation of Mondrian (whether with the preconfigured install of Pentaho, or what-have-you).

First, visit the Mondrian installation documentation. Near the top is a section entitled "2 Set up test data in a non-embedded database". Do whatever is necessary to get that test dataset setup (this is the 'foodmart' database. If you got the PCI, or otherwise are using the pentaho demo, more than likely you don't already have this setup -- you'd probably know if you did). If you have trouble getting the foodmart database loaded, check this bug and see if the suggestions there work for you: http://jira.pentaho.org/browse/BISERVER-917.

Once you have this dataset loaded you'll need to add this as a datasource to whatever analysis program you're using. In jrubik this is easy. With pentaho you'll need to open your server.xml file (eg, in tomcat it'll be ..../tomcat/conf/server.xml) and in the <Host> tag near the end of your server.xml file add a <Resource> tag to the Pentaho <context> tag, somewhat like the following:

 <Host name="localhost" appBase="webapps"
      unpackWARs="true" autoDeploy="true"
      xmlValidation="false" xmlNamespaceAware="false">

<!-- some commented sections that are irrelevant -->

  <Context path="/pentaho" docbase="webapps/pentaho/">
    <Resource name="jdbc/foodmart" auth="Container" type="javax.sql.DataSource" maxActive="20"
      maxIdle="5" maxWait="10000" username="foodmart" password="foodmart"
      factory="org.apache.commons.dbcp.BasicDataSourceFactory" driverClassName="com.mysql.jdbc.Driver"
      url="jdbc://mysql://servername:3306/foodmart" />
    <Resource ... />
    ...
    <Resource ... />
  </Context>
</Host>

If you have problems with this stuff, check the forums other other wiki article, but suffice it to say, if you want to use the foodmart database to learn MDX, you'll want to get the above working before you go on.

Next, visit this article. There are 63 different articles (at the time of this post) on writing MDX queries. Don't let the fact these articles are written for Microsoft's OLAP implementation scare you. Almost all the queries work fine in Mondrian -- in fact, if you look closely in the documentation on MDX, Julian (or whomever maintains that page) links to this set of articles as well. If there are any incompatibilities it typically takes a minor tweak to fix it. Just skip all the sections related to using their analysis tool(s), and copy or type up the queries yourself in jpivot, jrubik, or what-have-you.

Finally, on the topic of dimensional modeling concepts, take a peak at these URLs:

Again, on the last links, ignore the fact that they're for Microsoft products. The things they say about MDX and so-forth fall right in line with Mondrian on almost every point, enough so that if you're trying to wrap your head around how MDX works, its purpose, and all that, you should find very few differences between Mondrian and MS's implementation of OLAP.

These resources have been invaluable to me. They should be enough to help you get from relying on jpivot, jrubik, etc to generate your queries to being able to formulate good queries, make modifications to them, add filtering, and other things to your queries that aren't straightforward in gui-driven analysis tools. The two tools together (gui tools + your own intellect) make a far more powerful team.

Posted on behalf of Phantal from the Pentaho Forums

More information and external links

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.