Issues with MSAS default members

We recently came across this confusing issue with how default members affect resultsets. If you use default members in your dimension, you may have this issue unknowingly and you should definitely check your reports which go out for validity.

Its been a long standing bug-bear of mine that there’s no easy way in Excel of knowing that a default member has been set for an attribute, but this is entirely different and much more serious

Consider a simple cube with the below dimensions. The salient point from the cube design is:

• Random Measure – defaults to Daily Sales
  
• Biz Date – defaults to 2011-08-25

The data in the cube is summarily represented below. The yellow bars represent the default members

Now, I want to use this new cube to do Sales reporting by aggregating sales over days. You’ve just done away with the several-hour database queries and are excited about your new cube and the fact that it can do the calculations which used to take hours in a matter of seconds. You’re also excited about the ability for new reports to be generated without the need for IT involvement or testing so business users can ‘self serve’.

Now, I am asked for the total sales across all stores for 24-Aug and 25-Aug.

I do this by setting the below in my Excel

• Biz Date – {2011-08-25, 2011-08-24}
• Risk Measure – { Daily Sales }
• Store – {all}

Now, I pull in my Value and see that the answer is 13.

I send this off pleased with myself that I’ve managed to answer their query in minutes instead of days. Such a simple query, what could possibly go wrong? I’m then asked to break the sales down by both Date and Store for a more granular report.

Now I’m confused – the total is apparently 21, but according to my initial report, it was 13. This is because 13 is reflective of the default member values only – so in this case it is the value for 25-Aug.

Inconsistent behaviours

The situation is made worse because the behaviour is inconsistent and depends entirely on how the query is written

Using Excel and not selecting default members

If you don’t select the default member, you get the correct sum. Look at the below example

Biz Date – {2011-08-24, 2011-08-23}
Risk Measure – { Daily Sales }
Store – {all}

Semantical nuances

How you write your MDX affects the results you get. If you write the below query you get the correct results back.

Excel, unfortunately, generates nested select statements. The actual query generated is below. Its as if the red box is ignored.

A different query, but still keeping with a sub-select similarly ignores the red box.

Summary

This issue will only affect you if you set default members in your dimensions and write queries using sub-selects. If you leave everything defaulted to the ‘all’ member, you can continue blissfully unaware. It may sound obvious, but if you are using defaulted members in your dimensions, make sure you test your output thoroughly.

This is going to be documented on the Microsoft Knowledgebase soon as a KB article, but that’s akin to a health warning on medication.

Acknowledgements

Sanjay Nayyar and Christian Bracchi from IMGROUP for helping identify this issue