The OLAP Procedure

LEVEL Statement

The LEVEL statement provides additional information about a level specified with the LEVELS= option in a HIERARCHY statement, and enables you to set options for ragged hierarchies. Each LEVEL statement must correspond to a HIERARCHY statement. You cannot have a level that does not belong to a hierarchy.

LEVEL level-name <option(s)>;

For TIME dimensions, you can use LEVEL statements to specify a time period for each level in the dimension. However, if you specify the time period for one level, then you must specify the time period for all levels. You also use LEVEL statements to supply information such as a level description or a level-specific sort order. You can have a maximum of 256 levels per cube and a maximum of 19 levels per hierarchy.

Note: Levels that are shared between hierarchies share the values of the options EMPTY_CHAR=, EMPTY_NUM=, EMPTY=, and IGNORE_EMPTY. These options are used to create ragged or unbalanced heirarchies, as described in Defining Cube Hierarchies. [cautionend]

Note: When you rebuild a cube that has been physically deleted, the rebuilt cube still uses the formats that were originally used to build the cube and were saved in the cube's metadata. This means that the rebuilt cube does not automatically include any formatting changes that you might have made in the input data source. To manually specify the new formats, edit and rebuild the cube by using SAS OLAP Cube Studio. [cautionend]

Required Arguments

level-name

specifies a valid SAS name. For naming guidelines, see Naming Guidelines and Rules for the SAS OLAP Server . This is the same name that is used in the LEVELS= option in the HIERARCHY statement. Level names must be unique within a cube.

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

CAPTION='string'

specifies a maximum of 200 characters that can be used to create a meaningful description of the level. Third-party applications that report on cube data might display this description. If the text includes blank spaces or special characters that are not permitted in a valid SAS name, then enclose the caption 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:

The column's label in the input data source. If there is no label available, the default is the level name.

COLUMN=column-name

specifies the name of a column from the input data source. Use this option if the column name is not the same as the LEVEL name. This option is useful in the following scenarios:

You want your level name to be different from the input column name.
Different dimension tables for the cube each have a column with the same name
You want to use the same dimension table and columns in more than one dimension of your cube.
You load multiple levels from the same input column (in combination with the FORMAT= option).

You can use a column as a level even if it is also being used as a measure.

DESC | DESCRIPTION='string'

specifies any number of characters that can be used to create a meaningful description of the level. 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 value of the CAPTION= option if one exists; otherwise, the column's label. If there is no label available, the default is the level name.

EMPTY='string'

specifies the text string that identifies members that are to be skipped or disregarded. Members are skipped in order to create ragged or unbalanced hierarchies, as described in Defining Cube Hierarchies.

To be skipped, a member must have a caption whose value matches the value of the EMPTY= option. For example, if a member is skipped, and if the caption of that member is Empty, then the EMPTY= option is specified as follows:

empty='Empty'

The maximum length of the quoted string is 256 characters.

Interaction:

The EMPTY= option overrides for that level any specification of EMPTY_CHAR=, EMPTY_NUM=, or IGNORE_EMPTY that might have been specified in the respective HIERARCHY or PROC OLAP statement.

ESRI_MAP_LAYER=MapLayerName

specifies the ESRI map layer which should be associated with this level. It must be a map layer defined in the specified map service. The level must be a level contained in the dimension with dimension type GEO. Only one map layer may be defined per level. The same map layer can be specified for different levels in the dimension. However, it is generally with a different map field specified.

FORMAT=sas-format-name

specifies the SAS format to be used when reading in the member caption data from the input data source for the LEVEL. By default, the column format specified in the input table is used. However, you can override that format by using the SAS FORMAT statement in the PROC OLAP step or the FORMAT= option on the LEVEL statement.

This option can be particularly useful if you want to load multiple levels from the same column. A typical use is loading the levels of a time dimension. For example, if your input data contain a column with a SAS date values, you could load the time dimension levels, as shown in the following example.

dimension Time
   hierarchies=(Time)
   type=time
   ;  
   hierarchy Time
      levels=(Year Quarter Month Day)
      ; 
   level Year 
      type=year 
      column=date_id
      format=year.
      ;  
   level Quarter
      type=quarters
      column=date_id
	format=qtr.
      ;      
   level Month
      type=months
      column=date_id
      format=monname.
      ;  
   level Day
      type=days
      column=date_id
      format=weekdate.
      ;

IGNORE_EMPTY

specifies that any value of the EMPTY_CHAR= option (for character levels) or EMPTY_NUM= option (for numeric levels) that was specified in the respective HIERARCHY or PROC OLAP statement is to be ignored. The level is not to be skipped in the cube build. For further information, see Defining Cube Hierarchies

SORT_ORDER=ASCENDING | DESCENDING | ASCFORMATTED | DESFORMATTED | DSORDER

specifies a sort order for a level within a dimension. Values that are returned from queries display in this order.

Default:

If a sort order is not specified in the DIMENSION statement or in the LEVEL statement, then the default order of ASCENDING is applied.

Interaction:

This setting overrides the SORT_ORDER= setting in the DIMENSION statement.

specifies the time period for the dimension levels when you specify the TYPE=TIME option in the DIMENSION statement.

Requirement:

If you specify a time period for one level in the TIME dimension, then you must specify the time period for all levels in the dimension. With regard to drill path, identify the levels from the most general time period to the most specific.

Top of Page