Defining Measures for a Cube

Selecting Measures in SAS OLAP Cube Studio

When you build a cube in the Cube Designer wizard, you must specify the measures that are used to help query the cube. After you have defined the data input source and the structure of the cube (dimensions, levels, hierarchies) you can define measures on the Select Measures page of the Cube Designer wizard. You can view the cube's currently defined measures, add new measures, and remove existing measures.

To define a measure, you select from the Available list of columns and statistics. This list contains the numeric columns in the input data source and the statistics available for each column. Move the selected items to the Cube Measures table. You can have a maximum of 1024 measures per cube.

Note: If you are including aggregated data from tables other than the input data source, considerations for stored statistics should be made. This applies when you are creating a cube from a detail table or a star schema. You must include measures for the stored statistics that are required for each derived statistic that you create in the cube. For example, if you want to calculate AVG, you must create measures for N and SUM, as well as AVG.

Unique Member Count Measures

You define unique member count measures in the Cube Designer – Select Measures page. Select the level and hierarchy for the new measure, and then move them to the Cube Measures table. A name is automatically generated for that unique member count measure consisting of the level name,"NUNIQUE", and the parent hierarchy name. It is important to note that only one combination of level and hierarchy can be defined for a measure. After a combination has been used to create a unique member count measure, that combination cannot be used again. In addition, if the level or hierarchy is deselected, then any associated defined unique member count measures are deleted from the cube.

For a cube to build, at least one non-unique member count (non-NUNIQUE) measure must be defined for the cube.

NUNIQUE Statistic

You can define a distinct count statistic using the MEASURE statement and the NUNIQUE statistic. The LEVEL and HIERARCHY options for the MEASURE statement are used with the NUNIQUE statistic and are ignored for non-NUNIQUE statistics if specified.

Note: The LEVEL name is optional. If it is omitted, then the level name is assumed to be the name specified for the NUNIQUE measure. The HIERARCHY name is required only if the level is in multiple hierarchies. For further information about using the NUNIQUE statistic see the MEASURE Statement.

Defining Stored and Derived Measures for a Fully Summarized Cube

Overview

When you are building a cube with fully summarized data, you can select predefined (stored) measures that are actually stored with the summarized data source. You can also then create derived measures from those stored measures. In the Cube Designer wizard, pages for stored and derived measures are available when you have selected a fully summarized data source to build a cube from.

Stored Measures

On the Stored Measures page of the Cube Designer wizard, you can select the NWAY columns that contain stored measures data. Select from the Available list and move the needed measures to the Selected list. On the Assign Stored Measures page of the Cube Designer wizard, you can then assign a statistic to each stored measure and each measure to an analysis group.

Derived Measures

After you have selected stored measures from the summarized data source, you can define any needed derived measures for the cube. You can store only derived statistics that can be calculated from the available stored statistics. For example, to use the derived statistic AVG, you must have stored statistics for N and SUM with the same assigned group name.

On the Select Derived Measures page of the Cube Designer wizard, select the Add button to create a new derived statistic or the Modify button to change an existing derived measure. To define a derived measure, you specify the following options:

Analysis Group

displays a list of analysis groups assigned to the cube's stored measures. Select a group from this drop-down list to see available statistics in the Derived Statistics drop-down list. Only those groups that contain stored measures that can be used to derive a statistic are included in the drop-down list.

Derived Statistics

displays a list of statistics that you can derive from the statistics that you are storing with the cube for the selected analysis group. Select the derived statistic that you want to use. Only those statistics that can be derived from the stored statistics are included in the drop-down list

Measure Name

displays a name for the derived measure. The name must be a valid SAS name (up to 32 characters). For more information, see Naming Guidelines and Rules for the SAS OLAP Server .

Note: A derived statistic can be used only once per cube per analysis group.

Statistics Available for Measures

Overview

When you define a cube, you select the statistics that are used to calculate the cube's measures. Here is the list of available statistics.

Base Stored Statistics

Count
Sum
Maximum
Minimum
Count of Missing Values
Uncorrected Sum of Squares

Derived Statistics

Average
Range
Correct Sum of Squares
Variance
Standard Deviation
Standard Error of Mean
Coefficient of Variance
T Value
Probability of Greater Absolute Value
Lower Confidence Limit
Upper Confidence Limit

Required Measure Statements for Derived Statistics

New cubes that are based on a data source that contains existing summarized data must include measure statements for the stored statistics, which are required for each derived statistic that you want to create for the new cube. For example, if you want to calculate AVG, you must create measures for N and SUM, as well as AVG. The following table indicates which stored statistics are required for each derived statistic.

Stored Statistics That Are Used to Create Derived Statistics

Derived Statistics	Required Stored Statistics
AVG	N, SUM
RANGE	MIN, MAX
CSS	N, SUM, USS
VAR, STD, STDERR, CV, T, PRT, LCLM, UCLM	N, SUM, USS