1. Types of Hierarchies

There are attribute (or flat), Parent-Child and multilevel hierarchies. The hierarchy where each level contains members of its own type is called multilevel. Figure 1 shows a classic example of a multilevel hierarchy.

Figure 1

In this hierarchy, each of the levels Year, Quarter, Month contains members of corresponding types. Another classical example of a multilevel hierarchy is "geographical position". The usual levels for a geography hierarchy are Continent, Country, State, City.

As a rule, the in-depth position of a hierarchy member corresponds with the ordinal number of its level. A hierarchy that fails to follow this rule is called ragged herarchy. The example of a ragged hierarchy is given in Figure 2.

Figure 2  

In a Parent-Child hierarchy it's impossible or difficult to determine a level structure the way it's done for a multilevel hierarchy. For example, in an organizational diagram, in general, it's difficult to find a principle according to which some employees can be attributed to any hierarchical level. An example of a hierarchy of the Parent-Child type is shown in Figure 3.

 

Figure 3

In terms of data warehouses Parent-Child hierarchies are usually described by schemas where dimension tables have Parent-Child relations, i.e. each hierarchy member has only one parent from the same table. Unlike them multilevel hierarchies are usually described by !MISSING PHRASE 'snowflake schemas:snowflake schema'! with data organized in two or more dimension tables that may be joined in different patterns.

2. Review of Hierarchies in the RadarCube

In the RadarCube library, hierarchies are described by the instances of the TCubeHierarchy type. Each of them has the property Levels containing the collection of instances of the TCubeLevel type that describe levels of the hierarchy. Members of the hierarchy are described by instances of the TCubeMember type. Figure 4 shows a diagram of the RadarCube objects related to hierarchies, hierarchical levels and hierarchical members.

Figure 4

3. Attribute (or flat) hierarchies

These are simple flat lists with no hierarchical structure. In the Levels collection of such hierarchy there is only one instance of TCubeLevel. All its members are listed in the Members collection.

4. Parent-Child Hierarchies

Parent-Child hierarchies, just like flat ones, have only one instance of TCubeLevel in their Levels list. But only the root members of such hierarchies are listed in the Members collection. But for every TCubeMember instance there is a Children and a Parent properties. The former includes a list of instances of the TCubeMember type that serve as children to the given hierarchical node. The latter indicates a direct parent of a given hierarchical node. For nodes listed in the TCubeLevel.Members, the Parent property value equals null, if they are roots of a tree.

5. Multilevel hierarchies

In multilevel hierarchies everything is a bit more complicated. In addition to the Children collection, responsible for the Parent-Child hierarchy relations within the level, we should also take into account the NextLevelChildren collection that contains the list of the same parents’ children members placed on the following level.

Let's have a look at the example shown in Figure 1. The instance of the TCubeHierarchy type, responsible for the hierarchy altogether, in its property Levels contains three instances of TCubeLevel, each of them is responsible for its own hierarchy level.

The first instance (level) named Year has a single instance of the TCubeMember type (named "2004") in the property Members. This member has the Parent property set to the null value. The Children collection of this element is empty (since the level has no Parent-Child relation) whereas the NextLevelChildren collection contains four members with the names from Quarter 1 to Quarter 4, whose Parent property contains a reference to the "2004" member.

In contrast to Figure 1, Figure 3 shows an example of a hierarchy of another type: it has only one level. In its Members collection there is a single object - Paul West whose Children collection has three child members whereas the NextLevelChildren collection is empty.

Despite that each TCubeMember object has two collections - Children and NextLevelChildren, in fact, only one of those can be filled out. This condition guarantees that you'll be able to represent your hierarchy as a tree. The same condition explains the necessity to set the Parent property for all hierarchy members, except for the root members of the first level.

ragged hierarchy in RadarCube are created in a similar manner. But to make them operate correctly a missing member is substituted with a virtual one with an empty DisplayName property. These virtual members are created automatically and, unlike all others, have their IsRaggedVirtual property set to true.

All this is true not only for the Cube classes, but for the Grid classes as well. At the point of initialization the structure of a Grid's hierarchy is the same as in the Cube. You can change it by adding or deleting groups and calculated members, but still on the condition that only the leaves of each Parent-Child hierarchy can have children on the below level.

5. Hierarchies at Design Time

The information about the members and the levels of any hierarchy in the RadarCube is available after its initialization, which is automatically performed at the point of moving the hierarchy to one of the three active areas (row area, column area or page area) or manually by calling the InitHierarchy method.

If we use the TMDCube component (which is a client to MS Analysis) as a data source, then before the hierarchy initialization we will have no information about its levels or members. By examining the Origin property, we will be able to know only the type of the hierarchy.

If for a source data we use the TOLAPCube component, which works directly with warehouse tables, we can get the information about the internal structure of a hierarchy through additional properties that are not available in the TMDCube.

The basic principle of building multilevel hierarchies is that each level represents a set of logically associated members. An example in Figure 1, the members of the first level representing years, second - quarters, third - months.

In a data warehouse, a multilevel hierarchy is usually represented with several fields of a fact table, or, which is more often, with several dimension tables.

In TOLAPCube, when defining the structure of a multilevel hierarchy, such as in Figure 1, at first, for each of the levels its own attribute hierarchy should be created (with TCubeHierarchy.Origin = hoAttribute), and only after this these three hierarchies can form the fourth, the multilevel one (see Figure 5).

 

Figure 5

The list of attribute hierarchies, contained in a multilevel hierarchy, is available through the property ChildrenList. The same hierarchies are, of course, placed in the TCubeDimension.Hierarchies collection and are also available to an end user, for whom all this is displayed as follows in the "Cube editor" window:

 

Figure 6

Naturally, during the initialization of a hierarchy the attribute hierarchies are the first to be initialized. Each of them is placed according to its level (the TCubeLevel object). Thus, a multilevel hierarchy always contains as much levels as it has nested attribute hierarchies.

Each of the levels of the initialized multilevel hierarchy has the property SourceHierarchy referencing to the attribute source hierarchy.