MDX query is very slow and returns memory exception in SSRS

Question

I'm trying to get a detailed list of all records where my total amount is more than 100k from the following Multidimensional Expressions (MDX) query:

with member [measures].[total] as 
[Measures].[m1] + [Measures].[m2] + [Measures].[m3]
select non empty
[measures].[total] on columns, 
non empty filter ([dim1].[h1].allmembers
* [dim1].[h2].allmembers
* [Loss Date].[Date].[Year].allmembers
* [Dim1].[h3].allmembers 
, [measures].[total]>100000 and [Measures].[Open File Count]>0) on rows
from [Monthly Summary]
where ([1 Date - Month End].[Month End Date].[Month].&[20120331])

Although I get fast results from creating a stored procedure and the final result is less than 1000 rows, my MDX query runs for ever in SSMS and in SSRS returns a memory exception. Any idea on how to optimize or enhance it?

Answer 1

You could use Having instead of Filter, since it is applied after the Non Empty and you may get better performance (see this excellent blog post by Chris Webb). This would be the new version of the query:

with member [measures].[total] as 
[Measures].[m1] + [Measures].[m2] + [Measures].[m3]
select non empty
[measures].[total] on columns, 
non empty 
[dim1].[h1].allmembers
* [dim1].[h2].allmembers
* [Loss Date].[Date].[Year].allmembers
* [Dim1].[h3].allmembers 
having [measures].[total]>100000 and [Measures].[Open File Count]>0 on rows
from [Monthly Summary]
where ([1 Date - Month End].[Month End Date].[Month].&[20120331])