Chris Webb's BI Blog

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

NON_EMPTY_BEHAVIOR and sets

with 5 comments

Late last year, in the middle of an email correspondence with Mosha, I included the following piece of an MDX Script containing a calculated member definition generated by BIDS when working in form view:

CREATE MEMBER
CURRENTCUBE.[MEASURES].[Demo]
AS [Measures].[Sales]*2,
FORMAT_STRING = "#,#",
NON_EMPTY_BEHAVIOR = { [Sales] },
VISIBLE = 1  ;

Mosha commented that putting braces round the measure [Sales] in the NON_EMPTY_BEHAVIOR property in this case would ‘do more harm than good’ and, although he didn’t expand on why this was (a good subject for a blog entry Mosha?) ever since then I’ve dutifully removed the braces that BIDS puts in but never noticed much impact. Until yesterday, when a query I was tuning which was running in 45 seconds started running in 8 seconds simply as a result of doing this. Hmmm…

While we’re here, it’s a personal hobby horse of mine to insist on using full unique names in all MDX calculations. So, in this case, I would use [Measures].[Sales] rather than [Sales]. Not only is it more readable but if you’re using dimension security you might run into problems if you don’t, as the following thread on the MSDN Forum demonstrates:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=753483&SiteID=1

So, just to be clear, if you’re using NON_EMPTY_BEHAVIOR and have created your calculated member in form view, always be sure to change it from the format above to be something like this:

… NON_EMPTY_BEHAVIOR = [Measures].[Sales] …

Written by Chris Webb

October 26, 2006 at 11:06 pm

Posted in Uncategorized

5 Responses

Subscribe to comments with RSS.

  1. Sorry to ruin this post, but with SP2 builds – it won\’t matter anymore. I always thought it was ridiculous that such a powerful engine that AS2005 is couldn\’t deal with such a simple matter as curly braces. I am glad to report that this is fixed with SP2.
    As for the explanation on what\’s really going on – the NON_EMPTY_BEHAVIOR is a subject that I am afraid to touch. It is single most misunderstood feature of AS, but at the same time is also the most powerful optimization when applied properly. Even mighty NonEmptyCrossJoin cannot rival it neither on degree of confusion around it nor on the potential performance impact. NON_EMPTY_BEHAVIOR in AS2005 does much much more then merely improve NON EMPTY clause. It can do wonders to calculations, aggregations etc. But it is very difficult to explain. Well, maybe one day somebody will do it…

    Mosha

    October 27, 2006 at 12:48 am

  2. And also, BTW, as this (http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx) my blog explained, you don\’t really need to specify NON_EMPTY_BEHAVIOR for multiplication of Sales*2 as long as you write it this way, and don\’t use much worse 2*Sales expression !

    Mosha

    October 27, 2006 at 12:52 am

  3. I\’m pleased to hear it\’s been fixed! However, I do wonder what the point is of a bit of functionality that\’s so complex that no-one who uses the product can hope to understand it…

    Chris

    October 30, 2006 at 2:16 pm

  4. You are right. Even though it is a complex issue, it is also an important one. So I decided to write an explanation. Not sure if it will make things more clear or less, but here is it: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx

    Mosha

    November 5, 2006 at 10:08 pm

  5. hmm… it’s interesting

    bloger7791

    February 7, 2012 at 5:42 am


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,073 other followers

%d bloggers like this: