Member Does Not Exist in MDX Calculations

Granted if an MDX calculation involves a specific named member, then this member should ideally exist in the datamart and be processed in the cube.  Nonetheless, there may be the occasion where this isn’t the case and when browsing the calculated measure in your OLAP client will get a #Value! error.

The following code snippet provides an example for how to handle missing member. The approach uses a SET expression to look for the missing member followed by a simple IIF expression in the CREATE MEMBER to determine if this member is missing and how to handle it.

Noted, there are numerous more performance savvy and sometimes cumbersome ways to solve this problem in Analysis Services, however the simple approach is taken here to get you up and running.  This will buy you some time to come up with a  more sophisticated approach.

/* PreExempt Missing Member */
Create Set CurrentCube.[MissingMemberSet] As
iif(IsError(StrToMember("[Dimension].[Hierarchy].&[MEMBER]")),
{}, {[Dimension].[Hierarchy].&[MEMBER]});

Create Member CurrentCube.Measures.[Calculation on Missing Member]
AS
IIF ([MissingMemberSet].Count > 0,
([Dimension].[Hierarchy].&[MEMBER],Measures.[SomeMeasure]),
0
)
,
FORMAT_STRING = "Currency",
LANGUAGE = 1033,
NON_EMPTY_BEHAVIOR = { [SomeMeasure] },
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Display Folder'  ;

Thanks to Dr. Nick and various posts by Deepak Puri for the inspiration.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

3 Responses

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s