Release 2 (9.2)
Part Number A95298-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i OLAP Developer's Guide to the OLAP DML Release 2 (9.2) Part Number A95298-01 |
|
Defining Data Objects, 9 of 11
A hierarchical dimension is a means of organizing and structuring parent-child (one-to-many) data within a single dimension and using self-relations to organize the values of the hierarchical dimension into groups. A hierarchy exists when values within a dimension are arranged in levels, with each level representing the aggregated total of the data from the level below. Some dimensions have multiple hierarchies.
Hierarchical dimensions allow you to store data of varying levels of aggregation within a single variable. This type of storage affords a quicker response time for users who want to view the data, particularly when the variable is large.
Rather than defining two separate dimensions, one for city and the other for region, you could define a hierarchical dimension named geography that contains both city and region values.
GEOGRAPHY -------------- EAST WEST BOSTON SAN FRANCISCO SEATTLE
You use a hierarchical dimension to define a variable that contains data of varying levels of aggregation within a single variable. This type of storage affords a quicker response time for users who want to view the data, particularly when the variable is large.
Frequently, the cells in the variable that correspond to upper level values in the hierarchical dimension contain the sum or total of the values in the cells of the variable that correspond to the lower level dimension values. For example, in a sales variable that is defined with a hierarchical dimension representing time, the cells of the variable for each quarter might represent the total sales for the months in the quarter.
After you have defined a variable with hierarchical dimensions, you can add variable data to the lowest level of the hierarchy, and then calculate or aggregate the values for the higher levels of the hierarchy. Conversely, you can distribute or allocate data from higher levels to lower levels of the hierarchy.
See Also:
|
The conceptual diagram below illustrates the geography dimension that contains values for both cities and regions, the geo.geo relation that defines the relationships between cities and regions, the division dimension that contains the list of divisions, and the costs variable that contains the expenses for each division by city and the totals by region.
The division and geography dimensions have the following values.
DIVISION -------------- DIVA DIVB GEOGRAPHY -------------- EAST WEST BOSTON SAN FRANCISCO SEATTLE
Assume that the geo.geo relation was defined using the following command.
define geo.geo relation geography <geography>
After region values have been assigned to the city values in the geo.geo self-relation, a report of geo.geo produces the following.
GEOGRAPHY GEO.GEO -------------- ---------- EAST NA WEST NA BOSTON EAST SAN FRANCISCO WEST SEATTLE WEST
If you enter data at the lowest level (city level) of costs, then it has the values shown below.
------------------------COSTS------------------------------ ----------------------GEOGRAPHY---------------------------- DIVISION EAST WEST BOSTON SAN FRANCISCO SEATTLE --------- ---------- ---------- ---------- ------------- ---------- DIVA NA NA 27,600.00 10,000.00 40,000.00 DIVB NA NA 30,000.00 12,000.00 50,000.00
After you aggregate the data, the costs variable has values in all of its cells, including the cells for the totals for the East and West regions.
------------------------COSTS------------------------------ ----------------------GEOGRAPHY---------------------------- DIVISION EAST WEST BOSTON SAN FRANCISCO SEATTLE --------- ---------- ---------- ---------- ------------- ---------- DIVA 27,600.00 50,000.00 27,600.00 10,000.00 40,000.00 DIVB 30,000.00 62,000.00 30,000.00 12,000.00 50,000.00
|
 Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|
