Calculated Members
A calculated member is a dimension member whose value is calculated at run time using an expression that you specify when you define the calculated member. Calculated members can also be defined as measures. Only the definitions for calculated members are stored; values are calculated in memory when needed to answer a query.
Calculated members enable you to add members and measures to a cube without increasing its size. Although calculated members must be based on data (such as members) that already exists in the cube, you can create complex expressions by combining this data with arithmetic operators, numbers, and a variety of functions. Analysis Services includes a library of over 100 functions and allows you to register and use other function libraries.
For example, suppose executives in a shipping company want to determine which types of cargo are more profitable to carry, based on profit per unit of volume. They use a Shipments cube that contains the dimensions Cargo, Fleet, and Time and the measures Price_to_Ship, Cost_to_Ship, and Volume_in_Cubic_Meters; however, the cube does not contain a measure for profitability. You can create a calculated member as a measure named Profit_per_Cubic_Meter in the cube using the existing measures in the expression:
([Measures].[Price_to_Ship] - [Measures].[Cost_to_Ship]) /
[Measures].[Volume_in_Cubic_Meters]
The next time the Shipments cube is browsed, Profit_per_Cubic_Meter appears along with the other measures.
To create calculated members, use Calculated Member Builder. For more information, see Creating Calculated Members and Calculated Member Builder.
In Analysis Manager, a calculated member is identified by the following icon.
Calculated members have a Format String property that controls the format of cell values displayed to end users. This property is accessed in the properties pane of Cube Editor or Virtual Cube Editor. The Format String property accepts the same values as the Display Format property of measures.
Although calculated members created in Calculated Member Builder are saved so that they can be used when their cube is browsed, calculated members can also be created but not saved. To create a calculated member for the duration of a session, use the CREATE MEMBER statement. This statement is also used in the Statement property when you create calculated members programmatically with Decision Support Objects (DSO). To create a calculated member for the duration of a single query, use the WITH clause of the SELECT statement.