The OLAP Procedure

DIMENSION Statement

The DIMENSION statement defines the logical and hierarchical relationships between the variables in the input data.

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

At least one DIMENSION statement must be specified when the cube is created. The DIMENSION statement is not used when adding or deleting aggregations from cubes.

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 + NumMultipleHeirarchies = 128

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

Here are some examples of cubes that are defined with the maximum number of dimensions:

	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

The DIMENSION statement does not create aggregations. To create aggregations, use the AGGREGATION statement.

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.

The following example uses one DIMENSION statement, two HIERARCHY statements, and three optional LEVEL statements to define a fully specified dimension. In the example, the same levels are being used in different ways.

dimension time
   hierarchies=(Year_Months Year_Quarters)
   ;  
   hierarchy Year_Months
      levels=(year month day)
      ; 
   hierarchy Year_Quarters
      levels=(year quarter day)
      ;
   level year 
      type=year 
      caption='Year'
      ;  
   level quarter
      type=quarters
      caption='Quarter'
      ;      
   level month
      type=months
      caption='Month'
      ;  
   level day
      type=days
      caption='Day'
      ;

Required Arguments

dim-name: names a dimension by using a valid SAS name up to 32 characters. For naming guidelines, see Naming Guidelines and Rules for the SAS OLAP Server.
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.

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:

dim-name

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.

Default:

dim-name

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

For example, for a dimension that is composed of three levels--NAME, ADDRESS, and INCOME--a dimension key named CUSTOMER_ID might exist. In this dimension, each unique value of CUSTOMER_ID corresponds to a unique combination of NAME, ADDRESS, and INCOME.

Sample Dimension Data That Illustrates How Unique DIMKEY Values Correspond to Unique Combinations of Level Values
CUSTOMER_ID	NAME	ADDRESS	INCOME
1	Juan	hostel	2000
2	Shelly	apartment	2000
3	Paul	house	25000
4	Makoto	castle	250000000

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 data is accessed at run time. 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. [cautionend]

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

DIMTABLELIBREF=

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. You cannot put different languages in different libraries, but you can put different dimensions in different libraries. This option is required if you are using the Multiple Language Support capabilities of the SAS OLAP Server. It is also 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=. [cautionend]

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

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.

Interaction:

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.

Interaction:

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

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

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 above. There can only be one map service per GEO dimension.

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 on the PROC OLAP statement instead.

SORT_ORDER=ASCENDING | DESCENDING | ASCFORMATTED | DESFORMATTED | DSORDER

specifies a sort order for all levels in the dimension. Values that are returned from queries display in this order by default.

Default:

ASCENDING

Interaction:	This setting is overridden if sort order is set in a LEVEL statement.
Tip:	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 display in this order.

Note:

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 only be one GEO and one TIME dimension for a cube.

Requirement:

You must set this option for a TIME or GEO dimension. TIME and GEO are the only valid values for this option.

Interaction:

You can use LEVEL statements to specify the time period of each level in the TIME dimension. Specifying TYPE=TIME also allows 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. [cautionend]

UPDATE_DIMENSION

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

MEMBERS: This means that the dimension table currently associated with the dimension should be read and processed for new members of every hierarchy in that dimension. If 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, 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.
MEMBERS_AND_PROPERTIES: This means that the dimension table currently associated with the dimension should be read and processed for new members PLUS 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: This means that the dimension table currently associated with the dimension should NOT be read at all. 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.

Top of Page