Checking KPI value for divide-by-zero

We've got some potentially complex KPI value definitions, which can involve division of two cell values. I want to put in a check to make sure we don't do a divide-by-0. If I do, is there any performance implication due to the calculation of the denomerator happening twice (once in the first IIf clause, again in the actual value). For example, if I have a value that's essentially:

[Measure1] / (Aggregate([A Set], [Measure 2])

and I want to make it

IIf( (Aggregate([A Set], [Measure 2]) > 0, [Measure1] / (Aggregate([A Set], [Measure 2]), Null)

Is there a performance hit for doing this? Does the aggregate happen twice? Is there a smarter way of doing this check?

Yes, Aggregate will happen twice. The best way to handle it is

CREATE HIDDEN AggASet = Aggregate([A Set], [Measure 2])

and then you can say

IIf( AggASet > 0, [Measure1] / AggASet, Null)

|||Thanks Mosha. But can I specify a composite expression like that in a KPI value expression?|||No - the CREATE HIDDEN statement should be somewhere inside the MDX Script.|||Shoot, that's what I was afraid you were going to say. The problem is that these KPIs are being generated by a GUI tool, and so far we've done everything inside the KPI definition itself, without having to muck with the MDX script. Changing that is going to involve some major code changes, sadly.

Is there any other way to handle this condition gracefully?|||

Your choices as I see them are:

1. Do nothing - take perf hit, and hope that in the next version query optimizer will automatically detect such expressions and will do the "right thing"

2. Change your logic to add hidden calculated measures to MDX Script

3. Don't check for 0 in denominator and let users see 1.#INF when such division occurs

4. Create another KPI which will hold the Aggregate expression and reference its through KPIValue function from your KPI expression. This way you won't need to change MDX Script. I am not sure, however, if it is possible to create hidden KPIs. So the drawback is that you will get an extra 'junk' KPI.

5. Something else ?

HTH,

Mosha (http://www.mosha.com/msolap)