The OLAP Procedure

DIMENSION Statement

Defines the logical and hierarchical relationships between the variables in the input data.

Syntax

DIMENSION dim-name HIERARCHIES=(hier–nam ... hier-nameN) <option(s)>;

Details

Overview

At least one DIMENSION statement must be specified when the cube is created. The maximum number of dimensions that can be defined in a cube is determined by combining the number of dimensions with the number of multiple hierarchies that are defined in those dimensions. The maximum value of that sum is 128. Mathematically, the sum is expressed as follows:

MaxDims = NumDims + NumMultipleHierarchies = 128

All hierarchies other than the first hierarchy in each dimension apply to the total.

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

A DIMENSION statement must include the name of at least one hierarchy in its HIERARCHIES= option. In addition, a HIERARCHY statement must include the name of at least one level in its LEVELS= option. Note that you cannot use the same level in more than one dimension.

You can use LEVEL statements to specify a time period for each level in a TIME dimension. You can also use LEVEL statements to supply information such as a level-specific sort order or a level description.

Required Arguments

dim-name | folder-path/dim-name <(SHARED DIMENSION)>): dim-name names a dimension by using a valid SAS name up to 32 characters in length. Shared dimensions use the longer form, with the path, name, and keyword. For naming guidelines, see Naming Guidelines and Rules for the SAS OLAP Server . For information on shared dimensions, see Overview, and Building a Shared Dimension.

HIERARCHIES=(hier-name...hier-nameN): specifies the name of one or more hierarchies as defined by HIERARCHY statements.

Options

CAPTION='string': specifies a maximum of 200 characters that can be used to create a meaningful description of the dimension. 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 name of the dimension, as specified by the required argument dim-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.

DESC | DESCRIPTION='string': specifies any number of characters that can be used to create a meaningful description of the dimension. 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 value of the CAPTION= option.

DIMKEY=dimension-table-column

specifies the name of a column in the dimension table that is specified in the DIMTBL= option. That column must contain values that correspond to fact key values in the fact table and be a value that corresponds to a unique combination of level values in the fact table.

Note: The corresponding fact key is specified with the FACTKEY= option. The fact table is specified with the FACT= option in the PROC OLAP statement.

For example, for a dimension that consists of three levels—COUNTY, REGION, and STATE—a dimension key named AREA_ID might exist. In this dimension, each unique value of AREA_ID corresponds to a unique combination of COUNTRY, REGION, and STATE.

Sample Dimension Data That Illustrates How Unique DIMKEY Values Correspond to Unique Combinations of Level Values

AREA_ID	COUNTRY	REGION	STATE
1	USA	East	NY
2	USA	East	NJ
3	USA	West	CA
4	USA	West	AZ
5	CANADA	East	QUEBEC
6	CANADA	West	BRITISH COLUMBIA

DIMTBL=libname.memname: specifies the two-level SAS name or a dimension table that matches the fact table that is specified with the FACT= option in the PROC OLAP statement. The dimension table must contain one column for each dimension level and each level property and one column for the dimension key. However, if the dimension key is also a level, then the dimension table needs to have only as many columns as there are levels in the dimension. Member metadata for the dimension is derived from the information in the level columns of the dimension table.

You can also specify data set options with DIMTBL=. Options are stored within the cube and reapplied when the cube is unpacked or rebuilt. For more information, see “Data Set Options” in SAS Language Reference: Concepts.

Note: The same dimension tables can be used to load cubes that have some, but not all, dimensions in common. This means that it is possible for multiple cubes to share the same dimension data.

Note: If you are building a cube that will contain multiple national languages, then replace the DIMTBL= option with DIMTABLELIBREF= and DIMTABLEMEMPREF= options. In addition, you must create a USER_DEFINED_TRANSLATIONS statement.

DIMTABLECAPPREF=caption-table-prefix: specifies the member prefix for the translated dimension CAPTION tables. The member prefix is the prefix of the data set name. The suffix of the name is provided by the USER_DEFINED_TRANSLATIONS statement. This specification is optional but if used must differ from the DIMTABLEMEMPREF option. For example, if the caption member prefix is dealdimcap_ and the suffix is da_DK, then PROC OLAP looks for a data set named dealdimcap_da_DK.sas7bdat in the library that is specified by the DIMTABLELIBREF= option. This option is used in conjunction with the DIMTABLELEBREF=option and the USER_DEFINED_TRANSLATIONS statement.

If this option is not used, then captions appear in the default language.

DIMTABLELIBREF=mls-library: specifies the library for the data sets that exist, for this dimension, in each language that is specified by the USER_DEFINED_TRANSLATIONS statement. The library is associated with the dimension and not the language. If you choose to put your data sets in different librefs based on languages, you must use a concatenated libref so that it appears to the PROC as a single libref.

This option is required if you are using the Multiple Language Support capabilities of the SAS OLAP Server. This option is used in conjunction with the DIMTABLEMEMPREF= option.

Note: If you are building a cube that will contain multiple national languages, then DIMTABLELIBREF= and DIMTABLEMEMPREF= are required instead of DIMTBL=.

DIMTABLEMEMPREF=: specifies the member prefix for the translated dimension tables. The member prefix is the prefix of the data set name. The suffix of the name is provided by the USER_DEFINED_TRANSLATIONS statement. For example, if the member prefix is dealdim_ and the suffix is da_DK, then PROC OLAP looks for a data set named dealdim_da_DK.sas7bdat in the library that is specified by the DIMTABLELIBREF= option. DIMTABLEMEMPREF= is required if you are using the Multiple Language Support capabilities of the SAS OLAP Server. It is used in conjunction with the DIMTABLELIBREF= option and the USER_DEFINED_TRANSLATIONS statement.

Note: If you are building a cube that will contain multiple national languages, then DIMTABLELIBREF= and DIMTABLEMEMPREF= are required instead of DIMTBL=.

EMPTY_CHAR='string': specifies the text string that identifies members of character levels 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 in a character level must have a caption whose value matches the value of the EMPTY_CHAR= option. For example, if a member in a character level is skipped, and if the caption of that member is Empty, then the EMPTY_CHAR= option is specified as follows: empty_char='Empty'

The maximum length of the quoted string is 256 characters.

When specified in the HIERARCHY statement, the EMPTY_CHAR= option overrides (for that hierarchy) any specification of the EMPTY_CHAR= option in the PROC OLAP statement. In turn, the EMPTY_CHAR= option in the HIERARCHY statement is overridden by the EMPTY= or IGNORE_EMPTY options in the LEVEL statements in that hierarchy.

To skip members in numeric levels, use the EMPTY_NUM= option.

EMPTY_NUM='string': specifies the text string that identifies members of numeric levels 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 in a numeric level must have a caption whose value matches the value of the EMPTY_NUM= option. For example, if a member in a numeric level is skipped, and if the caption of that member is Empty, then the EMPTY_NUM= option is specified as follows: empty_num='Empty'

The maximum length of the quoted string is 256 characters.

When specified in the HIERARCHY statement, the EMPTY_NUM= option overrides (for that hierarchy) any specification of the EMPTY_NUM= option in the PROC OLAP statement. In turn, the EMPTY_NUM= option in the HIERARCHY statement is overridden by the EMPTY= or IGNORE_EMPTY options in the LEVEL statements in that hierarchy.

Note: If there is no format that is associated with the member value, then BEST12 is used as the format.

To skip members in character levels, use the EMPTY_CHAR= option.

FACTKEY=fact-table-column: specifies the name of the column in the fact table that corresponds to the dimension table column that is specified with the DIMKEY= option. The name does not have to match the DIMKEY name. Referring back to the previously discussed example, the FACTKEY name could be CUST_NO even though the DIMKEY name is CUSTOMER_ID. However, even if the names are different, the underlying data must match. For example, you must match numeric columns with numeric columns and character columns with character columns. In addition, if the FACTKEY is a character column, then it must be the same length as the DIMKEY column. If the FACTKEY is a numeric column, then it is handled as a decimal precision number (rather than as an integer).

Restriction:For shared dimensions, the FACTKEY= option is not allowed. Instead, the fact key is specified with the USE_DIMENSION statement.

FORCE

enables deletion of existing shared dimension during a shared dimension build without having to use a DELETE option in a separate invocation.

If the shared dimension already exists in the specified schema, then the shared dimension will be deleted prior to building the new shared dimension. If this is a short-form build, using code generated by SAS OLAP Cube Studio, then this is equivalent to a DELETE_PHYSICAL followed by the build. If a metadata folder path is specified (for example DIMENSION <dim-name> SHAREDFORCE="/Shared Data/SD/MySD" FORCE), then the shared dimension, if it exists, must exist in the specified folder or the deletion will fail, as shown in the following example:

DIMENSION "/Shared Data/SD/MySD"  SHARED FORCE

This is how DELETE and DELETE_PHYSICAL behave currently.

Restriction:The FORCE option on the DIMENSION statement is for shared dimensions only. The FORCE option is also valid for cubes when used on the PROC OLAP statement with CUBE=.

CAUTION:

The FORCE option does not include a rollback feature.

If the shared dimension build fails, there is no rollback of the original shared dimension.

IGNORE_EMPTY: specifies that, for this hierarchy, any values that were specified for the EMPTY_CHAR= and EMPTY_NUM= options in the PROC OLAP statement are to be ignored. This option can be overridden by specifications of EMPTY_CHAR= and EMPTY_NUM= in the same HIERARCHY statement. The IGNORE_EMPTY option can also be overridden in subsequent LEVEL statements by using the EMPTY= option. For further information, see Defining Cube Hierarchies.

MAP_SERVICE=MapServiceName: specifies the map service to which this cube should be linked. The dimension must have a dimension type of GEO. The map service must be a map service defined with the ESRI server specified previously. There can be only one map service per GEO dimension.

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 dimension. 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 about MLS caption tables and MLSID, see USER_DEFINED_TRANSLATIONS Statement.

NONUPDATEABLE: specifies that the dimension should be built with the minimum amount of disk space to represent the members available when the cube is created. By default, new dimensions are built to allow for new members to be added in future updates. NONUPDATEABLE is valid only when the cube is first created.

If this option is set, no new members can be added to the dimension in future updates of the cube. If you want to make all dimensions of a cube non-updateable, you can use the NONUPDATEABLE option in the PROC OLAP statement instead.

PATH='path-name': specifies the physical or logical path to the location of a new shared dimension. Within the specified path, the dimension is stored in a directory that uses the name of the dimension. For example, if you enter the path 'C:\shared_dimensions\Customer', then you should enclose the path within quotation marks.

SHARED: specifies that the dimension that is being referenced is a shared dimension.

SORT_ORDER=ASCENDING | DESCENDING | ASCFORMATTED | DESFORMATTED | DSORDER: specifies a sort order for all levels in the dimension. Values that are returned from queries appear in this order by default. This setting is overridden if sort order is set in a LEVEL statement.

Default: ASCENDING

To specify a sort order for each level within a dimension, set the SORT_ORDER= option in each LEVEL statement. Values that are returned from queries appear in this order. The sort order can be changed at query time using the MDX ORDER functions.

TYPE=TIME | GEO: identifies the dimension as a TIME or GEO dimension. The GEO type is used when defining ESRI map information for a cube. There can be only one GEO and one TIME dimension for a cube. You must set this option for a TIME or GEO dimension. TIME and GEO are the only valid values for this option. You can use LEVEL statements to specify the time period of each level in the TIME dimension. Specifying TYPE=TIME also enables you to use the MDX time series functions during data query.

Note: In order to add geographic information to an existing cube, you must use SAS OLAP Cube Studio. The OLAP procedure does not support adding the GEO type to an existing dimension.

UPDATE_DIMENSION

enables you to add new members to a cube and update member properties for existing members of the dimension. UPDATE_DIMENSION can be used only on a dimension without the NONUPDATEABLE designation. It can be used with or without the ADD_DATA option in the PROC statement. It can be used with one of the following parameters:

MEMBERS	The dimension table currently associated with the dimension should be read and processed for new members of every hierarchy in that dimension. If the ADD_DATA option is used, this is the default behavior for cubes loaded from a detail table or a star schema. If the ADD_DATA option is not used, this parameter can be applied to individual dimensions for cubes loaded from star schemas only. It is not valid for a cube loaded from a detail table. Thus this option provides a way to update individual dimensions without adding data to the cube.
MEMBERS_AND_PROPERTIES	The dimension table currently associated with the dimension should be read and processed for new members and member properties for existing members should be changed with the new values in the dimension table. If the ADD_DATA option is used, this option can be used for cubes loaded from a detail table or a star schema. If the ADD_DATA option is not used, it can be applied to individual dimensions for cubes loaded from star schemas only. It is not valid for a cube loaded from a detail table. Thus this option provides a way to update individual dimensions without adding data to the cube.
OFF	The dimension table currently associated with the dimension should not be read at all. When this parameter is used, it applies only to cubes loaded from a star schema and never to a cube loaded from a single detail table. If the ADD_DATA option is used on the PROC, this option must be used on any UPDATEABLE dimensions whose dimensions tables should not be processed, since the default behavior is UPDATE_DIMENSIONS= MEMBERS. If the ADD_DATA option is not used on the PROC, this is the DEFAULT option for all dimensions in the cube.