Using Non_Empty_Behavior with YTD Calculations: Katmai Update

I don’t know why I didn’t think of doing this, but after being prompted by Richard Tkachuk I ran the original query from my post of last week on setting non_empty_behavior for YTD calculations on Katmai CTP6:

with member measures.ytdsales as
periodstodate([Date].[Calendar].[Calendar Year],[Date].[Calendar].currentmember)
, [Measures].[Internet Sales Amount])
select {[Date].[Calendar].[Month].&[2003]&[12].children} on 0,
non empty
[Customer].[Customer Geography].[Customer].members
on 1
from [adventure works]
where(measures.ytdsales, [Product].[Subcategory].&[2])

And guess what – it ran in 7 seconds. That’s 7 seconds on a virtual machine on my laptop without that massive extra fact table, and that’s the same time as the optimised query running on AS2005 directly on my laptop with no VM slowing things down; remember that the query above was running in 1 minute 10 seconds on AS2005. That’s impressive.


It was SQLBits II on Saturday, and I just wanted to say thanks to everyone involved for making it yet another massive success: Tony, Simon, Martin, Allan, Darren, Clare Burgess from Microsoft, all the sponsors, all the speakers, all the helpers who volunteered to stuff bumpf into bags on Friday night, type in feedback and other such thankless tasks, and most of all the attendees. As for those of you who signed up, confirmed you were coming and then didn’t (unless you had a good excuse like the trains being messed up), well I’m giving you an extra hard stare right now – if fifteen more of you had turned up then I would have won the attendance sweepstake…

I took a couple of photos on the day but didn’t get around to uploading them onto my laptop before I got on the plane to Switzerland yesterday. When I get home I might post them to the SQLBits group I created on Facebook a few months ago and which has been pretty much dormant ever since; if anyone else has any photos that might be a good place to post them. Martin and Tony certainly took loads of pictures and I’ll see if I can get hold of theirs too.

Next stop DevWeek next week and then PASS Europe in a month’s time. If you going to either of those and see me, say hello!

Analysis Services and Trimming

Interesting post here on the PSS SQL Server Engineers blog on SQL Server working set trim problems: 

There’s also a related KB article here:
And a representative blog entry on the subject here:

I’m working with a customer where PSS think this is happening with Analysis Services. Certainly Perfmon shows some massive drops in memory usage for no obvious reason (ie the system is nowhere near the LowMemoryLimit). Something to look out for… I’ll post more details if/when I get them.

%d bloggers like this: