About MDXMissingMemberMode in SSAS

I just wanted to write about the important Dimension property called “MDXMissingMemberMode” in SSAS 2005 and 2008.

In SSAS 2000, it was raising “Can’t find dimension member” errors. It used to be a difficult task to handle those errors. The only workaround was to use ISERROR function. Even this function can’t be used in many cases.

Starting from SSAS 2005, new property is introduced to ignore the errors or to raise the errors.

We’ve to be very careful in utilizing this property. Because it is very common tendency to set the property to set to ignore the errors and this case is valid only when you are running the reports.
But this case again bothers you if you have any interface to setup MDX formula. As a common practice in any MDX formula editors or setup interface, Syntax needs to be checked. If it is ignoring the Missing key errors you can’t image the impact on accuracy of results. Because it does not even show you the MDX syntax errors. Hardest part is that anything that you type is considered is taken as the correct MDX.

Example is that instead of [Account].[Net Income] even if you put [Account].[Net Income Net income] or even if you give [Account].Net Income] (Starting bracket is missing) it would accept the MDX and does not raise the error.

I’ve seen so many complaints around this and end user can’t image what’s going on with this.
This property can be setup in 2 places.

1. At Dimension level ( as shown below)
If you setup the MDX Missing Member Mode property at dimension level then it ignores the errors only on this dimension.

2. As a part of connection string
If you specify the below property in your OLAP connection string then it would raise the errors related to missing keys / formula errors in all the dimensions.
MDX Missing Member Mode=Error;

So the best practice is that always setup the Missing member mode property as a part of connection string. In addition to this, wherever you are allowing for formula setup – By default set to raise the error in connection string (Not at dimension level).
Wherever you are displaying the reports – By default set it to ignore the errors but provide flexibility in your OLAP clients or reporting interface to user to click on that option and see if there are any cells which are having formula syntax errors or missing keys.

Hope fully this is helpful.