Building Cubes and Administering Cubes

Defining Cube Hierarchies

When you define cube dimensions, levels, and hierarchies in SAS OLAP Cube Studio, a default hierarchy for a dimension is automatically created if a hierarchy is not explicitly defined. This default hierarchy includes all levels that were specified for the current dimension and the order they were listed in for the dimension. In addition, if you define multiple hierarchies and do not select a default, then the default is automatically assigned to the first hierarchy that is created for the dimension. On the Dimension Designer - Hierarchy window, you can click the Default button to set a selected hierarchy as the default for the dimension.

Defining Multiple Hierarchies for a Dimension

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:

A level in a dimension might be used in more than one hierarchy within that dimension. However, levels cannot be used in hierarchies that are not defined within the dimension that the level is defined in.
Each level must be used in at least one hierarchy.
Levels from the same dimension that are picked for an aggregation must be in the drill order for at least one hierarchy in that dimension.
You cannot share levels between dimensions.

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 only have one TIME dimension for a cube. The dimension hierarchies also have some restrictions:

The first hierarchy that is defined for the dimension is designated as the default. When there are multiple hierarchies, you can designate the default hierarchy for the dimension.
Hierarchy names must be unique across the cube. If there is a single hierarchy for a dimension, then its name must be the name of the dimension. Also, dimension and hierarchy names cannot be the same as a level name within that dimension.
For any cube that is loaded with a star schema, in which a dimension table represents multiple hierarchies for that dimension, the dimension key that is used to join the dimension table to the fact table will be used for all hierarchies of that dimension.

HIERARCHY Statement

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

 hierarchy campaigns
    levels=(campaign_type campaign sub_campaign);

Cube Designer

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.

Note: You can modify existing hierarchies by selecting a hierarchy and clicking Modify. You can also assign a default hierarchy by selecting a hierarchy and clicking Default. The first hierarchy is automatically the default hierarchy. [cautionend]

Note: An exception to defining multiple hierarchies for a dimension is the TIME dimension. Levels in hierarchies in the TIME dimension must follow a prescribed order that is determined by the numeric value that is assigned to the type. This order is from the smallest value (Year, 16) to the greatest value (Seconds, 3,096). [cautionend]

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.

Defining Ragged and Unbalanced Hierarchies for a Dimension

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 instance, at company X the sales staff are located in different regions and cities in different countries. A balanced hierarchy might look like this:

global sales president (top of hierarchy)
sales presidents (per country)
regional sales managers
city sales managers

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 drillpath of the cube data.

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

Note: Existing SAS 9.2 OLAP cubes that have been updated with one or more new members for a hierarchy can possibly contain ragged hierarchies and must be rebuilt in the second maintenance release after SAS 9.2. [cautionend]

Defining Ragged and Unbalanced Hierarchies in SAS OLAP Cube Studio

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:

Ragged Hierarchies

Located on the Advanced Cube Options dialog box, on the Cube Designer - General page, this tab enables you to specify character and numeric missing member information. By default, no missing member information is indicated with the value None.

Dimension Designer - Level Properties

Ragged - Ignore Missing Members specifies whether to ignore or use global or hierarchy-specific ragged hierarchy settings. To ignore settings, set this property to True. To use the settings, set this property to False. By default, this is set to False.
Ragged - Designate Missing Members specifies that the Cube Designer use the specified string to identify missing values and override any global or hierarchy-specific ragged hierarchy settings. You can use up to 256 characters. The value of the True/False setting in Ragged - Ignore Missing Members controls whether or not you override any global or hierarchy-specific ragged hierarchy settings.

Dimension Designer - Define a Hierarchy

You can select one of these options from the Ragged Hierarchies tab:

Ignore. From this drop-down list, select True to ignore the global missing member settings that you entered at the Advanced Cube Options dialog box. Select False to use the global settings for the current hierarchy.
Character. For this hierarchy only, enter a maximum of 256 characters that will be used to identify missing character members.
Numeric. For this hierarchy only, enter a maximum of 256 characters that will be used to identify missing numeric members.

Defining Ragged and Unbalanced Hierarchies with PROC OLAP

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 HIERARCHY 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, HIERARCHY statement, and LEVEL statement. In the PROC OLAP and HIERARCHY 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 allows you to specify separate values for each level in a hierarchy, regardless of any similar values that were specified in preceding HIERARCHY and PROC OLAP statements.

The HIERARCHY 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.

Ragged Hierarchies and Unique Member Names

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.

Top of Page