SSAS property MDXMissingMemberMode

Adrian Chodkowski February 8, 2015 0 Comments

Analysis Services, Business Intelligence

Analysis Services, MDXMissingMemberMode, Missing Member, Object not found

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

SELECT 
       {} on 0,
       [Customer].[Customer].&[186951] ON 1
FROM 
       [Adventure Works]

1

2

3

4

5

SELECT

{} on 0,

[Customer].[Customer].&[186951] ON 1

FROM

[Adventure Works]

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.