Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)

Part Number A95298-01
 Home Book List Contents Index Master Index Feedback

Defining Data Objects, 9 of 11

## Defining Hierarchical Dimensions and Variables That Use Them

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
```

### Defining a Variable with a Hierarchical Dimension

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.

### Example: Variable with a Hierarchical Dimension

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.

Text description of the illustration hiervar.gif

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
```