Handling Missing Members In The CubeSet() Function With Power Pivot

Reposted from Chris Webb's blog with the author's permission.

Last week I received an email from a reader asking how to handle missing members in MDX used in the Excel CubeSet() function. My first thought was that this could be solved easily with the MDXMissingMemberMode connection string property but it turns out this can’t be used with Power Pivot in Excel 2013/6 because you can’t edit the connection string back to the Excel Data Model:

Instead, you have no choice but to handle this in MDX.

Here’s an illustration of the problem. Imagine you have the following table of data on your Excel worksheet:

With this table added to the Excel Data Model, you could write the following Excel formula using CubeSet():

=CUBESET(
	"ThisWorkbookDataModel",
	"{[Sales].[Product].[All].[Apples], 
	  [Sales].[Product].[All].[Oranges], 
	  [Sales].[Product].[All].[Pears]}", 
	"Set")

In the screenshot above the CubeSet() formula is used in H3, while in H4 there’s a formula using CubeSetCount() that shows the set contains three members.

If the source data is updated so that the row for Pears is deleted like so:

Then the CubeSet() formula returns an error because the member Pears no longer exists:

How can this be avoided? If what you actually wanted was all of the Products, whatever they were, the best thing to do is to use the MDX Members function like so:

=CUBESET(
	"ThisWorkbookDataModel",
	"{[Sales].[Product].[Product].MEMBERS}", 
	"Set")

[I talk about the Members function in this post in my series of posts on MDX for Power Pivot users]

This formula does not return an error and you can see that the CubeSetCount() formula below shows the set only contains two members now:

If you do need to refer to individual members then the MDX you need is more complicated:

=CUBESET(
	"ThisWorkbookDataModel", 
	"{[Sales].[Product].[All].[Apples], 
	 [Sales].[Product].[All].[Oranges], 
	 iif(
		iserror(
		 strtomember(""[Sales].[Product].[All].[Pears]"")
		), 
		{},
		{strtomember(""[Sales].[Product].[All].[Pears]"")} 
	 )
	}", 
	"Set")

This MDX uses the StrToMember() function to interpret the contents of a string as an MDX expression returning a member; if this expression returns an error then it is trapped by the IsError() function and an empty set is returned.

This isn’t particularly pretty, though, and ideally the MDXMissingMemberMode connection string property would be set to Ignore in the Excel Data Model connection string property.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .