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():
[sourcecode language=”text” padlinenumbers=”true”]
=CUBESET(
"ThisWorkbookDataModel",
"{[Sales].[Product].[All].[Apples],
[Sales].[Product].[All].[Oranges],
[Sales].[Product].[All].[Pears]}",
"Set")
[/sourcecode]
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:
[sourcecode language=”text”]
=CUBESET(
"ThisWorkbookDataModel",
"{[Sales].[Product].[Product].MEMBERS}",
"Set")
[/sourcecode]
[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:
[sourcecode language=”text”]
=CUBESET(
"ThisWorkbookDataModel",
"{[Sales].[Product].[All].[Apples],
[Sales].[Product].[All].[Oranges],
iif(
iserror(
strtomember(""[Sales].[Product].[All].[Pears]"")
),
{},
{strtomember(""[Sales].[Product].[All].[Pears]"")}
)
}",
"Set")
[/sourcecode]
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
thanks for writing about cube formula, I tried a lot Fancy charts in self service BI tool, but nothing beat a cube formula in Excel, this thing is so powerfull
Chris,
Would you please write a book on CUBE formulas? I have been looking but cannot find a good one. They are so powerful and much more flexible than Pivot Tables.
I’m not sure there’s enough there to support a whole book, but I agree they are very useful 🙂
Hi Chris,
I converted the following DAX measure into a MDX dynamic named set and calculated member:
Earliest Selected Year Sales:=
VAR
EarliestSelectedYear = CALCULATE(MIN(‘Order Date'[Fiscal Year]), ALLSELECTED(‘Order Date'[Fiscal Year]))
RETURN
CALCULATE([Sales],’Order Date'[Fiscal Year] = EarliestSelectedYear)
CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[Hidden Dynamic Set] AS
HEAD([Order Date].[Fiscal Year].[Fiscal Year].MEMBERS);
CREATE MEMBER CURRENTCUBE.[Measures].[Earliest Selected Year Sales] AS
AGGREGATE(
[Hidden Dynamic Set]
,[Measures].[Sales]
),
FORMAT_STRING = “$#,##0.00;($#,##0.00)”,
VISIBLE = 1;
Is it possible to prevent the dynamic named set from being recomputed every time a query is run? How would you write the MDX measure?
Thank you,
John
No, unfortunately – by definition a dynamic named set has to be evaluated each time a query is run. Presumably you’re handling a situation where a user can select multiple years? If so, your current MDX is probably the best way of doing this.
Thanks, Chris.
My goal was to create a group of measures that compare annual totals against a single base year rather than using the more common year-over-year comparison. For example, if the user selects FY15, FY16, FY17, and FY18, then the following comparisons would be made:
FY15 vs FY15
FY15 vs FY16
FY15 vs FY17
FY15 vs FY18
A hypothetical example (which might be different in your experience) is technology book sales where the initial release period generates the most sales and subsequent periods show a steady decline over time. If the initial release period is the most important period (the bellwether), then it might be relevant to compare all subsequent periods to the initial release period.
I read about the dynamic named set approach on Gerhard Brueckl’s blog: https://blog.gbrueckl.at/2014/12/ssas-dynamic-named-sets-in-calculated-members/
Gerhard wrote that a dynamic named set is recomputed every time a query is executed, even if the dynamic named set is not used in a query. I was hoping that you might know either a better approach or a workaround to prevent the dynamic named set from being recomputed when not referenced in a query.
Thank you,
John
Well, thinking about it another approach you could try would be to build a measure group from your Date dimension table (as discussed here: http://sqlblog.com/blogs/mosha/archive/2007/05/27/counting-days-in-mdx.aspx) and use a measure to work out the first year selected. However, I don’t think you should worry about the overhead of your named set – it should have no obvious impact on query performance, even though it is evaluated for each query, because it is very simple.