Chris Webb's BI Blog

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

Using Non_Empty_Behavior with YTD Calculations: Katmai Update

with 4 comments

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.

Written by Chris Webb

March 9, 2008 at 12:01 am

Posted in Uncategorized

4 Responses

Subscribe to comments with RSS.

  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

    Deepak

    March 11, 2008 at 12:07 am

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

    Chris

    March 12, 2008 at 11:19 pm

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

    Mosha

    March 15, 2008 at 3:24 am

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

     

    Unknown

    March 21, 2008 at 6:05 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,190 other followers

%d bloggers like this: