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] …
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…
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 !
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…
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
hmm… it’s interesting