Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
The HIERHEIGHT function returns the value of a node at a specified level for the first value in the current status list of a hierarchical dimension.
To populate a previously-defined relation with the values of a specified hierarchical dimension by level, use the HIERHEIGHT command.
Return Value
The data type returned by HIERHEIGHT is the data type of the dimension value of family-relation.
Syntax
HIERHEIGHT(family-relation [,] level)
Arguments
A child-parent self-relation for the hierarchical dimension. The values of family-relation are the parents.
An INTEGER
value that represents a level of the hierarchical dimension. The value 1
(one) represents the lowest-level of the hierarchical dimension.
Notes
Limiting the Hierarchical Dimension
The HIERHEIGHT function always returns a single value of the hierarchical dimension. When you do not limit the hierarchical dimension to a single value before calling the HIERHEIGHT function, the HIERHEIGHT function executes against the first value in the current status list of the dimension. Typically, you either limit the hierarchical dimension to a single value before you call the HIERHEIGHT function or you use the HIERHEIGHT function after a FOR statement in order to execute the HIERHEIGHT function for each value of the hierarchical dimension.
Examples
Example 16-11 Using HIERHEIGHT as a Simple Command
Assume that your analytic workspace has a hierarchical dimension named geography
and a relation named g0.stanparent
that is a self-relation of the geography
values for the Standard
hierarchy of geography
.
DEFINE g0.newparent RELATION geography <geography> LD Parent-child when hierarchy of geography is 1
Issuing a statement like REPORT g0.stanparent
displays the values in g0.stanparent
.
GEOGRAPHY G0.STANPARENT ---------------- ---------------- World NA Americas World Canada Americas Toronto Canada Montreal Canada Ottawa Canada ... ... USA Americas Boston USA LosAngeles USA ... ... Mexico Americas Mexicocity Mexico Argentina Americas BuenosAires Argentina Brazil Americas Saopaulo Brazil Colombia Americas Bogota Colombia Australia World East.Aust Australia Sydney East.Aust Madrid Spain Budapest Hungary Athens Greece Vienna Austria Melbourne East.Aust Central.aust Australia Tai-pei Taiwan Singapore Asia Adelaide Central.Aust Bangkok Thailand West.aust Australia Newdelhi India Perth West.Aust Bombay India Malaysia Asia Europe World France Europe Caen France Paris France
Now you limit GEOGRAPHY to the value Americas
by issuing the following OLAP DML statement.
LIMIT geography TO 'Americas'
When you use the HIERHEIGHT function to find the node for Americas
for the lowest-level of the hierarchy (level 1) by issuing the following OLAP DML statement.
REPORT HIERHEIGHT(g0.stanparent 1)
The following report is produced.
HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ NA
When you use the HIERHEIGHT function to find the node for Americas
for the highest-level of the hierarchy (level 4
) by issuing the following OLAP DML statement.
REPORT HIERHEIGHT(g0.stanparent 4)
The following report is produced.
HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ World
When you use the HIERHEIGHT function to find the node for Americas
for the levels 2
and 3
of the hierarchy by issuing the following OLAP DML statements.
REPORT HIERHEIGHT(g0.stanparent 2) REPORT HIERHEIGHT(g0.stanparent 3)
The following reports are produced.
HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ NA HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ Americas
Notice that the output for each level corresponds in between the values that are created for a relation created using HIERHEIGHT command. For example, assume you created a relation named geog.stanhierrel
for the standard hierarchy for geography
and limit geography
to 'Americas
. A report of geog.stanhierrel
would show the same geography
values for each level.
LIMIT geography TO 'AMERICAS' REPORT DOWN geography geog.stanhierrel ---------------------------GEOG.STANHIERREL-------------------- ----------------------------GEOG.LVLDIM------------------------ GEOGRAPHY 1 2 3 4 ---------------- ---------------- ---------------- ---------------- ------------ Americas NA NA Americas World
Example 16-12 Using HIERHEIGHT After a FOR Statement
Assume that your analytic workspace has a program named findnodes
that finds the nodes of all of the geography
values in status.
DEFINE FINDNODES PROGRAM PROGRAM VARIABLE level INTEGER FOR geography DO counter = 1 WHILE counter LE statlen(geog.lvldim) DO REPORT HIERHEIGHT(g0.stanparent level) level = level + 1 DOEND DOEND END
Assume also that you limit geography
to Americas
and Asia
and call the HIERHEIGHT function for each level of the Standard
hierarchy by issuing the following OLAP statements.
LIMIT geography TO 'Americas', 'Asia' CALL findnodes
The output of the findnodes
program for the geography
values Americas
and Asia
is follows. The program first reports on the value of each level for Americas
is provided. Then it reports on the value of each level for Asia
.
HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ NA HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ NA HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ Americas HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ World HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ NA HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ NA HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ Asia HIERHEIGHT(G0.STANPARENT COUNTER) ------------------------------ World
Notice that the output for each level corresponds in between the values that are created for a relation created using the HIERHEIGHT command
LIMIT geography TO 'Americas' 'Asia' REPORT DOWN geography geog.stanhierrel ---------------------------GEOG.STANHIERREL-------------------- ----------------------------GEOG.LVLDIM------------------------ GEOGRAPHY 1 2 3 4 ---------------- ---------------- ---------------- ---------------- ------------ Americas NA NA Americas World Asia NA NA Asia World