locked
Standard IIF or alternative approach? RRS feed

  • Question

  • Hi all,

    I'm wondering if someone can help me in understanding the difference between a "standard" MDX IIF

    if (a>0, b, c)

    and the two different approach

    (a>0)*true*b +not(a>0)*true*c

    or

    (a>0)*true*b+(1+(a>0)) *c

    are there any differences in the query plans? If yes, how they impact perfomances?

    thanks in advance

    Norman


    Norman

    Thursday, May 7, 2015 1:55 PM

Answers

  • This is a matter of where you place the parenthesis. 

    There is no difference in performance between the 2 cases you present.

    In both those cases the whole expression is evaluated.


    Nosh Mernacaj, Identity Management Specialist

    Friday, May 8, 2015 1:24 PM

All replies

  • This is a math question applying the order of operations. 


    Nosh Mernacaj, Identity Management Specialist

    Thursday, May 7, 2015 4:06 PM
  • Hi Nosh,

    thank you for answering.

    I didn't quite get how that's a "math question applying the order of operations". Can you please explain me better? Does it have any impact on performances?

    Thanks


    Norman

    Friday, May 8, 2015 9:08 AM
  • This is a matter of where you place the parenthesis. 

    There is no difference in performance between the 2 cases you present.

    In both those cases the whole expression is evaluated.


    Nosh Mernacaj, Identity Management Specialist

    Friday, May 8, 2015 1:24 PM
  • Hi Norman,

    According to your description, you want to know the difference between standard IIF() function and your expression. Right?

    In MDX, for the IIF() function, it will pre-process all the three part: logic expression, true part and false part. If any part is invalid or throws error. The whole function will throw error before retrieving data. For the expression, both parts before and after "+" will be evaluated during runtime. So the order of operation can be a little different. The performance has almost no difference.

    For improving the performance, I suggest you always try to replace the IIF() function with SCOPE() statement.

    Best Regards,


    Simon Hou
    TechNet Community Support


    Monday, May 11, 2015 2:25 PM