SSAS property MDXMissingMemberMode

Adrian Chodkowski February 8, 2015 0 Comments

Today I would like to write few words about MDXMissingMemberMode. This is property of a dimension in SSAS and sometimes a need occur to change its default value.

MDXMissingMemberMode has three possible values:

• Default
• Error
• Ignore

where Default is Ignore. When we leave default value, all references in our MDX code that point to non existing members of dimension hierarchy will be ignored and client tool like SSMS will return empty result, for example

Return nothing because member with uniqe name [Customer].[Customer].&[186951] doesn’t exist. When we set MDXMissingMemberMode to Error then we will get:

Query (2, 1) The level ‘&[186951]’ object was not found in the cube when the string, [Customer].[Customer].&[186951], was parsed.

Default behavior of this property is needed in most cases when we deal with flexible hierarchies – because when member moves from one parent member to another it automatically changes his unique name – but sometimes it will be useful to change this default behavior.