The OLAP Procedure

HIERARCHY Statement

Specifies the navigational order of the levels in a dimension.

Syntax

HIERARCHY hier-name LEVELS=(level–name1 <level–name2 ...level-nameN>)
<option(s)>;

Details

Overview

You must define at least one hierarchy for each dimension. Specifically, each DIMENSION statement must identify at least one unique HIERARCHY statement. The maximum number of hierarchies that can be defined in a cube is 128. Mathematically, the sum is expressed as follows:

MaxHiers = NumMultHiers + NumDimensions = 128

All hierarchies other than the first hierarchy in each dimension apply to the total. Levels in the same dimension can be shared between hierarchies. You can have a maximum of 19 levels per hierarchy. There is no limit to the number of hierarchies per dimension.

Here are some examples of cubes that meet the maximum number of hierarchies:

128 dimensions, each dimension has 1 hierarchy
127 dimensions, 1 dimension has 2 hierarchies
126 dimensions, 1 dimension has 3 hierarchies
126 dimensions, 2 dimensions have 2 hierarchies

Required Arguments

hier-name: specifies a valid SAS name for the hierarchy. This name is also used in the HIERARCHIES= option in the DIMENSION statement. The hier-name cannot be the same as any of its level names. Hierarchy names must be unique within the cube. If the hierarchy that you are defining is the only one in the dimension, then the hierarchy name must match the dimension name. For other naming guidelines, see Naming Guidelines and Rules for the SAS OLAP Server .

LEVELS=(level-name1 <level-name2 ...level-nameN>): specifies a valid SAS name for at least one level. These names correspond to the level names used in any optional LEVEL statements. Level names must be unique within a cube and cannot be the same as the hier-name. (You can use a column as a level even if it is also being used as a measure.) Enter one or more names, separated by a space. Enter the level names in the order in which you want them to be used, beginning with the top level. For naming guidelines, see Naming Guidelines and Rules for the SAS OLAP Server .

If the hierarchy is part of a TIME dimension, then the levels must be listed in order from most general to least general based on their assigned TYPE. For example, a TYPE=YEAR level must be listed before a TYPE=QUARTER level. Level names should not conflict with the ALL member name in the HIERARCHY statement. You should not use ALL for the level name.

Options

ALL_MEMBER='string'

specifies the caption for the ALL member of the hierarchy.

The ALL member name should not conflict with level names in the LEVEL or HIERARCHY statement. When selecting the ALL member name, follow these guidelines:

The ALL member caption should not be all.
A level name within the hierarchy should not be named all.
The ALL member caption should not be a level name that is within the hierarchy all.

When a cube is built, a warning message is displayed in the SAS log and in SAS OLAP Cube Studio if a conflict between the ALL member name and a level name is detected.

CAPTION='string': specifies a maximum of 200 characters that can be used to create a meaningful description of the hierarchy. Third-party applications that report on cube data might display this description. If the text includes blank spaces or any characters that are not permitted in a valid SAS name, then enclose the text within quotation marks. The default value is the hier-name.

Note: Cubes that are built with captions that are longer than 200 characters cannot be fully registered in the SAS Metadata Repository. Captions will be truncated to 200 characters.

DEFAULT: identifies a hierarchy as the default hierarchy for the dimension that is defined by the DIMENSION statement. The default value is the first hierarchy listed for the dimension.

DESC | DESCRIPTION='string': specifies any number of characters that can be used to create a meaningful description of the hierarchy. Third-party applications that report on cube data might display this description. If the text includes blank spaces or any characters that are not permitted in a valid SAS name, then enclose the text within quotation marks. The default value is the hierarchy caption, which might be the default, hier-name.

DEFAULT_MEMBER= 'member-unique-name’: specifies a default member when an MDX query opens a cube. This member is used to aggregate the hierarchy when no other criteria are provided. The member name must be fully qualified, as in "[TIME].[All TIME].[2010].[December]". When the cube is built, the ALL member is the default member. The default can be changed using this option along wit the UPDATE_DISPLAY_NAMES option.

The named member must be part of the members to which the user is permitted access. If the member name cannot be found, or if the member is inaccessible, then the ALL member is used as the default member.

The DEFAULT_MEMBER= option is used with the PROC OLAP statement option UPDATE_DISPLAY_NAMES.

Specifying a calculated member as the default member can degrade performance.

MLSID=n: is a positive integer identifier between 0 and MACINT (2147483647) that identifies the observation in the data set that contains the translated caption and description for the hierarchy. This identifier is expected in the MLSID column of the data set specified by DIMTABLECAPPREF=, DIMTABLELIBREF=, and the USER_DEFINED_TRANSLATIONS statement.

Note: For further information on MLS caption tables and MLSID, see USER_DEFINED_TRANSLATIONS Statement.

Example

Here is an example of a HIERARCHY statement that specifies three levels:

hierarchy Geography
   levels=(country region division);