Measures and Measure Groups - Expert Cube Development with SSAS Multidimensional Models - page 94

We can also create another column to hold property values that can be applied
to each member on the hierarchy too, which then needs to be assigned in the
CustomRollupPropertiesColumn property. The contents of this column takes
the form of a comma-delimited list of property values, just as you would use at
the end of a calculated member definition; for example, if a member had the value
FORMAT_STRING='$#,#.00', BACK_COLOR=RGB(255,0,0) associated with it in
the dimension table, then all values for that measure would have FormatString
$#,#.00 applied to them, and they would be displayed with a red cell background.

Custom Member Formulas offer something very similar to what you can do
with MDX Script assignments, so let's look at the advantages and disadvantages
of using them:

• In terms of query performance, Custom Member Formulas and MDX Script

assignments are the same.
As dimension calculations, Custom Member Formulas differ from MDX
Script assignments in one important respect: they use the "Closest Pass
Wins" rule rather than the "Last Pass Wins" rule when a cell's value can
be calculated in two different ways from two different calculations. A
description of how these rules work is outside the scope of this topic
(see the topic Microsoft SQL Server Analysis Services 2008 Unleashed , Gorbach ,
Berger , and Melomed , Chapter 13 , for this), but in some respects this means
that Custom Member Formulas are easier to work with. You don't have the
flexibility you have with MDX Script assignments, but you can always be
sure that a member's value will be calculated using the formula you specify
and not be accidentally overwritten by another assignment.

• Using Custom Member Formulas means the dimension becomes, in
some respects, self-documenting. If the formulas are made visible to
the end user somehow, perhaps by creating another attribute with
AttributeHierarchyEnabled set to False in the formula column, and
the formulas are relatively simple, then users should be able to view and
understand them.
• If a dimension with Custom Member Formulas is shared across multiple cubes,
then these calculations are automatically applied to all of the cubes. With MDX
Script assignments, the code would have to be duplicated across cubes.
• The major drawback of using Custom Member Formulas is that if you need
to edit a calculation, you need to edit a value in a relational table. Not only is
this inconvenient, it makes debugging calculations much more difficult and
it means that your MDX code can be split between two places: the dimension
table and the MDX Script.