Levels and Members
A level is an element of a dimension hierarchy. Levels describe the hierarchy from the highest (most summarized) level to the lowest (most detailed) level of data.
Levels exist only within dimensions. They are based on columns in a
Levels are defined within a dimension to specify the contents and structure of the dimension's hierarchy. That is, the level definitions determine the members that are included in the hierarchy and their positions relative to one another within the hierarchy.
Levels are created when you create a dimension in the Dimension Wizard, Dimension Editor, or Cube Editor. After you create a dimension, you can maintain its levels in Dimension Editor (if the dimension is shared) or Cube Editor (if the dimension is private). In the editors you can set the properties of the levels.
After shared dimensions and their levels are created, measures are typically the next objects to be created. However, they are created in the process of creating the cubes that contain them.
For example, a Calendar dimension contains the levels Year, Quarter, and Month. The relationship between the levels and members of the Calendar dimension (a regular dimension) is shown in the following diagram.
For example, a Calendar dimension contains the levels Year, Quarter, and Month.
| Year | Quarter | Month |
|---|---|---|
| 1999 | Quarter 1 | Jan |
| 1999 | Quarter 1 | Feb |
| 1999 | Quarter 1 | Mar |
| 1999 | Quarter 2 | Apr |
| 1999 | Quarter 2 | May |
| 1999 | Quarter 2 | Jun |
| 1999 | Quarter 3 | Jul |
| 1999 | Quarter 3 | Aug |
| 1999 | Quarter 3 | Sep |
| 1999 | Quarter 4 | Oct |
| 1999 | Quarter 4 | Nov |
| 1999 | Quarter 4 | Dec |
You can define a level in one of three ways, depending on the variety of dimension in which the level is defined. For regular dimensions, select a column from the dimension table; this column supplies the members, or components, of the level.
When working with parent-child dimensions, it is important to distinguish between the level object and a level in the hierarchy. A parent-child dimension always contains only one level object, but the hierarchy of the dimension usually contains multiple levels. For a parent-child dimension, select two columns from the dimension table. One column identifies the members of the dimension, and the other column identifies the parents of the members. For each row in the table, the two columns identify a parent-child linkage. All the linkages are combined to determine the hierarchy of the dimension. The column that contains the member identifiers is the column that supplies all of the members of the dimension.
For a virtual dimension, select a member property in another dimension or in a column in the table of another dimension. This member property or column supplies the members of the level.
The identification of members to be included in a level is controlled by its Member Key Column property. This is the same column that supplies the level's members as described earlier in this topic. (The initial value of this property is set when you create a dimension with the Dimension Wizard.)
In a regular or virtual dimension, the vertical positions of members within the dimension's hierarchy are controlled by the order of the levels in the dimension's definition. Each level in the definition produces a level in the hierarchy. The levels' vertical order in the definition matches the levels' order in the hierarchy. The horizontal position of a member is determined by the level in which it is included.
In parent-child dimensions, the vertical positions of members are determined differently. In a parent-child dimension, only a single level can be defined (besides the optional (All) level), but it usually produces multiple levels in the hierarchy. Members' vertical positions are determined by the level's Parent Key Column and Root Member If properties. Members' horizontal positions within a level are determined by the level's Order By property.
The (All) level is a special kind of level. Except in virtual dimensions, the (All) level is optional. If defined, it is the highest level in the dimension. It contains a single member whose value is the aggregation of the values of the members in the immediately subordinate level.
Levels are immediately subordinate to the following objects in the object hierarchy:
- Dimension immediately beneath a database (shared dimension)
These levels are in dimensions that are shared among the cubes in the database.
- Dimension immediately beneath a cube (shared or private dimension)
These levels are in dimensions that are included in the cube. These dimensions may be derived from the shared dimensions in the database or they may be private (that is, unshared).
Member properties are immediately subordinate to levels. Member properties are optional objects that provide end users with additional information about members.
In Analysis Manager, levels are identified by icons containing very small squares. The number of squares indicates the level's position in the dimension's definition. Except for the (All) level, the highest level's icon has one square, the second level's icon has two squares, and so on. For example, the following three icons represent the top three levels in a dimension.
The icon for the (All) level is not displayed in Dimension Editor or Cube Editor but is displayed in some dialog boxes. The icon for the (All) level looks like this.
If you are programming with Decision Support Objects (DSO), the class types associated with the level are clsAggregationLevel, clsDatabaseLevel,clsCubeLevel, and clsPartitionLevel.