none
non empty behavior not working...:(

    Question

  • Hi

    I can't get the non_empty_behavior working....:(

    I have a simple calculation 10/[Measure].[B]

    In the calculations designer (in BIDS) I set the Non-empty behavior property to [Measure].[B]

    but it simply does nothing...when [Measure].[B] is empty the devision is executed and i get error instead of nothing..I checked wether [Measure].[B] is realy empty using iif(isempty([Measure].[B]) = true,1,0) and i do get 1 (meening it is empty...) Any idea where to look next??? TIA Rea

    Thursday, February 07, 2013 11:20 AM

Answers

  • I guess the important thing here is that it is just a hint for performance optimisation - it doesn't force the SSAS formula engine to do anything, the hint may be ignored in certain cases, and so it can't be used to try to control the values a calculation returns.

    So yes, you always need to use IIF() to prevent division by zero and division by null, and this has always been the case, nothing has changed. Like I said, though, it's better to use

    iif(Measures.B=0, null, 10/Measures.B)

    instead of

    iif((isempty(Measures.B), null, 10/Measures.B)

    Because the first tests for division by zero and division by null, while the second only tests for division by null.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    • Marked as answer by reapeleg Thursday, February 07, 2013 2:00 PM
    Thursday, February 07, 2013 1:32 PM
    Moderator

All replies

  • Maybe this would give a clue:

    Non empty behavior seems to work fine on future time periods where no data exists at all in the cube.

    I don't understand why it would not work on historical data where some selection in the cube returns no data...

    Thursday, February 07, 2013 11:52 AM
  • This is because non_empty_behavior doesn't do what you think it does!

    Non_empty_behavior is an optimisation hint to the SSAS formula engine, that says your calculation will return null if a given non-calculated measure is null. What you want to do to avoid the error is check for division by zero and division by null inside your calculation definition:

    iif(Measures.B=0, null, 10/Measures.B)

    Incidentally, it's enough to check for division by zero in MDX because zeroes and nulls are treated as equal in scenarios such as this.

    Finally, if you are using SSAS 2008 or greater, you probably should not be using non_empty_behavior at all. It is very difficult to set correctly, and if you set it incorrectly you risk getting inconsistent results from your cube; and from 2008 on the SSAS formula engine will automatically optimise 99% of all the calculations that non_empty_behavior can be set for anyway, making it unnecessary. My advice is not to use it unless you really, really know what you are doing...

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, February 07, 2013 12:00 PM
    Moderator
  • Thanks Christopher!

    Following your words "...Non_empty_behavior is an optimisation hint to the SSAS formula engine, that says your calculation will return null if a given non-calculated measure is null..."

    This is exactly what I am hopping to see: when Measure.B is empty then 10/measure.B will be empty...(that is not calculated at all!).

    I guess I am still missing something here...

    You also recommanded not to use NEB at all.

    So we are back to the old 2000 mechanism of preventing calculations by explicitly writting iif(isempty([measure].B)=true,null...)??

    Thanks again!

    Rea

    Thursday, February 07, 2013 1:24 PM
  • I guess the important thing here is that it is just a hint for performance optimisation - it doesn't force the SSAS formula engine to do anything, the hint may be ignored in certain cases, and so it can't be used to try to control the values a calculation returns.

    So yes, you always need to use IIF() to prevent division by zero and division by null, and this has always been the case, nothing has changed. Like I said, though, it's better to use

    iif(Measures.B=0, null, 10/Measures.B)

    instead of

    iif((isempty(Measures.B), null, 10/Measures.B)

    Because the first tests for division by zero and division by null, while the second only tests for division by null.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    • Marked as answer by reapeleg Thursday, February 07, 2013 2:00 PM
    Thursday, February 07, 2013 1:32 PM
    Moderator
  • "you always need to use IIF() to prevent division by zero and division by null, and this has always been the case, nothing has changed"

    Thankfully in SSAS2012 there is now a safe DIVIDE() function!

    Tuesday, November 12, 2013 11:51 PM