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

highest granularities (lower levels of the dimensions) to produce total values for the lowest
granularities (top levels of the dimensions). However, there are dimensions for which
typical summing, counting, and finding the minimum or maximum are just not enough.
A good example of such a dimension is an Account dimension, which has members such
as Assets , Liabilities , and Net Income . The rules of aggregation for these members
differ from those of simple summing. For example, you do not add assets and liabilities.

One of the ways to get around this dilemma is to specify unary operators on a dimension,
one unary operator for each dimension member. The unary operator defines the arith-
metic operator to apply to the value of the member when rolling it up to produce the
aggregated value. Analysis Services supports seven unary operators. Table 13.1 describes
each one.

TABLE 13.1 Unary Operators Supported by Analysis Services
Unary Operator Description

Adds the value of the member to the aggregate value. +

Subtracts the value of the member from the aggregate value. -

Multiplies the value of the member by the aggregate value.

*

The aggregate value divided by the value of the member. /

~ Ignores the value of the member in the aggregation.

Multiplies the value by the factor and adds the result to the
aggregate values. ( Factor is any numeric value.)
An empty unary operator is equivalent to the + operator.

<<Factor>>

To create a unary operator in a dimension, you typically 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 unary operator column contains a specific unary operator for that member.

After you create an unary operator column, you specify the UnaryOperatorColumn property
of the attribute to which you assign the unary operator. You can do this in SQL Server
Business Intelligence Development Studio (BI Dev Studio) or by manually issuing a Data
Definition Language (DDL) statement.

NOTE
For a parent-child dimension—and parent-child is the type of the dimension in which
unary operators are mostly used—you specify unary operators on the parent attribute.

To apply unary operators to the Account dimension of the FoodMart 2008 database, follow
these steps: