Sunday, February 3, 2013

Mondrian: Consequences of not defining an All member

To come straight to the point: If you do not define an all member for a hierarchy Mondrian will implicitly create a slicer with the default member of the dimension … this is even happening if you do not mention the dimension at all in your MDX query!

In example take following MDX:
[Measures].[Sales] ON 0,
[Sales Channels].[Sales Channel].Children ON 1

If we take a look at the SQL that Mondrian generates, we suddenly see that it tries to restrict on the year 2012 in the join condition:

Why is this happening? The reason lies in the fact that one of the hierarchies of the date dimension does not have an All member. So Mondrian tries to find the first member of this hierarchy (as this is the default member), which happens to be [Year]. And as in this case I only had data as of the year 2012 in the date dimension table, it was used in the join.

<Hierarchy name="Time" hasAll="false">
<Level attribute="Year" />
<Level attribute="Quarter" />
<Level attribute="Month" />
<Level attribute="Day"/>
<Hierarchy name="Weekly" hasAll="true">
<Level attribute="Year" />
<Level attribute="Week"/>
<Level attribute="Weekday"/>

Note if we use a hierarchy of the Date dimension in the MDX then everything works as expected:

So it is really important to keep in mind what consequences not defining an All member has!


  1. Is this the intended behavior? Sounds like a bug

    1. Yes, I first thought it was a bug as well, but it isn't ... this is also why I decided to write this blog post about it. I initially created a JIRA case for it, which you can find here: Please read Julian's comments. I've always defined an ALL members, so I never encountered this behaviour. Only in the tests that I did for Mondrian 4 I omitted it once and realized this behavior.

  2. Also if you set hasAll="false" and you haven't loaded any data into your tables Mondrian throws an IllegalHierarchyException because it tries to find that default member in the database. So if you haven't loaded a table yet it makes it impossible to open a Mondrian connection in this scenario. Not sure this "feature" is as well designed as it should be.