MSAS - Implementing Calculations Using MDX Part 1

In this tutorial we will be learning about Calculated members, Creating Calculated Members, Using Function Libraries to Build Calculated Members and Calculated Member Builder.

Understanding Calculated members

Calculated members are members of a dimension whose value is calculated at run time using Multidimensional expressions. The values of a calculated member may be derived from other member’s values. For instance Profit is determined by subtracting the value of member costs from value of member sales. This is unlike an input member whose value is directly loaded from the data base.

The expressions for calculated members are defined when the member is defined. Calculated members can also be defined as measures of a dimension. Values derived from calculated members are not stored. Only the expressions are stored and the calculation is done at run time when a query is generated by the end user.

Calculated members enable users add members and measures to a cube without increasing the size of the cube. The components of the calculated member are derived from the data in the cube but complex multidimensional expressions can be built using arithmetic operators, numbers and a variety of functions. Analysis Services has a library of more than 100 functions and users can register and use other function libraries too.

The Format String property of a calculated member controls the format of cell values displayed to end users. This property can be accessed from the property pane of the Cube Editor or the Virtual Cube Editor. This property accepts the same values as the Display Format property of measures.

All calculated members created in a cube need not be saved. Some calculated members are created for run time use. Such members are created using the CREATE MEMBER statement. The statement property of the is also used in Decision Support Objects(DSO) when calculated members are created programmatically. A calculated member can be created for the duration of a single query using the WITH clause of the SELECT statement.

Calculated members can be created in regular or virtual cubes.

Creating Calculated Members

Calculated members can be created using the Calculated Member Builder. This enables the user specify the following options for the calculated member.

The Parent dimension to be included in the Calculated member. The calculated member will provide a new heading or label in the parent dimension selected.

Calculated members can also be included in the measures instead of the dimension. This option provides a new column or row heading attached to the measures in the browser.

The user can select the parent member to be included in the calculated member. This option is not available if one level dimensions or measures are selected as the parent dimension. The heading for the calculated member is included directly below the member selected as the Parent Member.
The name of the calculated member can be selected. This name appears as a column or row heading for the calculated member values when end users browse the cube.

Value expressions can be specified as an expression that produces the values of the calculated member. This expression is written in Multidimensional expressions(MDX). The expression may contain data expressions that represent the cubes components(dimensions, levels, measures etc); Arithmetic operators, numbers and functions.

Calculated members can be used as components of an expression for creating other calculated members. For instance Profit may be expressed as Sales - Cost value. Costs may be defined as a sum of several items. However the expression for costs has to be first created and then only included in the expression for deriving Profit.

Calculated members can be renamed after they are created.