Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for October 2007

Asymmetric Sets on Columns in Reporting Services

leave a comment »

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…

Written by Chris Webb

October 10, 2007 at 7:04 pm

Posted in MDX

Precision Considerations for Analysis Services white paper

leave a comment »

Another week, another white paper from the SQLCat team (do these guys ever sleep?) on the topic of inconsistent numbers being returned from different queries against the same cube as a result of precision issues:
It’s not as bad as it sounds, but definitely something to read before you get hauled up in front of the end users when they notice this! 

Written by Chris Webb

October 10, 2007 at 6:59 pm

Posted in Analysis Services

Maximising Attribute Relationship Aggregations in Changing Dimensions article

with one comment

Erik Veerman has written a nice article for the Solid Quality Mentors (who I also do training for) newsletter on attribute relationships and the RelationshipType property:
 
As he says on his blog, if you want to see more articles like this you can sign up for the Solid Quality newsletter here:

Written by Chris Webb

October 9, 2007 at 5:40 pm

Posted in Analysis Services

SAP to buy Business Objects

with 2 comments

If you care about this, I’m sure you’ve already heard that SAP have agreed to buy Business Objects:
The general consensus seems to be that this is bad news for Business Objects customers and that the last few remaining independents (eg Cognos) will go the same way too. Will Microsoft be buying anyone? MS aways gets mentioned as a potential buyer in these kind of situations (see for example Mark Rittman) but I just don’t see it happening myself – it would be completely out of character. IBM on the other hand…

Written by Chris Webb

October 8, 2007 at 6:13 pm

Posted in On the internet

SQLBits

leave a comment »

Today was the SQLBits conference in Reading and it went really well. We had 321 attendees, some great sessions, loads of freebies (on top of which everyone got a £30 book voucher, as well as the free evening event) and everyone seemed to be having a good time. Thanks are due to Care Parr of Microsoft for her help organising it, Tony, Simon and Martin for doing the majority of the leg work, and the sponsors. I think all the slide decks will be up on www.sqlbits.com early next week and there might be some photos posted there soon too.

Hopefully the bigger event we’ve got planned for June/July next year will be equally successful…

Written by Chris Webb

October 6, 2007 at 10:42 pm

Posted in Events

Follow

Get every new post delivered to your Inbox.

Join 3,189 other followers