Creating Calculated Members

You can create customized measures or dimension level members, called calculated members, by combining cube data, arithmetic operators, numbers, and/or functions. For example, you can create a calculated member called “Marks” that converts dollars to marks by multiplying an existing dollar measure by a conversion rate. Marks can then be displayed to cube users in a separate row or column.

Calculated member definitions are stored, but their values exist only in memory. So, in the preceding example, values in marks are displayed to cube users but not stored as cube data.

Microsoft® SQL Server™ OLAP Services provides its own extensive function library for creating calculated members. It also supports other function libraries:

• Microsoft Visual Basic® for Applications Expression Services: This library is included with OLAP Services and automatically registered.
• Microsoft Excel worksheet: This library must be installed separately from OLAP Services, but it is automatically registered.
• Others: These libraries must be installed separately from OLAP Services and manually registered by clicking Register in the Calculated Member Builder.

OLAP Services supports only some functions in libraries other than the OLAP Services function library. Functions are supported only if they accept as arguments only string or numeric data types, or array or variant data types containing string or numeric values. In addition, functions are supported only if they return only string or numeric data types, or variant data types containing numeric values. It is recommended that you test separately each function from these libraries before you expose the resulting data to users. For lists of supported functions, see Visual Basic for Applications Functions and Excel Functions.

Note When you use a function in a library other than the OLAP Services function library, you can omit an optional argument only if you also omit all arguments that follow it.

If multiple libraries include the same function name, OLAP Services functions take precedence. After that, precedence is resolved in order of registration.

In order for client users to see the correct values returned by a function, the library containing the function must be installed and registered on their computers.

To create a calculated member, use the Calculated Member Builder. It enables you to specify the following definitions for the calculated member:

• Parent dimension

  Select the parent dimension to include the calculated member. Dimensions are descriptive categories by which the numeric data (that is, measures) in a cube can be separated for analysis. In tabular browsers, dimensions provide the column and row headings displayed to users when they browse a cube’s data. (In graphical browsers, they provide other types of descriptive labels but with the same function as in tabular browsers.) A calculated member provides a new heading (or label) in the parent dimension you select.

  Alternatively, you can include the calculated member in the measures instead of a dimension. This option also provides a new column or row heading, but it is attached to measures in the browser.

• Parent member

  Select a parent member to include the calculated member. This option is unavailable if you select a one-level dimension or Measures as the parent “dimension.”

  Dimensions are divided into levels which contain members. Each member produces a heading. While browsing the cube’s data, users can drill down from a selected heading to subordinate headings previously undisplayed. The heading for the calculated member is added at the level directly below the parent member you select.

• Name

  Select the name of the calculated member. This name appears as the column or row heading for the calculated member values when users browse the cube.

• Value expression

  Specify the expression that produces the values of the calculated member. The expression may contain any of the following:

  • Data:

    – Cube name

    – Measures category

    – Measures level category

    – Measure name

    – Dimension name

    – Level name

    – Member name

    – Calculated member name

  • Arithmetic operators
  • Numbers
  • Functions:

    OLAP Services functions are divided into the following categories:

    – Array

    – Dimension

    – Hierarchy

    – Level

    – Logical

    – Member

    – Numeric

    – Set

    – String

    – Tuple

The Calculated Member Builder has extensive Help.

For more information, see Calculated Members.

To create a calculated member using the Calculated Member Builder

1. In the OLAP Manager tree view, under a database, expand the Cubes folder.
2. Right-click the cube to contain the calculated member, and then click Edit.
3. In the Cube editor, on the Insert menu, click Calculated Member.
4. In the Calculated Member Builder:
   1. In the Parent dimension box, select the dimension that will include the calculated member, or select Measures.
   2. In the Parent member box, specify the member that will include the calculated member. Click Change to select a member other than the displayed member. (Change is unavailable if you selected a one-level dimension or Measures in Step A.)
   3. In the Member name box, type a name of the calculated member.
   4. In the Value expression box, construct an expression to produce the values of the calculated member. Use any combination of the following methods to add to the expression:
   • Drag items from the Data and Functions boxes.
   • Click an item in the Data or Functions box, and then click Insert.
   • Click the arithmetic operator and number buttons.
   • Type. This method is required to add functions from libraries other than the OLAP Services function library.
   5. To register additional function libraries, click Register.
   6. To close the Calculated Member Builder, click OK.
5. To save the calculated member, in the Cube editor, on the File menu, click Save.

Note Calculated members are not visible in the OLAP Manager tree view. To create and access them, you must first display the Cube editor.

After you add or change a calculated member in a cube, you must process dependent virtual cubes to provide users continuing access to them. (This processing is much faster than processing the component cubes if the component cubes’ structures have not changed since their last processing.) When you add or change the calculated member, virtual cube users connected to the server computer are unaffected as long as they remain connected. However, until you process the dependent virtual cubes, users who connect will be unable to see them.

You can rename a calculated member. Renaming changes the associated column or row heading displayed to cube users.

To rename a calculated member

1. In the OLAP Manager tree view, under a database, expand the Cubes folder.
2. Right-click the cube containing the calculated member, and then click Edit.
3. In the Cube editor tree view, right-click the calculated member, and then click Rename.
4. In the box beside the calculated member icon, type a new name, and then click outside the box.