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

Formatting calculated measures
Format strings allow us to apply formatting to the raw numeric values computed in
our measures, as we saw in Chapter 5 , Handling Transactional-Level Data . They work
for calculated measures in exactly the same way as they work for real measures; in
some cases a calculated measure will inherit its format string from a real measure
used in its definition, but it's always safer to set the FORMAT_STRING property of a
calculated measure explicitly.
However, one important point does need to be made here: don't confuse the actual
value of a measure with its formatted value. It is very common to see calculated
members like this:
CREATE MEMBER CURRENTCUBE.Measures.PreviousPeriodGrowth AS
IIF (Measures.PreviousPeriodSales = 0,
'N/A',
(Measures.[Sales Amount] - Measures.PreviousPeriodSales)
/ Measures.PreviousPeriodSales),
FORMAT_STRING = "#,#.00%";
What is wrong with this calculation? At least four things:
• We are defining one of the return values for the measure as a string, and
Analysis Services is not designed to work well with strings - for example it
cannot cache them.
• If we use the measure's value in another calculation, it is much easier to
check for NULL values than for the N/A string. The N/A representation might
also change over time due to customer requests.
• Returning a string from this calculation will result in it performing
sub-optimally; on the other hand returning a NULL will allow Analysis
Services to evaluate the calculation much more efficiently.
• Most client tools try to filter out empty rows and columns from the data
they return by using NON EMPTY in their queries. However the preceding
calculation never returns an empty or NULL value; as a result, users might
find that their queries return an unexpectedly large number of cells
containing N/A .