Oracle9i OLAP Services Developer's Guide to the OLAP DML Release 1 (9.0.1) Part Number A86720-01 |
|
Creating and Using Analytic Workspace Metadata, 4 of 7
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:
If you also want to display information about hierarchy levels, refer to "Metadata That Describes Dimension Hierarchy Levels".
Define a hierarchy dimension to hold the name of every hierarchy in a dimension, then set the HIERDIM property on that dimension.
Use the following steps to define a hierarchy dimension:
For a detailed example of creating a hierarchy dimension, refer to the RELATION command in the OLAP DML Reference.
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 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.
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".
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'
The following command uses the number-of-hierarchies formula to determine which dimensions have hierarchies:
limit dbdimdim to numhiers gt 0
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.
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".
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.
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>
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'
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.
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:
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'
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.
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.
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'
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.
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.
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'
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|