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
sum(
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
*
[Promotion].[Promotion].[Promotion].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.
Chris, those results are very encouraging – at SQL PASS Denver\’07, it was mentioned that there\’d be less need to define NON_EMPTY_BEHAVIOR explicitly in Katmai. Any detailed info/guidelines on when it may still be called for?
– Deepak
Hi Deepak, I guess it\’s still going to be needed in the cases not covered in the BOL topic I mentioned here: http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1502.entry
I would\’ve liked to deprecate NEB completely in AS2008, but it won\’t happened. My reasoning is simple: For every correct use of NEB, I see at least 10 incorrect uses. However, in AS2005 there are plenty of cases where the only optimization possible is to define NEB. But in AS2008, it will be extremely rare where engine won\’t be able to figure out NEB on its own, and will need hint from the user. In fact, I challenge you to come up with such a case (barring the obvious tricks with data dependent IIF\’s)
On a closely-related note the improvements in YTD engine behavior will be very apparent in calcs created by the time intelligence wizard. This is where we will get the most "bang for the buck" (as opposed to custom-written MDX) These Katmai improvements in YTD are loooooooong overdue.
Even today (SP2 and all) my SQL2000 (AS2K) YTD queries kick the pants off of SQL2005 (SSAS).
The glaring problem with YTD queries in SSAS is that they never ran in time comparible to the combined "current period" queries.
I added my 2cents a while ago in the Katmai MSDN forums:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2264394&SiteID=1