The OLAP Procedure

HIERARCHY Statement

The HIERARCHY statement specifies the navigational order of the levels in a dimension.

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

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.

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

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.

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

hierarchy Geography
   levels=(country region division);

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.

Requirement:

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.

Interaction:

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.

Interaction:

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.

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.

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. [cautionend]

Default:

hier-name

DEFAULT

identifies a hierarchy as the default hierarchy for the dimension that is defined by the DIMENSION statement.

Default:

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.

Default:

The hierarchy caption, which may be the default, hier-name.

Top of Page