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.

4 thoughts on “Using Non_Empty_Behavior with YTD Calculations: Katmai Update

  1. 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

  2. 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)

  3. 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:

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.