NON_EMPTY_BEHAVIOR if NON EMPTY isn't used

furmangg

A cube I'm working on has lots of calculations. But none of the MDX queries in the reports use the NON EMPTY clause or the NonEmpty function. What I'm wondering is whether the optimizer or any other part of the Formula Engine uses NON_EMPTY_BEHAVIOR for anything else besides removing empty cells from the resultset.

If the Sum function is summing two calculated measures, will a NEB on those two calculated measures help the Sum function, for example That's the kind of thing I'm looking for... places where NEB is used other than removing entire cells from the resultset.

Mosha Pasumansky


Yes, NON_EMPTY_BEHAVIOR is used even if there are no NON EMPTY queries. Some more information about it here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx

furmangg

Mosha, thanks for the reply.

The takeaway from your blog regarding NEBs is that the multiplication operator (as in the * operator used for math, not for crossjoin) uses the NEB. Are there any others

What I was hoping for was sort of a list of places where NEB's are used unless that's an enormous list.

Mosha Pasumansky

Multiplication was one of the examples. Many other (but not all) MDX functions also take it into account. The exact list is not easy to build, because there are some other additional factors affecting whether or not NEB will be used.

The most important thing though, is to ensure that NEB is used correctly, before thinking about perf. Even in this forum, we routinely see people using NEB incorrectly, which can and does cause wrong results.