Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Handling missing members

with 3 comments

This is something of an FAQ on the newsgroups, but since someone asked me about it again recently I thought I’d write up the answer up for posterity: in an MDX query, how do you deal with the problem of a query breaking because members which were once present in a cube are no longer present? For example, you have a query that references the member [Time].[2002].[Q1].[M1] but in a freak dimension-processing accident you suddenly find that this member no longer exists in your cube. In this scenario, trying to run your query will raise an error.

Now in any well-run data warehouse this shouldn’t really happen but in the real world it does. In AS2K MDX there isn’t an elegant solution to this problem – you have to use the VBA ISERROR function as follows:

WITH
MEMBER MEASURES.TEST AS
‘IIF(VBA!ISERROR(STRTOVALUE("MEASURES.[UNIT SALES]")), 1,0)’
MEMBER MEASURES.TEST2 AS
‘IIF(VBA!ISERROR(STRTOVALUE("MEASURES.[THIS WONT WORK]")), 1,0)’
SELECT {MEASURES.TEST, MEASURES.TEST2} ON 0 FROM SALES

In Yukon, though, it’s handled much better: dimensions have a property called MDXMissingMemberMode, which you can set to either ‘Error’ which reproduces the AS2K behaviour, or ‘Ignore’ which means that the member is ignored if the dimension part of the unique name is valid but the name or key part is not. The default behaviour is ‘Ignore’.

I have to say that this caused some head-scratching here when we first came across it: when you’re writing MDX manually, sometimes you don’t spot the fact that a member isn’t appearing in your results because you’ve misspelled its name. Overall though, I think the benefits of this new functionality outweigh any potential confusion.

Written by Chris Webb

March 23, 2005 at 10:19 am

Posted in MDX

3 Responses

Subscribe to comments with RSS.

  1. In the latest CTPs it is actually much more complex then that. The rules for MdxMissingMemberMode are different between user queries and MDX Script and security definitions (where you probably always want to get an error), and there is also connection string property which controls how queries behave etc. Hopefully documentation will make all of it clearer.

    Mosha

    March 23, 2005 at 5:52 pm

  2. I long for a simple wrapper function…something similar to how AddCalculatedMembers() works. Instead of adding calculated members, however, it would remove missing members. You call it something like this:RemoveMissingMembers([SomeSet])This function would removing missing members regardless of why they were missing…it could be due to security or a mis-spelling or a member that was there and is no longer.I think you have enough "complexity" in Analysis Services…what I think you need more of is "simplicity". I believe a function as noted above which I sent into SQL Wish 3 years ago would go a long ways to helping out this situation. It puts the control in the hands of the query designe…which is where you want itr….not in the hands of the cube builder…or worse yet…some combination of the 2.

    Unknown

    April 27, 2005 at 4:02 pm

  3. Hi Chris,I think we met some time ago at PMI in lausanneAny way, This may be irrelevant here, but I\’m desperate to get an answer. We just migrated to AS2k 64-bit. The dynamic security that used to work fine in 32-bit is failing now because we can not handle missing members with iserror (64bit does not support VBA) Any hint to treat missing member in AS2K 64-bit? Thanks

    Nabil

    June 13, 2005 at 4:43 pm


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

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: