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

Always work on the basis that the value of a measure should be handled by the
main formula, while the visual representation of the value should be handled by
the FORMAT_STRING property. A much better way to define the preceding calculated
member is:
CREATE MEMBER CURRENTCUBE.Measures.PreviousPeriodGrowth AS
IIF (Measures.PreviousPeriodSales = 0,
NULL,
(Measures.[Sales Amount] - Measures.PreviousPeriodSales)
/ Measures.PreviousPeriodSales),
FORMAT_STRING = "#,#.00%;-#,#.00%;;\N\/\A";
The formula here returns a NULL value when appropriate, and the format string then
formats the NULL value as the string N/A (although, as we noted in Chapter 4 , Measures
and Measure Groups , this won't work in Excel 2007). The users see the same thing as
before but this second approach does not suffer from the four problems listed earlier.
Calculation dimensions
All the calculations we have described so far have been calculated measures - they
have resulted in a new member appearing on the Measures dimension to display the
result of our calculation. Once created, these calculated measures can be used just
like any other measure and even used in the definition of other calculated measures.
In some circumstances, however, calculated measures can be rather inflexible.
One example of this is time series calculations. If we want to let our users see,
for example, the year-to-date sum of the Sales Amount measure, we can use the
technique explained earlier and create a Sales Amount YTD measure. It will be soon
clear though that users will want to see the year-to-date sum not only for the Sales
Amount measure but on many others. We can define a new calculated measure for
each real measure where the YTD function might be useful, but doing so, we will
soon add too many measures to our cube, making it harder for the user to find the
measure they need and making maintenance of the MDX very time-consuming. We
need a more powerful approach.
The technique we're going to describe here is that of creating a Calculation
Dimension (also known as Time Utility Dimensions or Shell Dimensions); while it
may be conceptually difficult, it's an effective way of solving the problems we've
just outlined and has been used successfully in the Analysis Services community
for many years. It involves creating a new dimension, or a new hierarchy on an
existing dimension, in our cube specifically for the purpose of holding calculations.
Moreover, because our calculations are no longer on the Measures dimension, when
we select one of them in a query, it will be applied to all of the measures in the cube.
This means, for example, we could define our year-to-date calculation just once and
it would work for all measures.