Creating and Using Analytic Workspace Metadata, 5 of 7

Metadata That Describes Dimension Hierarchy Levels

Metadata that is required for the display of hierarchy levels

If a dimension has one or more hierarchies, and you want to display the various levels of those hierarchies, then the following metadata is required for that dimension:

Set the LEVELDIM property to identify the names of hierarchy levels

Define a hierarchy level dimension to identify the name of each hierarchy level, then set the LEVELDIM property on the base dimension (meaning, the dimension whose hierarchy levels are being identified).

Defining the hierarchy level dimension

The following example defines a hierarchy level dimension for the TIME dimension. Note that the dimension is defined with a text data type.

define time.leveldim dimension text

You then add dimension values that are the names of the levels of the TIME dimension's hierarchies.

Setting the LEVELDIM property

After you have defined the hierarchy level dimension, then you can set the LEVELDIM property on the dimension.

For example, the following code sets the LEVELDIM property on the TIME dimension and specifies the dimension named time.leveldim:

consider time
property 'LEVELDIM' 'time.leveldim'

Set the LEVELREL property to identify hierarchy level contents

Define a dimension member level relation to identify the hierarchy level to which each dimension value belongs, then set the LEVELREL property on that dimension.

Defining the dimension member level relation

The following example defines a relation for the TIME dimension. Note that the relation relates the hierarchy level dimension for TIME, named time.leveldim to the TIME dimension and its hierarchy dimension, named time.hierdim. See "Set the LEVELDIM property to identify the names of hierarchy levels" for information about defining a hierarchy level dimension. See "Set the HIERDIM property if a dimension has any hierarchies" for information about defining a hierarchy dimension.

define time.levelrel relation time.leveldim <time, time.hierdim>

You then relate each dimension value to the hierarchy level to which it belongs. For information about populating a relation, refer to the DEFINE RELATION command in the OLAP DML Reference.

Setting the LEVELREL property

After you have defined the dimension member level relation, then you can set the LEVELREL property on the dimension.

For example, the following code sets the LEVELREL property on the TIME dimension and specifies the relation named time.levelrel:

consider time
property 'LEVELREL' 'time.levelrel'

Set the HIERLEVELVS property to specify level order

Define a level-to-hierarchy mapping valueset to hold the list of levels for each hierarchy, then set the HIERLEVELVS property on that dimension.

Order the levels from highest (meaning, the root level) to lowest. A level can belong to more than one hierarchy.

Defining the level-to-hierarchy mapping valueset

The following example defines a level-to-hierarchy mapping valueset for the TIME dimension. Note that the valueset is defined with a short integer data type. The definition also requires the level dimension and hierarchy dimension. For information about defining a level dimension, see "Set the LEVELDIM property to identify the names of hierarchy levels". For information about defining a hierarchy dimension, see "Set the HIERDIM property if a dimension has any hierarchies".

define time.hierlvl valueset time.leveldim <time.hierdim>

You then populate the valueset with a list of levels for each hierarchy. For information about populating valuesets, refer to the DEFINE VALUESET command in the OLAP DML Reference.

Setting the HIERLEVELVS property

After you have defined the level-to-hierarchy mapping valueset, then you can set the HIERLEVELVS property on the dimension.

For example, the following code sets the HIERLEVELVS property on the TIME dimension and specifies the valueset named time.hierlvl:

consider time
property 'HIERLEVELVS' 'time.hierlvl'

Set the LEVELDEPTHVAR property to identify hierarchy level depth

Define a level depth variable to identify each hierarchy level's distance from the hierarchy root, then set the LEVELDEPTHVAR property on that dimension.

The top level of the hierarchy has a value of 0.

Defining the level depth variable

The following example defines a level depth variable for the TIME dimension. Note that the variable is defined with a short integer data type. The variable is dimensioned by TIME's level dimension and hierarchy dimension. For information about defining a level dimension, see "Set the LEVELDIM property to identify the names of hierarchy levels". For information about defining a hierarchy dimension, see "Set the HIERDIM property if a dimension has any hierarchies".

define time.lvldepth variable shortinteger <time.leveldim, time.hierdim>

You then add assign an integer for each hierarchy level that identifies its depth in the hierarchy.

Setting the LEVELDEPTHVAR property

After you have defined the level depth variable, then you can set the LEVELDEPTHVAR property on the dimension.

For example, the following code sets the LEVELDEPTHVAR property on the TIME dimension and specifies the variable named time.lvldepth:

consider time
property 'LEVELDEPTH' 'time.lvldepth'

Set the LEVELLDSC property to identify hierarchy level descriptions

Define a level long description variable to hold the description of each hierarchy level, then set the LEVELLDSC property on that dimension.

Defining the level long description variable

The following example defines a level long description variable for the TIME dimension. Note that the variable is defined with a text data type. It is dimensioned by TIME's hierarchy level dimension, named time.leveldim. For information about defining a hierarchy level dimension, see "Set the LEVELDIM property to identify the names of hierarchy levels".

define time.lvlldsc variable text <time.leveldim>

You then add text that describes the names of the levels of the TIME dimension's hierarchies.

Setting the LEVELLDSC property

After you have defined the level long description variable, then you can set the LEVELLDSC property on the dimension.

For example, the following code sets the LEVELLDSC property on the TIME dimension and specifies the variable named time.lvlldsc:

consider time
property 'LEVELLDSC' 'time.lvlldsc'