Dimension-Based MDX Calculations - Microsoft SQL Server 2008 Analysis Services - page 221

Custom Member Formulas
When you define how to aggregate the values of members, you might have to do more
than to specify a trivial operator. You might have to use a more complex formula that
demands operators other than the simple add, subtract, or multiply—or even factor. In
such cases, you typically create one more column in the relational dimension table and
assign an MDX expression—a custom member formula—to members of the dimension.
To create a custom member formula in a dimension, you follow a process similar to the
one you use to create unary operators. You add a column to the dimension table in the
relational database. Each row corresponds to a dimension member; the value of that row
in the custom member formula column contains an MDX expression for that member.
(When you do not need an expression specified for that member, you can leave a NULL
value in the column.)
After you create your custom member formula column, you specify the
CustomRollupColumn property of the attribute to which you assign the custom member
formula. You can do this in BI Dev Studio or by manually issuing a DDL statement.
NOTE
For a parent-child dimension—and parent-child is the type of the dimension where cus-
tom member formulas are mostly used—you specify the CustomRollupColumn property
on the parent attribute.
For example, in the FoodMart Account dimension, the value of Gross Sales does not
come from the Budget cube. However, it does exist in our Unified Dimensional Model
(UDM)—in the Store Sales measure of the Sales measure group of the Warehouse and
Sales cube. When you are working in the Budget cube, you can retrieve this information
either by linking to the Sales measure group (for information about linked measure
groups, see Chapter 25, “Building Scalable Analysis Services Applications”) or by using the
LookupCube MDX function.
Even though the LookupCube MDX function is probably easier to understand, linked
measure groups yield better performance, and they fit better into the UDM. The UDM
brings all the information that your organization needs into one unit, so you can use the
same data in different cubes without replicating it. Therefore, we are going to have you
use the second approach and create a linked measure group, Sales , in the Budget cube.
Now that information about sales is accessible in the Budget cube, you can assign the
simple MDX formula ( [Measures].[Store Sales], [Account].[Accounts].[All] ) to the
Gross Sales member. In Figure 13.3, you can see this formula in the table row that
contains Gross Sales .