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