The OLAP Procedure

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.

Syntax

LEVEL level-name <option(s)>;

Details

Overview

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.

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 hierarchies, as described in Defining Cube Hierarchies.

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.

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.

Do not use ALL or ALL hiername as the level name, to avoid ambiguities during queries. The cube will build with such level names, but the SAS log will contain a warning.

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.

The default setting is 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.

The default setting is the value of the CAPTION= option if one exists; otherwise, it is 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.

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 can be defined per level. The same map layer can be specified for different levels in the dimension. However, the map layer is generally specified with a different map field indicated.

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 in 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.

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 level. 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.

SORT_ORDER=ASCENDING | DESCENDING | ASCFORMATTED | DESFORMATTED | DSORDER: specifies a sort order for a level within a dimension. Values that are returned from queries appear in this order.

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

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

TYPE=YEAR | HALF_YEARS | QUARTERS | MONTHS | WEEKS | DAYS | HOURS | MINUTES | SECONDS: specifies the time period for the dimension levels when you specify the TYPE=TIME option in the DIMENSION statement.

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.