Now I don’t know if I’ve blogged about this before – apologies if I have, but I can’t find any trace of it on Google and since this is a fairly common performance-related problem it deserves to be mentioned. It’s not rocket science MDX either but sometimes I think I focus too much on fun but obscure problems at the expense of real-world scenarios…
As you know, Reporting Services only allows you to have members from the Measures dimension on columns in a dataset. This is rubbish at the best of times since you need to use a matrix control to pivot your results, but it can sometimes present a performance problem. Consider the following query:
select
{([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2004]),
([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2003]),
([Measures].[Internet Tax Amount], [Date].[Calendar Year].&[2004])}
on 0,
non empty
[Customer].[Customer].[Customer].members
*
[Product].[Product].[Product].members
on 1
from [Adventure Works]
On the AdventureWorks Simple db (kind of like Adventure Works but with some things removed – you can download it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=975c5bb2-8207-4b4e-be7c-06ac86e24c13&DisplayLang=en) this runs in around 11 seconds and returns 55361*3=166083 cells on a cold cache. It’s typical of the kind of large reports people for some reason like to run in SSRS but the fact that you have an asymmetric set on columns becomes a problem when you try to convert it for use with a matrix: when you pivot Year to appear on rows you are automatically returning extra data you didn’t want to use in your query, in this case values for Internet Tax Amount and 2003:
select
{[Measures].[Internet Sales Amount],[Measures].[Internet Tax Amount]}
on 0,
non empty
[Customer].[Customer].[Customer].members
*
[Product].[Product].[Product].members
*
{[Date].[Calendar Year].&[2003],[Date].[Calendar Year].&[2004]}
on 1
from [Adventure Works]
In this case it doesn’t make much of a difference, but it can result in a big increase in the size of the resultset and a corresponding increase in query time especially if you have measures from different measure groups and your new query touches extra partitions as a result of the pivot.
You may think that the answer is to create a calculated measure to display the value of Internet Sales Amount for 2003 and put that on columns along with the regular Internet Sales Amount and Internet Tax Amount measures, and put the Year in the Where clause:
with member measures.inetsalesprevyear as
([Measures].[Internet Sales Amount],
[Date].[Calendar Year].currentmember.prevmember)
select
{[Measures].[Internet Sales Amount],
measures.inetsalesprevyear,
[Measures].[Internet Tax Amount]}
on 0,
non empty
[Customer].[Customer].[Customer].members
*
[Product].[Product].[Product].members
on 1
from [Adventure Works]
where([Date].[Calendar Year].&[2004])
But if you try running this you’ll find that performance is much, much worse: in fact on my laptop I killed the above query after it had run for more than a minute. The problem is that AS can’t optimise this query in the same way as it has the previous queries, and a quick look in Perfmon confirms this as you can see the Total Cells Calculated counter going through the roof when it runs.
What can we do? You might think Non_Empty_Behavior is the way to go but I’ve not got it to make any difference even with a hard-coded tuple; in fact we what we need to do is optimise the Non Empty rather than the calculated measure. Instead of looking for non empty rows where one of the columns represents a calculated measure, we can use the NonEmpty function to be specific about what rows we want to appear:
with
member measures.inetsalesprevyear as
([Measures].[Internet Sales Amount],
[Date].[Calendar Year].&[2003])
select
{[Measures].[Internet Sales Amount],
measures.inetsalesprevyear,
[Measures].[Internet Tax Amount]}
on 0,
nonempty(
[Customer].[Customer].[Customer].members
*
[Product].[Product].[Product].members
, {([Measures].[Internet Sales Amount],
[Date].[Calendar Year].&[2004].prevmember)
, ([Measures].[Internet Sales Amount],
[Date].[Calendar Year].&[2004])
, ([Measures].[Internet Tax Amount],
[Date].[Calendar Year].&[2004])})
on 1
from [Adventure Works]
where([Date].[Calendar Year].&[2004])
What I’m doing here is sticking with the same calculated measure but in the filter set for NonEmpty using the same set of tuples that I originally wanted on columns; the calculated measure is then only evaluated when we know that the row contains a non empty value for one of the three columns. Performance is exactly the same as the original query as far as I can see, and it can all be parameterised nicely.
Now if Reporting Services had proper support for MDX we wouldn’t need to go into all this, but don’t get me started on that topic again…
