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:
SELECT
[Measures].[Sales] ON 0,
[Sales Channels].[Sales Channel].Children ON 1
FROM
[Sales]

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.

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


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!

Mondrian MDX and Schema Validation Difference PDR and Schema Workbench

PDR 3.6.1 doesn't seem to behave the same way in processing MDX queries and XML Schemas than other tools (Schema Workbench, JPivot, etc). 

In example, CurrentDateMember([Date.Weekly Calendar], '[yyyy]\.[ww]').Lag(3.0) works perfectly in Schema Workbench, but not in PDR. PDR requires a more accurate writing style: CurrentDateMember([Date.Weekly Calendar], '["Date.Weekly Calendar"]\.[yyyy]\.[ww]').Lag(3.0). So whereas Schema Workbench and JPivot can figure out where to find year and week, PDR needs the exact path.

Another example: The formula of a calculated member looks like this in the Schema: [Subscription Base Revenue Share Deals (Actual)]/[Subscription Base (Actual)]. If you use JPivot or Schema Workbench, everything will work perfectly. But PDR requires the formula to be like this: [Measures].[Subscription Base Revenue Share Deals (Actual)]/[Measures].[Subscription Base (Actual)]. 

For both example, the syntax that PDR requires is the really accurate one. I would really appreciate if Schema Workbench (version 3.2.0.13661) would have the same strict validation as well. This would avoid a lot of confusion as to why we get so many more error messages in PDR.

When discussing this topic on the Mondrian developer mailing list, Julian Hyde commented the following:
"It looks like PRD is using mondrian to validate formulas. I suspect that it is an earlier version of Mondrian, which had weaker validation rules. I don't recall why we made the change, but people will log bugs that MDX succeeds in SSAS and fails in mondrian, and we will (rightly) change mondrian.

Qualifying members with their dimension & hierarchy name is recommended. Mondrian can resolve members faster if you do.

We can't give an error if people don't qualify member names. But should we emit a warning if someone writes [Store Sales] / [Store Cost] in a formula? I don't have a strong opinion either way."

As Thomas pointed out in the comment below, have a look at the mondrian.properties file located in the PDR folder report-designer\resources. You can find various settings there like this one:

mondrian.olap.elements.NeedDimensionPrefix=true

This seems to be the one that stopped my "not so accurate" MDX queries to run. I do not recommend changing this setting though, but advise to write precise MDX queries and make sure that the calculated members in your Schema have to complete reference as well.

UPDATE 2010/10/04:

Make sure the PDR mondrian.properties file is set up the same way as the BI Server and Schema Workbench ones.

Open prd-ce-3.6.1-stable\report-designer\resources\mondrian.properties and look for the settings shown below (these ones, especially the first one, will have a major impact):

###############################################################################

# Property determines if elements of dimension (levels, hierarchies, members)

# need to be prefixed with dimension name in MDX query.

#

# For example when the property is true, the following queries

# will error out. The same queries will work when this property

# is set to false.

#     * select {[M]} on 0 from sales

#     * select {[USA]} on 0 from sales

#     * select {[USA].[CA].[Santa Monica]}  on 0 from sales

#

# When the property is set to true, any query where elements are

# prefixed with dimension name as below will work

#     * select {[Gender].[F]} on 0 from sales

#     * select {[Customers].[Santa Monica]} on 0 from sales

#

# Please note that this property does not govern the behaviour where in

#     * [Gender].[M]

# is resolved into a fully qualified

#     * [Gender].[All Gender].[M]

#

# In a scenario where the schema is very large and dimensions have large

# number of members a MDX query that has a invalid member in it will cause

# mondrian to to go through all the dimensions, levels, hierarchies, members

# and properties trying to resolve the element name. This behaviour consumes

# considerable time and resources on the server. Setting this property to

# true will make it fail fast in a scenario where it is desirable

#

mondrian.olap.elements.NeedDimensionPrefix=true

Don't change this one: It's important that your Schema and MDX has properly defined syntax.

###############################################################################

# Boolean property indicating whether errors related to non-existent members

# should be ignored during schema load. If so, the non-existent member is 

# treated as a null member.

mondrian.rolap.ignoreInvalidMembers=true

Change to: mondrian.rolap.ignoreInvalidMembers=false

###############################################################################

# Boolean property indicating whether errors related to non-existent members

# should be ignored during query validation. If so, the non-existent member is 

# treated as a null member.

mondrian.rolap.ignoreInvalidMembersDuringQuery=true

Change to: mondrian.rolap.ignoreInvalidMembersDuringQuery=false

Now open psw-ce-3.2.0.13661\schema-workbench\mondrian\properties and add the above highlighted properties from the PRD properties file (if these properties already exist, amend them so that they are exactly set the same way). 

In my example mondrian.rolap.ignoreInvalidMembers was already in the Schema Workbench mondrian.properties file and properly set to "false" (so no changes necessary). I also added the two other properties.