NON_EMPTY_BEHAVIOR if NON EMPTY isn't used

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.

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

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.

|||

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.

non_empty_behavior advice

Hello!

I'm new in the SSAS2005 world and was wondering if the following MDX script was good, especialy the non_empty_behavior part:

SCOPE ([ACCOUNTS].&[PU_MOI]);
This = Avg(
Descendants([ENTITIES].CurrentMember, , LEAVES) * Descendants([PRODUCTS].CurrentMember, , LEAVES)
);
Non_Empty_Behavior = [ACCOUNTS].&[PU_MOI];
END SCOPE;

As you can see, I want to display Averages for a special member of my ACCOUNTS dim and the same member is used in the non_empty_behavior property (if there is no data at an agregated level, there is not data on the leaves levels and so no need to calculate averages, at least I think so . Is it right ?

I also tried to code this with the new EXISTING function but it doesn't seem to work
SCOPE ([ACCOUNTS].&[PU_MOI]);
This = Avg(
EXISTING Leaves([ENTITIES]) * EXISTING Leaves([PRODUCTS])
);
Non_Empty_Behavior = [ACCOUNTS].&[PU_MOI];
END SCOPE;

Should I use EXISTING that way ?

Thx!

Unless you plan to support scenarios where there is multiselect on Entities or Products - leave EXISTING out of it - the Descendants function will work just fine. If you do need to support multiselect, than you can use EXISTING, but please don't use Leaves() function in the right-hand side of assignment. You can replace it with the key level of appropriate dimension. It would also help if Entities and Products were not parent-child dimensions.

Use of Non_Empty_Behavior here is probably OK, but only if you don't have some other calculations (or even unary operators) which can interfere with how PU_MOI is computed.

|||

Thanks for your answer Mosha!

In fact, there may be a multi-select on Entities but the Average value wouldn't be of interest in that way so.. I also forgot to mention but Entities and Products are parent-child dims, so if I use the EXISTING formula, I have to use Leaves too, or it there another way ?

By the way, speaking of multi-selects, when retrieving coordinates of a cell from a MDX query result with a muti-select on Entities (from an MDX Query generated the old fashionned way, with an ugly Aggregate() function), how can I check that the Entity selected is not a real one but a calculation ? Maybe a Filter([Entities].Members) against the currently selected Entity ? Or is there a 'IsCalculatedMember' like function I could use ?

|||

The fact that Entities and Products are parent-child will cause issues, but you still don't need to use Leaves function. Descendants with LEAVES flag is good enough here. For more differences between the two, check out this blog: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

> By the way, speaking of multi-selects, when retrieving coordinates of a cell from a MDX query result with a muti-select on Entities (from an MDX Query generated the old fashionned way, with an ugly Aggregate() function), how can I check that the Entity selected is not a real one but a calculation ?

Sorry, but I didn't understand the question.