CODEWITHSUNDEEP
sql-serverssrs-2008ssasmdx
Frank Goortani
Frank Goortani

Reputation: 1435

MDX query is very slow and returns memory exception in SSRS

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?

Upvotes: 1

Views: 5050

Answers (3)

Stacia
Stacia

Reputation: 7218

I would recommend a couple of changes.

First, do you really want the All member of each of your dimensions to be returned in the query? They will be included if they meet the condition in the filter. Also, I have found changing a where clause to a subselect to perform better in some cases. You need to test to see if it changes performance. Next, you can reduce the number of members that you're filtering by using a NonEmpty function first, by putting it inside the Filter function. Also, in some cases, using a polymorphic operator (*) performs worse than using a CrossJoin function or creating a tuple set of your members. The NON EMPTY on columns is unnecessary when you have only one item on the axis. I've combined all of these suggestions below:

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

See this for a bit of explanation on the NON EMPTY versus NonEmpty: http://blogs.msdn.com/b/karang/archive/2011/11/16/mdx-nonempty-v-s-nonempty.aspx. In some cases putting a NonEmpty function inside a Filter function can produce a performance hit, sometimes not - you need to test.

The problem might be in a dimension or cube design (storage engine problem) and not in the query (formula engine problem). You can diagnose using the techniques here: http://www.microsoft.com/en-us/download/details.aspx?id=661. The whitepaper was written for SSAS 2005, but still applies to later versions of SSAS.

Upvotes: 2

Meff
Meff

Reputation: 5999

To reduce memory, order your dimensions.

Instead of:

[dim1].[h1].allmembers
* [dim1].[h2].allmembers
* [Loss Date].[Date].[Year].allmembers
* [Dim1].[h3].allmembers

Use

[dim1].[h1].allmembers
* [dim1].[h2].allmembers
* [Dim1].[h3].allmembers
* [Loss Date].[Date].[Year].allmembers

Under the covers, SSAS will inner join the dim1 members and outer join the Loss Date members.

Upvotes: 1

Santiago Cepas
Santiago Cepas

Reputation: 4094

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])

Upvotes: 2

Related Questions