Standard IIF or alternative approach?

• 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?

Norman

Norman

Thursday, May 7, 2015 1:55 PM

• 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,

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