How to optimize a MDX aggregation functions containing "Exists"?

Category: sql server analysisservices


Butmah on Tue, 15 Jul 2014 18:55:10

I have the following calculated measure:

sum(([D Player].[Player Name].[All],
		exists([D Match].[Match Id].children,([D Player].[Player Name].currentmember,[Measures].[In Time]),"F Player In Match Stat" ))

Analyzing this calculated measure (the one with "nonempty") in MDX Studio shows "Function 'Exists' was used inside aggregation function - this disables block computation mode". Mosha Pasumansky spoke about this in one of his posts titled "Optimizing MDX aggregation functions" where he explains how to optimize MDX aggregation functions containing "Filter", "NonEmpty", and "Union", but he said he didn't have time to write about Exists, CrossJoin, Descendants, or EXISTING (he posted this in Oct. 2008 and the busy man didn't have time since that date :P )... so anyone knows an article that continues on what Mosha miss or forgot? how to optimize a MDX aggregation function containing "Exists"? what can I do to achieve the same as this calculated measure but in block mode not cell-by-cell mode ?


Christopher Webb on Fri, 18 Jul 2014 15:44:28

This is almost exactly the scenario that I was writing about in this post:

I think a scoped assignment would perform much better in this situation. 


Butmah on Sun, 20 Jul 2014 07:26:17

Thanks Chris, that was helpful, yet I couldn't employee the ideas in the link you provided to optimize my calculated measure that contains EXISTS, the following is the maximum I've reached:



    THIS=sum(([D Player].[Player Name].[All],exists([D Match].[Match Id].children,([D Player].[PlayerName].currentmember,[Measures].[In Time]) ,"F Player In Match Stat" ))



But this does not deffer from the normal calculated measure (the one in my first post), same execution time, same number of cells calculated, and same number of SE queries, but a bit less Memory Usage KB.

I also wand to remind about the hint I got when I analyze the query in MDX Studio  "Function 'Exists' was used inside aggregation function - this disables block computation mode".


Christopher Webb on Mon, 21 Jul 2014 13:07:03

OK, now I have a little more time I can reply properly. The point about the scoped assignment approach in my blog post is that Exists() and Sum() should no longer be necessary.

The first thing to point out is that, as the post states, you need to create a new real measure (it not a calculated measure as in your last reply) in your cube. If you call this new measure [A] then the scoped assignment would be *something* like this:


SCOPE([D Match].[Match Id].[Match Id].MEMBERS);

THIS = IIF([Measures].[In Time]=0, NULL, [Measures].[Goals]);



By the way, the advice that MDX Studio gives about tuning MDX may be ok if you're using SSAS 2008, but since MDX Studio has not been updated for a very long time this advice might be wrong for later versions of SSAS.



Butmah on Tue, 05 Aug 2014 07:25:41

Sorry for the late replay.

I didn't check if your last proposed solution is faster or not, but I'm sorry to say that it gave the wrong result, look at this:

Player Name

Players Team

Goals Player Scored with Team


Team's Goals in Player's Played Matches

Lionel Messi





Lionel Messi





The correct result should be like the green column. The last proposed solution in the red column.

If you look at the query in my first post you will find that the intention is to find the total number of goals a team scored in all matches a player participated in. So in the above example Messi scored 28 goals for Argentina (before the last world cup:) )  when the whole Argentinian team scored 110 goals (including Messi's goals) in those matches that Messi played even one minute in.

Christopher Webb on Tue, 05 Aug 2014 19:52:59

I did say it would only be *something* like the code I showed you. The important thing is not the exact code but the way you can use scoped assignments on real measures to aggregate the result of a calculation efficiently without using the Sum() function.


Butmah on Wed, 06 Aug 2014 04:58:14

Thanks Chris for the information about how Scope way can speed aggregations, you have added and illustrated it in clearer way than Mosha Pasumansky post "Optimizing MDX aggregation functions" :

Yet my problem is more granular, my problem is speeding aggregating functions containing Exists function, or replacing it with something faster, which what Mosha missed in the above link (if you scroll to the bottom in his link), and which I want your help in. Maybe I was not so clear in my first question ...I think I was :) ... please, if you have time, read it again.

So my question in another words: I recently knew that using "Exists" function inside aggregation function disables block computation mode, which slows things down, so with what I should replace an aggregation function that contains "Exists" function to speed things up and solve this disabling block computation mode problem?

Christopher Webb on Wed, 06 Aug 2014 08:34:55

No, I do understand what you're trying to do here, and I do think it is possible to rewrite the calculation to avoid Sum() and Exists() using the technique I linked to. All the Exists() function is doing is returning a set of members at the Match Id level of the Match Id hierarchy, and that can be replaced with a scope.