none
MDX Script Measure Treated the same for zero or empty

    Pregunta

  • I have the following MDX Script in a cube:

    SCOPE([Quality Compass Indicators].[Calculation Type].&[Quotient]);
            SCOPE([Quality Compass Indicators].[Component Indicator].[Component Indicator].MEMBERS);
                [Measures].[Performance]=
                    IIF([Measures].[Cases]=0
                        ,0
                        ,([Measures].[Incidents]* [Measures].[Rate Units])/[Measures].[Cases]);
                NON_EMPTY_BEHAVIOR([Measures].[Performance])=[Measures].[Cases];
            END SCOPE;
        END SCOPE

    I use the Non Empty behavior to eliminate any calculation at all if the Cases measure is null/empty.  However, if cases have a zero value, the performance measure should return zero.  However, even if the cases measure is empty, this calculation is still returning a zero when stepping through the MDX script.

    It seems as if the non empty behavior is not working as it should and the calculation is treating a zero or empty the same in the IIF statement of the Performance calculation.

    The Cases measure is a stored measure with null processing behavior set to Preserve.


    Jeff T Jones

    miércoles, 20 de junio de 2012 14:38

Respuestas

  • Hello Jeff,

    what you described is the expected behavior because in MDX null = 0 returns True. If you want to test for empty values, you have to add an IIF(isempty([YourMeasure]),NULL,...).

    What about NON_EMPTY_BEHAVIOR?

    When NON_EMPTY_BEHAVIOR is NULL it doesn't automatically implies that the calculation is forced to NULL. It depends on AS calculation plan.NON_EMPTY_BEHAVIOR has a different meaning.

    As reported in the SSAS2005perfGuide and SSAS2008PerfGuide 

    "When an expression’s NEB is defined, the author is guaranteeing that the result is null when the NEB is null and consequently the result set is not null when NEB is not null. This information is used internally by the Query Execution Engine to build the query plan."

    "In SQL Server 2005 Analysis Services, there were complex rules on how the property could be defined, when the engine used it or ignored it, and how the engine would use it. In SQL Server 2008 Analysis Services, the behavior of this property has changed:

    • It remains a guarantee that when NON_EMPTY_BEHAVIOR is null that the expression must also be null. (If this is not true, incorrect query results can still be returned.)
    • However, the reverse is not necessarily true; that is, the NON_EMPTY_BEHAVIOR expression can return non null when the original expression is null.
    • The engine will more often than not ignore this property and deduce the nonempty behavior of the expression on its own."

    HTH

    Bye


    Norman

    • Marcado como respuesta Jeff Jones sábado, 23 de junio de 2012 14:25
    miércoles, 20 de junio de 2012 15:19

Todas las respuestas

  • Hello Jeff,

    what you described is the expected behavior because in MDX null = 0 returns True. If you want to test for empty values, you have to add an IIF(isempty([YourMeasure]),NULL,...).

    What about NON_EMPTY_BEHAVIOR?

    When NON_EMPTY_BEHAVIOR is NULL it doesn't automatically implies that the calculation is forced to NULL. It depends on AS calculation plan.NON_EMPTY_BEHAVIOR has a different meaning.

    As reported in the SSAS2005perfGuide and SSAS2008PerfGuide 

    "When an expression’s NEB is defined, the author is guaranteeing that the result is null when the NEB is null and consequently the result set is not null when NEB is not null. This information is used internally by the Query Execution Engine to build the query plan."

    "In SQL Server 2005 Analysis Services, there were complex rules on how the property could be defined, when the engine used it or ignored it, and how the engine would use it. In SQL Server 2008 Analysis Services, the behavior of this property has changed:

    • It remains a guarantee that when NON_EMPTY_BEHAVIOR is null that the expression must also be null. (If this is not true, incorrect query results can still be returned.)
    • However, the reverse is not necessarily true; that is, the NON_EMPTY_BEHAVIOR expression can return non null when the original expression is null.
    • The engine will more often than not ignore this property and deduce the nonempty behavior of the expression on its own."

    HTH

    Bye


    Norman

    • Marcado como respuesta Jeff Jones sábado, 23 de junio de 2012 14:25
    miércoles, 20 de junio de 2012 15:19
  • I would get rid of NON_EMPTY_BEHAVIOR. It's not recommended anymore and I think it has been deprecated.

    The key piece of info that will help you is that if you compare empty to 0 in MDX, it returns true. (That's different than T-SQL.) So if you want to distinguish empty and 0, then you need the following code:

                [Measures].[Performance]=
                  IIF(IsEmpty([Measures].[Cases])
                    ,null
                    ,IIF([Measures].[Cases]=0
                        ,0
                        ,([Measures].[Incidents]* [Measures].[Rate Units])/[Measures].[Cases])
                  );

    This pattern is atypical, though. For others reading this thread, the typical pattern is IIF(Denominator=0,null,Numerator/Denominator). That pattern will ensure the calculation returns null anytime the Denominator is zero or empty (null).


    http://artisconsulting.com/Blogs/GregGalloway

    viernes, 22 de junio de 2012 17:27
  • Thank you both for the quick response. The business requirement is to treat zero and null differently. I could do a nested IF statement or a Case statement but I am trying to avoid throwing the calculation engine into cell calculation mode and keep it in block calc mode.

    I can take an alternate approach though.  I have been creating hidden measures that return a null value when I want the desired outcome of a multi-condition expression to be null by multiplying the expression by the hidden measure. The hidden measure takes on one of the conditions.  Since a null * anything is a null I get the desired outcome while retaining block calculation mode.

    Jeff T Jones

    sábado, 23 de junio de 2012 14:25