Metadata That Describes Dimension Hierarchies

Metadata that is required for the display of dimension hierarchies

If a dimension has one or more hierarchies, and you want to make it possible to display those hierarchies on a Java client, then the following metadata is required for that dimension:

• "Set the HIERDIM property if a dimension has any hierarchies"

• "Define a NUMHIERFRM object to determine the number of hierarchies"

• "Set the HIERDEFAULT property to specify the default hierarchy"

• "Set the HIERLDSCVAR property to display hierarchy descriptions"

• "Set the DRILLINFOFRM to display information about drill direction"

• "Set the PARENTREL property to identify the hierarchy parent relation"

• "Set the FULLORDER property to identify the hierarchy organization"

If you also want to display information about hierarchy levels, refer to "Metadata That Describes Dimension Hierarchy Levels".

Set the HIERDIM property if a dimension has any hierarchies

Define a hierarchy dimension to hold the name of every hierarchy in a dimension, then set the HIERDIM property on that dimension.

Defining the hierarchy dimension

Use the following steps to define a hierarchy dimension:

1. Define a dimension to act as the hierarchy dimension. The dimension values are the names of the hierarchies in a dimension. You must define a separate hierarchy dimension for every dimension that has hierarchies.

2. Create a self-relation for the dimension. For example, create a dimension that relates the TIME dimension to itself.

3. Limit the self-relation to each hierarchy.

4. Relate the dimension values in the self-relation.

For a detailed example of creating a hierarchy dimension, refer to the RELATION command in the OLAP DML Reference.

Setting the HIERDIM property on its dimension

After you have created a hierarchy dimension, then you can set the HIERDIM property on its dimension.

For example, suppose you create a hierarchy dimension for the TIME dimension named timehierdim. Use the following command to set the HIERDIM property for the TIME dimension.

consider time
property 'HIERDIM' 'timehierdim'

Define a NUMHIERFRM object to determine the number of hierarchies

Define one number-of-hierarchies formula whose return value is the number of that dimension's hierarchies. Because the formula is dimensioned by the dimension dimension, you only need to define one formula for each analytic workspace.

Defining the number-of-hierarchies formula

The number-of-hierarchies formula must have an integer data type and must be dimensioned by the dimension dimension. See "Set the DBDIMDIM property to identify the workspace's dimensions" for more information about the dimension dimension.

The following example defines a number-of-hierarchies formula.

define numhiers formula integer <dbdimdim>
eq if not obj(hasproperty 'HIERDIM' dimexpobj) then 0
   else if not exists(obj(property 'HIERDIM' dimexpobj)) then 0
   else obj(dimmax obj(property 'HIERDIM' dimexpobj))

For information about the HIERDIM property, see "Set the HIERDIM property if a dimension has any hierarchies".

For an example of defining a DIMEXPOBJ object, see "Set the EXPOBJVAR property on the attribute dimension".

Setting the NUMHIERFRM property on the dimension dimension

After you have defined the number-of-hierarchies formula, then you can set the NUMHIERFRM property on the dimension dimension:

consider dbdimdim
property 'NUMHIERFRM' 'numhiers'

Using the number-of-hierarchies formula

The following command uses the number-of-hierarchies formula to determine which dimensions have hierarchies:

limit dbdimdim to numhiers gt 0

Set the HIERDEFAULT property to specify the default hierarchy

If a dimension has more than one hierarchy, set the HIERDEFAULT property on that dimension and specify the name of the hierarchy that you want to be used as the default hierarchy.

Setting the HIERDEFAULT property

For example, suppose that the TIME dimension has two hierarchies: the Calendar hierarchy and the Fiscal hierarchy. These names are specified in the hierarchy dimension; refer to "Set the HIERDIM property if a dimension has any hierarchies" for more information.

If you want to use the Fiscal hierarchy as the default hierarchy for TIME, then use the following commands:

consider time
property 'HIERDEFAULT' 'Fiscal'

For information about naming hierarchies, see "Set the HIERDIM property if a dimension has any hierarchies".

Set the HIERLDSCVAR property to display hierarchy descriptions

To display a text description for a dimension's hierarchies, define a text variable, which must be dimensioned by the hierarchy dimension. Set the HIERLDSCVAR property on the dimension to specify the name of the object you create.

Defining the hierarchy description variable

The following example defines a variable named time.ldsc for the TIME variable. Note that the variable is dimensioned by TIME's hierarchy dimension, NUMHIERTIME.

define time.ldsc variable text <numhiertime>

Setting the HIERLDSCVAR property

After you have defined a hierarchy description variable, then you can set the HIERLDSCVAR property on its dimension.

For example, the following commands set the HIERLDSCVAR property on the TIME dimension and specify the variable named time.ldsc:

consider time
property 'HIERLDSCVAR' 'time.ldsc'

Set the DRILLINFOFRM to display information about drill direction

If information about whether dimension values can be expanded or contracted ("drilled") is important, then you can define a formula, then specify the formula name when you set the DRILLINFOFRM property on the dimension.

Defining the hierarchy drill information formula

The following example defines a formula named time.drill for the TIME dimension. Note that the formula is dimensioned both by the TIME dimension and by its hierarchy dimension, NUMHIERTIME. TIME.PARENT is the parent relation of the TIME dimension.

define time.drill formula integer <time numhiertime>
EQ if statlen(limit(TIME to children using TIME.PARENT
convert(TIME, int))) eq 0 then 0 else if statlen(TIME) eq
statlen(limit(TIME remove children using TIME.PARENT
convert(TIME, int))) then 1 else 2

The possible return values are:

• 0 -- this dimension value has no children in the TIME hierarchy

• 1 -- this dimension value has children and is not expanded

• 2 -- this dimension value has children and is already expanded

Setting the DRILLINFOFRM property

After you have defined the hierarchy drill information formula, then you can set the DRILLINFOFRM property on the dimension.

For example, the following code sets the DRILLINFOFRM property on the TIME dimension and specifies the formula named time.drill:

consider time
property 'DRILLINFOFRM' 'time.drill'

Set the PARENTREL property to identify the hierarchy parent relation

The hierarchy parent relation is a self-relation in which you specify the parent of each dimension value. For example, suppose the TIME dimension values are months, quarters, and years. In the Calendar hierarchy, Q1 is the parent of January, February, and March. Therefore, you define a relation that relates TIME to itself, then relate Q1 as the parent of January, February, and March.

Once you have defined and populated the hierarchy parent relation, then set the PARENTREL property on that dimension.

Defining the hierarchy parent relation

The following example defines a self-relation for the TIME dimension. Note that the TIME dimension is related not only to itself but also to its hierarchy dimension, numhiertime.

define time.parent relation time <time, numhiertime>

You relate the dimension values (for example, for the Calendar year dimension values) in the self-relation (time.time) just as you would for a self-relation that has only one hierarchy. See the DEFINE RELATION command in the OLAP DML Reference for details.

Setting the PARENTREL property

After you have defined the hierarchy parent relation, then you can set the PARENTREL property on the dimension.

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

consider time
property 'PARENTREL' 'time.parent'

Set the FULLORDER property to identify the hierarchy organization

If a dimension has one or more hierarchies, define a hierarchy full order variable to identify the order of dimension values within each hierarchy, then set the FULLORDER property on that dimension.

Defining the hierarchy full order variable

The following example defines a variable for the TIME dimension. Note that the variable is defined with a decimal data type, and it is dimensioned by TIME and its hierarchy dimension, numhiertime.

define time.fullorder variable decimal <time, numhiertime>

You then assign a number for each combination of a TIME dimension value and a hierarchy name as the variable data.

Setting the FULLORDER property

After you have defined the hierarchy full order variable, then you can set the FULLORDER property on the dimension.

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

consider time
property 'FULLORDER' 'time.fullorder'