Specifying Calculated Members and Measures

Overview

After you have built a cube, you might find it necessary to add members or measures to the cube. You can add calculated members or measures to a cube in the following ways:

manually by submitting PROC OLAP code with the DEFINE statement.
using the Calculated Members Wizard

The Calculated Members function enables you to add new members for the Measures dimension. A calculated member is a definition (for a dimension member) that you create and store with the cube. The calculated member value is generated later during query time. You can also define a calculated member as a measure.

Using the Calculated Members Wizard

Overview

The Calculated Members function can be accessed from the SAS OLAP Cube Studio Actions menu or from the context menu for a cube. Click Calculated Members to open the Calculations dialog box.

In the Calculations dialog box, you can add new calculated members or modify existing members for the selected cube. The list box displays all calculated members that are defined in the metadata for the selected cube. Select Add to launch the New Member Wizard and define a new calculated member.

To modify an existing member, select the member and then click Edit. The Edit a Calculated Member dialog box appears.

Selecting Calculation Types

When you click Add in the Calculations dialog box, the New Member wizard is launched. In this wizard, you select the type of calculation that you want to create. You can select one of the following types:

Simple calculations to create a simple calculation formula. See Simple Calculations.
Time analysis calculations to create a time-based calculation formula. See Time Calculations.
Custom calculations to enter a custom calculation formula. See Custom Calculations.

Simple Calculations

On this page, you select the variables to create a simple calculation. You can select one of the following calculation types:

Sum
Difference
Ratio
Percent Increase
Percent Decrease
Distinct Count

The Formula drop-down lists for the members are populated with all measures, including calculated measures. The Formula panel changes depending on which calculation-type radio button you select. When a formula is selected, the Formula panel is populated with drop-down lists appropriate for the selected calculation. A member must be selected in each list. The member selections for the different formulas are preserved across formulas within a type.

If you select Distinct Count, a selection tree is displayed. Each dimension has a node that expands into its hierarchies and then into the levels for each hierarchy. One variable in the hierarchy tree must be selected.

For further information about the Simple Calculations page, see the SAS OLAP Cube Studio Help.

Time Calculations

On this page, you select the variables to create a time calculation. You can select one of the following calculation types:

Opening Balance
Closing Balance
Rolling Total
Average Over Time
Compare Parallel Periods
Compare Consecutive Periods

The Formula panel changes depending on the time-calculation radio button that you select. The Existing measure drop-down list is populated with all measures, including calculated measures. The Time period drop-down list is populated with members from the Time dimension. A member must be selected in each list. The member selections for the different formulas are preserved across formulas within a type.

Note: This option is not available if there are no TIME-type dimensions for the cube.

For further information about the Time Calculations page, see the SAS OLAP Cube Studio Help.

Custom Calculations

On this page, you enter the variables to create a custom calculation. You can enter the following:

Parent dimension

specifies a parent dimension for the new calculated member that you are creating. The default dimension is Measures. If you select a dimension other than Measures, this field is initially populated with the unique name for the default hierarchy's ALL member.

Parent member

specifies a parent member for the new calculated member. Use the Browse button to display a tree of valid members for the dimension. If you select the Browse button and the cube physically exists, you are prompted to log on to an OLAP server, if you haven't already.

Note: If the cube exists, you must connect to the OLAP server so that the valid members can be retrieved from the server. If the cube does not exist or a connection cannot be made to the OLAP server, the valid members displayed in the tree will be the ALL members for each of the hierarchies for the dimension.

Name

specifies a name for the custom calculation.

Formula

specifies an MDX formula for the new calculated member.

Click Verify to validate the MDX formula entered in the Formula text box. This function is available only for cubes that physically exist. When you select Verify, the Log on to an OLAP server dialog box appears. Select a server and enter a user ID and password. A connection is made to the selected OLAP server. Click Clear to clear the contents of the Formula text box.

The Generated MDX text area enables you to view the MDX code that currently exists for the selected cube and any new MDX code that is entered in the Formula text box.

For further information about the Custom Calculations page, see the SAS OLAP Cube Studio Help.