MSAS: Working with Levels and Hierarchies

Dimensions are defined as structural attributes of a cube made up of levels arranged in hierarchies. A level is a set of members of a dimension organized such that all members of the set are at an equal distance from the root of the hierarchy. A hierarchy is the set of members in a dimension and their positions relative to one another.

Working with Levels and hierarchies

The basic facts relating to levels was discussed in “Building Dimensions using Dimension Editor”. In this tutorial we will go a little deeper into how to work with levels and hierarchies. Before proceeding with the ways in which we work with levels let us briefly recapitulate all that we studied in the previous tutorial.

Levels can be defined using the Cube Editor, Dimension Editor or the Dimension Wizard. The Cube Editor and the Dimension Editor require the user to manually define the various levels and the properties of the levels. The Dimension Wizard makes this job easy for the user.

Levels are defined within the dimension to specify the content and structure of the dimension. The definitions of levels determine the relative positions of the members that are to be included in the hierarchy. The hierarchy can be defined at the most summarized level or at the most detailed level.

Levels get created when the Dimension is created using the Dimension wizard, editor or the cube editor tools. The properties of the levels can also be set using these tools.

Levels can be defined in three ways depending on the type of dimension

1. Regular dimensions require a column to be selected from the dimension table to supply the members and components of the level. 
2. Parent child dimensions require a distinction to be made between the level object and a level in the hierarchy. The parent child dimension always contains only one level object but hierarchies of multiple levels. Two columns have to be selected from the dimension table. One column will identify the members of the dimension and the other column identifies the parents of the members. Each row in the dimension table has a parent child linkage. All linkages combine to determine the hierarchy of the dimension. The column that contains the member identifiers is the column that supplies all the members of the dimension. 
3. Virtual dimensions require the selection of a member property in another dimension or in a column table. This member property or column supplies the members of the level.

The Member Key Column property controls the identification of members included in a level. The vertical positions of members in a dimension hierarchy (of a regular or virtual dimension) are controlled by the order of levels in the dimension definition. Each level in the hierarchy is produced by a level in the definition and the vertical order in the definition matches the levels’ order in the hierarchy. The horizontal position of the member is determined by the level in which it is included. In parent-child dimension, only a single level can be defined, but it produces multiple levels in the hierarchy. The members’ vertical positions are determined by the levels’ Parent Key Column and Root Member If properties and the horizontal positions are determined by the Order By property.

The (All) level is a special kind of level and is optional in all dimensions except the virtual dimension. If it is defined it is shown as the highest level in the dimension. The member in this level is an aggregation of the values of the members in the immediate subordinate levels.

Levels are subordinate to Dimensions and cubes in a database and are included within the dimensions and the cubes that are made up of these dimensions. Member properties are subordinate to levels.

Levels are graphically displayed as a series of small squares in Analysis Manager. The lower the level, the greater the number of squares indicating the level. There is no icon for All levels in Analysis Manager.