Montag, 21. Dezember 2009

Changing results with IIF hints

Recently we saw some strange effects with calculations using a conditional IIF-logic: the calculation should show the difference between two measures if the first measure is greater than 0:
IIF([Measures].[FirstMeasure]=0,NULL,
        ([Measures].[FirstMeasure]-[Measures].[SecondMeasure])
A query using the calculation returned the expected results for the [All]-node of a given hierarchy but wrong results for other levels. Since the given measures are involved in some scoped assignments we assumed an error in our logic, and were quite suprised when we discovered that a CASE-block returned the correct results:
CASE [Measures].[FirstMeasure] 
         WHEN 0 THEN NULL
         ELSE [Measures].[FirstMeasure]-[Measures].[SecondMeasure]
    END
Further research showed that IIF also brought the correct results when we used the eager hint in the calculation. The hint is a new feature in SSAS 2008 and documented here. The SQLCAT team explains the hint in the blog IIF Function Query Hints in SQL Server Analysis Services 2008:
the IIF mdx function is a commonly used expression that can be very costly to evaluate. It takes 3 arguments:

iif(< condition >, < then branch >, < else branch >)

Where the condition evaluates to true, the value from the “then branch” is used otherwise the “else branch” expression is used.

Note the term “used” – one or both branches may be evaluated even if its value is not used. It may be cheaper for the engine to evaluate the expression over the entire space and use it when needed (termed an eager plan) rather than chop up the space into a potentially enormous number of fragments and evaluate only where needed (a strict plan).

Apparently the different execution pathes can bring different results - and that is something I would not expect...

Keine Kommentare:

Kommentar veröffentlichen