Defining Cube Hierarchies

SAS OLAP cubes are organized into dimensions and levels of data. The levels are then arranged into hierarchies. After an initial hierarchy has been created, you can define additional hierarchies for a single dimension of a cube. This enables you to have multiple possible drill paths for the same data. When you create more than one hierarchy for a dimension, the levels have some restrictions:

You can arrange the levels in a hierarchy in any order. The one exception to this is the TIME dimension. Levels in hierarchies in the TIME dimension must follow a prescribed order that is determined by the numerical value that is assigned to the type. This order is from the smallest value (Years, 16) to the greatest value (Seconds, 3,096). You can have only one TIME dimension for a cube. The dimension hierarchies also have some restrictions:

The HIERARCHY statement is used with the PROC OLAP statement when you define a cube:

 hierarchy campaigns
    levels=(campaign_type campaign sub_campaign);

You can establish multiple hierarchies by using the Cube Designer – Dimension page, which is located in the SAS OLAP Cube Studio Cube Designer. To add a hierarchy to an existing dimension, select a dimension, and then click Modify. This opens the Dimension Designer – General page. It is populated with the values for the selected dimension. Select Next until you reach the Dimension Designer - Hierarchy page. Select Add to create an additional hierarchy.

On the Dimension Designer – Define a Hierarchy page, you can define a new hierarchy and select the different levels and their order for the hierarchy.

Dimension levels are arranged in one or more hierarchies. Hierarchies, by process of ordering, have a branching arrangement, and the different member levels have parent and child relationships. For example, at company X the sales staff are located in different regions and cities in different countries. A balanced hierarchy might look like this: Because of differences in the cube data, hierarchies are often not balanced and possibly have missing members. For example, some sales regions might not have sales managers assigned to a specific city. Or some countries might not have sales regions, just cities. These real-world scenarios would create hierarchies that have missing member data and possibly ragged hierarchies. This affects the drill path of the cube data.

You can also drill to missing members within a path and continue to drill down to members that are present.

The Cube Designer in SAS OLAP Cube Studio enables you to specify the missing members for a hierarchy and the type of data that is missing. Here are the Cube Designer pages that enable you to specify missing member information:

To create ragged and unbalanced hierarchies with PROC OLAP, you specify options that allow the procedure to skip over members of levels that have captions with specified values. The presence of these skipped members constitutes a ragged or unbalanced hierarchy. In a ragged hierarchy, skipped members in a given level can have descendants; skipped members are used to enable drill-down through empty levels. In an unbalanced hierarchy, the skipped members do not have descendants; members are skipped in order to create hierarchies where certain branches do extend to all available levels.

For an example of the creation of a ragged hierarchy, assume that a cube defines information about sales representatives. The Geography hierarchy is defined to have the levels Country, State, Region, and City. In this particular sales organization, the state of Nebraska has no regions, but it does have sales representatives in a number of cities. This ragged hierarchy can be shown as follows:

Cube           Sales Representatives

Hierarchy          ...Geography...
                         |
Levels:                  |
Country              ...USA...
                         |
State    ...Montana     Nebraska   North Carolina...
            |     |         |      |      |      |
Region     East  West      –0–  Central  East   West
            |     |         |      |      |      |
City        |  Billings...  |      |      |      Asheville...
            |               |      |    Greenville...
          Helena...         |   Charlotte...
                            |
         ...Council Bluffs, Lincoln, Sioux City...

The Geography hierarchy is ragged because of the need to skip the Region level, and because the skipped member (Nebraska) has descendants.

To create the ragged hierarchy shown above, the Nebraska member needs to be defined with one member at the Region level. That member needs to have a caption that matches the value of the EMPTY_CHAR= option that is defined in the respective DIMENSION statement.

In the resulting cube, drilling down from Nebraska takes you directly from the State level to the City level.

For an example of the creation of an unbalanced hierarchy, assume that a cube named Employees has a hierarchy named Organizations. In that hierarchy there are various divisions, departments, and groups. As shown below, some departments lack groups:

Cube                 Employees

Hierarchy        ...Organizations...
                         |
Levels:                  |
Div    ...R&D          Sales    Tech Support...
           |             |           |
Dept    Java  Platform   |   Java Support  Platform Support
           |     |       |           |      |
Group      |     |       |          –0–    –0–
           |     |       |
           |     |   Global   Marketing   U.S.
           |     |
           |   Hosts   Performance   Unit Test
           |
        Client   Mid-Tier   Server

The preceding hierarchy is unbalanced because the Tech Support level has no descendants at the Group level. To implement this unbalanced hierarchy, the levels Java Support and Platform Support would have to be defined with captions that matched the value of the EMPTY= option in their respective LEVEL statements.

The options that implement ragged and unbalanced hierarchies are found in the PROC OLAP statement, DIMENSION statement, and LEVEL statement. In the PROC OLAP and DIMENSION statements, you can specify separate caption values for character and numeric levels using the options EMPTY_CHAR= and EMPTY_NUM=. Similarly, the EMPTY= option of the LEVEL statement enables you to specify separate values for each level in a hierarchy, regardless of any similar values that were specified in preceding DIMENSION and PROC OLAP statements.

The DIMENSION and LEVEL statements also provide the IGNORE_EMPTY option, which specifies that any prior specifications of EMPTY_CHAR= or EMPTY_NUM= are to be ignored for that hierarchy or level.

In a ragged hierarchy, the parent of a member might not be at the level directly above that member. Furthermore, not all children of a member are necessarily at the same level. This can lead to a situation where two children have the same unique name.

For example, in a geography hierarchy, you might have the levels state, county, and city. The state Washington might have a child at the county level called Olympia and another child at the city level, also named Olympia. The city member is not a descendant of the county member of the same name. It is a child of Washington.

In a ragged hierarchy, levels can have an unconventional structure, and unpopulated levels are not assigned a token or placeholder. As a result, the unique name for the county member is Geography.[All Geography].Washington.Olympia, and the unique name for the city member is Geography.[All Geography].Washington.Olympia.

The result of this anomaly is that the city member cannot be asked for by a unique name in a query, either through MDX or an OLE DB for OLAP (ODBO) request for metadata. It will be returned in any set that contains it so the data that is associated with it is not lost. The same applies to the children of a member such as Olympia. Because the server searches through the hierarchy to validate member names, a request by name for a child of Olympia the city will result in a bad member name error. This is because the server actually searches under the county Olympia.

This situation occurs only when two members with the same name share a parent. Any number of members named Olympia could exist under other parents with no unusual results.