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?

thanks in advance

Norman

---

Norman

Answer 1

This is a math question applying the order of operations. 

---

Nosh Mernacaj, Identity Management Specialist

Answer 2

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

Answer 3

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

Answer 4

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