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.
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.
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.
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