Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

Referencing Named Sets in Calculations

I was recently involved in an interesting discussion about the negative performance impact of referencing named sets inside calculated members. It’s an issue that’s dealt with in this topic in BOL, along with lots of other useful tips for things to avoid when writing MDX calculations:http://msdn.microsoft.com/en-us/library/bb934106.aspx

Since I see lots of people making this mistake, though, I thought it was nonetheless worth a blog post; it’s certainly very easy to reproduce in Adventure Works. Take the following set of calculations:

You’ll notice that TEST1 and TEST2 are essentially the same calculation, as are TEST3 and TEST4; the only difference between them is that the set expressions in TEST1 and TEST3 have been replaced by references to the named set ALLCUSTS in TEST2 and TEST4.

Now run the following query four times on a cold cache, each time putting a different calculated measure from the list above in the WHERE clause:

On my machine the query with TEST1 took 874ms to run; the query with TEST2 took 6302ms; the query with TEST3 took 234ms; and the query with TEST4 I ended up killing after a few minutes.

So, clearly, as the article says referencing a named set inside one of the MDX aggregation functions in a calculation is a Very Bad Thing for performance and something to be avoided at all costs. While it might seem an appealing thing to do for readability, the downsides are significant.

13 thoughts on “Referencing Named Sets in Calculations”

And the real problem is that dynamic sets (which are the only generally working solution to the multiselect problems) suffer from the performance penalty.
Honestly saying, I don’t quite understand where SSAS takes the wrong path. MDX Studio, for instance, provides the same statistics for [Customer].[Customer].[Customer].MEMBERS, SET and DYNAMIC SET (except the time of course):

If I explicitly give the member a value such as
[Date].[Calendar Date].[Date].&[20120928]
The query runs in 7 seconds which I find strange because the above member statement runs extremely quick standalone.

No, this post is nothing to do with your problem – you’re not using named sets. You’ve just got a badly performing calculation! I’ll reply to your email re NUMA soon – I’ve been pretty busy for the last few days…

hi… can you tell me whether I can use the dynamic sets in a scope statement. Currently I have a bulk of scope statements using a static set. But when I convert them to dynamic the processing is failed.

I am fairly new to MDX and having to learn my way as I go. Without your posts and the posts of other generous bloggers I would be getting nowhere fast!

Is the performance issue that is observed with named sets replicated when using calculated measures within other calculated measures or is this specific to named sets?

I have used calculated measures inside other calculated measures in order to obtain a running total against a non-time based dimension. In order to do so I have used the Sum function, the Rank function and the Head function. However, my MDX query takes some time to run. I wondered whether this is because of using calculated measure names within other calculated measures?

This is specific to named sets, and it has been ‘fixed’ in SSAS 2012 too, so this post is only relevant to 2008R2 and earlier versions.

Your performance problem is more likely to be related to the functions you’re using in your MDX, or the way you’re writing the calculation. Drop me an email offline (my contact details can be found at http://www.crossjoin.co.uk) and I can take a look at your code if you want.

Working in a cube with financial data, I was just about to define some named sets containing members from the Account dimension. I thought this would make sense since it would make the code look clearer in the calculated measures I am about to define (which would reference the named sets). I was also thinking that this was a good practice and maybe would speed things up as well!

But as I understand it from your post, you do not recommend doing this? Performance wise it is better to not use named sets but instead define the sets within the calcuated measures themselves?

I am a beginner in MDX and working in SSAS 2008R2. Do you happen to have a link with information how this issue has been “fixed” in the SSAS 2012 version?

Follow Blog via Email

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 7,729 other followers

Public Power BI and SSAS Training Courses

I'm running several SSAS, MDX and Power BI-related training courses through Technitrain in 2017. Check out the Technitrain course catalogue for full details, and to see other upcoming courses from the likes of Alberto Ferrari, Andy Leonard, Allan Hirt and Alex Yates.

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk