Chris Webb's BI Blog

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

The Dangers of Non_Empty_Behavior

with 7 comments

One thing I see quite often when I’m reviewing MDX is the incorrect use of the Non_Empty_Behavior property. 99% of the time it’s set without the developer really knowing what it does, and luckily in most cases it makes no difference to performance or what the calculation returns. However… in this post I’m going to argue that you should not use it unless you really know what you’re doing and you can prove that it’s beneficial – because if it’s set incorrectly it can sometimes mean you get incorrect results from your queries.

First of all, what does Non_Empty_Behavior actually do? Basically, it’s a hint to the SSAS Formula Engine that says that a calculation will return a null value if a given tuple also returns a null value. Moreover it’s a hint that may or may not be ignored. For example, if you had an MDX calculated measure that was defined as

CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasure] as [Measures].[Internet Sales Amount]*2;

It would be true to say that this calculated measure would always return a null value when the real measure [Measures].[Internet Sales Amount] returned null, so you would be able to say:

CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasure] as [Measures].[Internet Sales Amount]*2
, NON_EMPTY_BEHAVIOR=[Measures].[Internet Sales Amount];

The late, great Mosha covered its use in detail in a number of blog posts from a few years ago:
http://sqlblog.com/blogs/mosha/archive/2006/11/05/budget-variance-a-study-of-mdx-optimizations-evaluation-modes-and-non-empty-behavior.aspx
http://sqlblog.com/blogs/mosha/archive/2007/03/06/averages-ratios-division-by-zero-and-non-empty-behavior.aspx

These posts all refer to SSAS 2005, and from SSAS 2008 onwards a lot of work went on to ensure that most of the scenarios where Non_Empty_Behavior had been necessary to get good performance were optimised away. Certainly, in the simple example I showed above there would be no need to set Non_Empty_Behavior at all – the Formula Engine will already be able to optimise it as much as it can. This means that if you’re on SSAS 2005 then Non_Empty_Behavior can be very useful for tuning calculations, but if you are using SSAS 2008 or greater it’s highly likely that you do not need to use Non_Empty_Behavior at all. In fact, Mosha’s advice back in 2009 when he did a preconference seminar at the PASS Summit on SSAS performance tuning was summed up in these two slides:

image

image

I think the message here is pretty clear…

So, it probably won’t help your query performance, but why is it dangerous? Consider the following query:

WITH
MEMBER MEASURES.SomeTest AS
IIF(Now()>0, "Internet", "Reseller")
MEMBER MEASURES.Demo as
STRTOMEMBER("[Measures].[" + measures.sometest + " Sales Amount]")
SELECT
{[Measures].Demo} 
ON 0,
NON EMPTY
[Product].[Category].[Category].members
*
[Date].[Date].[Date].members
ON 1
FROM [Adventure Works]

This returns 1887 rows. However, if I set Non_Empty_Behavior incorrectly, like so:

WITH
MEMBER MEASURES.SomeTest AS
IIF(Now()>0, "Internet", "Reseller")
MEMBER MEASURES.Demo as
STRTOMEMBER("[Measures].[" + measures.sometest + " Sales Amount]")
, NON_EMPTY_BEHAVIOR=[Measures].[Reseller Sales Amount]
SELECT
{[Measures].Demo}
ON 0,
NON EMPTY
[Product].[Category].[Category].members
*
[Date].[Date].[Date].members
ON 1
FROM [Adventure Works]

The query now returns only 64 rows! By saying that the calculation will return a null value when Reseller Sales Amount is null – which is not the case – then rows that do in fact have values will get filtered out by the non empty clause. Clearly this is a bad thing, and also one that may be very hard to see happening when you are testing.

The example above was quite obviously wrong, and in fact it’s quite hard on SSAS 2012 to find a query where Non_Empty_Behavior returns incorrect results. More often I see calculations like this:

WITH
MEMBER MEASURES.PreviousSales AS
([Measures].[Internet Sales Amount], [Date].[Calendar].CURRENTMEMBER.PREVMEMBER)
, NON_EMPTY_BEHAVIOR=[Measures].[Internet Sales Amount]
SELECT {[Measures].[Internet Sales Amount], measures.PreviousSales} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

…where Non_Empty_Behavior is set incorrectly (the calculation returns the Internet Sales Amount for the previous time period, and it’s not true to say that the previous time period’s sales will be null if the current time period’s sales are null) but the query still returns the correct results. This is because, luckily, Non_Empty_Behavior is being ignored.

So, to sum up, if you are using SSAS 2008 or greater Non_Empty_Behavior may not solve your performance problem and may result in incorrect results. That is not to say that there are still one or two isolated scenarios where it can still be useful. Jorg Klein came across one fairly recently and blogged about it here:
http://sqlblog.com/blogs/jorg_klein/archive/2013/02/18/implement-ssas-md-cell-security-using-dimension-security-with-blazing-performance.aspx

But these scenarios are few and far between. Therefore don’t use it if you’re not sure what it does!

Written by Chris Webb

March 11, 2013 at 1:30 pm

7 Responses

Subscribe to comments with RSS.

  1. Gah! You scared me when you referred to Mosha as “late” – not cool.

    Kyle Hale

    March 11, 2013 at 2:04 pm

    • Sorry! For anyone else reading, he is of course alive and well, just gone to a better place…

      Chris Webb

      March 12, 2013 at 7:18 am

      • As of Aug 12, 2014, the word “late” is still there. What “Sorry!” is for?

        LeoStar

        August 12, 2014 at 5:35 pm

      • ‘Late’ is a way of referring to someone who is dead; Mosha is not dead, just gone to work at Google, so it was a kind of joke…

        Chris Webb

        August 12, 2014 at 7:09 pm

  2. Chris,

    I know you want to warn us about the inherent risks of misusing the NEB, but I don’t think the example you chose is a proper one.
    In fact, if you just add the second measure in the NEB, to suit your member’s definition:

    NON_EMPTY_BEHAVIOR={[Measures].[Reseller Sales Amount],[Measures].[Internet Sales Amount] }

    The results shown are correct AND the NEB furthermore improves the query performance quite a bit (as tested in MDX Studio).

    I agree with you on this: we have to be very careful when using it (but after all we talk about MDX, so…).
    However, I think the NEB is still very useful in lots of cases and should instead be properly explained and promoted.

    Romuald Coutaud

    April 12, 2013 at 10:01 am

    • Hi Romuald,

      I have to (respectfully) disagree with you here – I do not think NEB is useful in lots of cases, if you are on 2008 or greater. As I said I can only think of one example of where I have seen it make a difference in the last few years – 99% of the time you can get the same kind of performance improvement by rewriting your MDX. So I would rather that people write their MDX properly in the first place than use NEB without understanding what it does.

      Chris

      Chris Webb

      April 12, 2013 at 12:15 pm

      • Hi Chris. No patriotism here, don’t worry :) , but I have to agree with Romu on the usefulness of the NEB, even if I’ve been far of using it every day .Very far. I can’t recall exactly when, but i remember being suprised several times by the engine’s inability to behave correctly without it. Of course, it always happens with quite complex MDX members, but I think you can also reproduce it with Mosha’s “summator” pattern (Sum(Filter()) optimization) using mind-killing boolean expressions. At least we can reach consensus on the fact that it’s very rarely needed,but still needs to be properly explained, just in case :) Congrats again for everything you do.

        François Jehl

        April 12, 2013 at 2:37 pm


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

%d bloggers like this: