Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 149

We can easily check that the value is correctly computed, but at the Grand Total level,
we get two errors. The reason for this is that the Customer Geography hierarchy is at
the All level and so the denominator in the formula has no value because:

• There is no ancestor of the CurrentMember at the country level, as it is

already at the top level, so this causes the error in RatioOverCountry

• There is no parent at the top level in the hierarchy, so this causes the error

in RatioOverParent

Since we know that the ratio has no meaning at all at the top level, we can use a
SCOPE statement to overwrite the value at the top level with NULL .

SCOPE ([Customer].[Customer Geography].[All], Measures.RatioOverParent);
THIS = NULL;
END SCOPE;

SCOPE ([Customer].[Customer Geography].[All], Measures.RatioOverParent);
THIS = NULL;
END SCOPE;

The two SCOPE statements will provide a better user experience, clearing values that
should not be computed due to functional limitations on their meaning:

There is still a problem that should be addressed: what if the denominator evaluates
to zero in some cases? The evaluation of any number divided by zero will show a
value of infinity - which will look like an error to the user - and this is not a situation
we would like to happen. For this reason, it is always a good practice to check for a
zero denominator just before performing the division.