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] …