Skip to main content

Working with Dimensions and Hierarchies

This page discusses hierarchies and dimensions in Business Intelligence. These elements are containers for levels but also have their own purposes.

Also see Accessing the BI Samples.

Introduction to Dimensions and Hierarchies

Most MDX functions refer directly to levels or to their members. In MDX, however, levels belong to hierarchies, which belong to dimensions. Hierarchies and dimensions provide additional features beyond those provided by levels.

Hierarchies are a natural and convenient way to organize data, particularly in space and time. For example, you can group cities into countries, and countries into regions. In such cases, it is useful to be able to query for the child cities of a given country, or to query for the parent postal region for a given country. A cube defines the hierarchies among the levels, and MDX provides functions that enable you to work with the hierarchy, so that you can write such queries.

In MDX, a dimension contains one or more hierarchies that specify how to categorize the records in a similar manner. There is no formal relationship between two different hierarchies or between the levels of one hierarchy and the levels of another hierarchy. The purpose of a dimension is to define the default behavior of its hierarchies and levels.

The Measures Dimension

All measures belong to a special dimension called Measures. This dimension implicitly includes a single hierarchy that has no name. This hierarchy does not include levels. The members of this hierarchy are the measures.

The All Level

Other than the Measures dimension, each dimension can also define a special, optional level, which appears in all the hierarchies of that level: the All level. If defined, this level contains one member, the All member, which corresponds to all records in the cube.

For a given dimension, the actual name of the All member depends upon the cube definition. For example, All Patients is the All member for the AgeD dimension in the sample.

Example

If we use the cube command in the MDX shell, we see the following elements in the demomdx cube:

Elements of cube 'demomdx':
-----------------------------------------
...
Dimensions
...
 HomeD
    H1
      ZIP
      City
...

The HomeD dimension contains one hierarchy (H1), which contains two levels:

  • The ZIP level

  • The City level

In a given hierarchy, a level is the parent of the level that is listed after it. This means, for example, that ZIP is the parent of City. More specifically, each member of ZIP is the parent of one or more members of City. That is, it is shorthand to say that one level is the parent of another level; the actual relationship is between members, not between levels. This shorthand is in common use, because it is convenient, even though it is not precise.

The following figure shows the relationships among the members of the HomeD.H1 hierarchy:

ZIP 32006 contains Juniper,Spruce cities, 32007 contains Redwood,34577 contains Cypress,Magnolia,Pine cities

The distinguishing feature of a hierarchy is that any given child element is unique to its parent. This example is artificial because in reality there is a many-to-many relationship between ZIP codes and cities.

Accessing the Members of a Hierarchy

To access the members of a hierarchy (that is, all the members of all its levels), you use the MEMBERS function. In this case, the syntax is as follows:

[dimension_name].[hierarchy_name].MEMBERS

In InterSystems MDX, if you omit the hierarchy name, the system assumes that you are referring to the first visible hierarchy in the given dimension.

For example, in the DemoMDX cube, the homed dimension has only one hierarchy. The following query shows the members of that hierarchy:

SELECT MEASURES.[%COUNT] ON 0, homed.MEMBERS ON 1 FROM demomdx
 
                                    %COUNT
 1 32006                                215
 2 Juniper                              122
 3 Spruce                                93
 4 32007                                111
 5 Redwood                              111
 6 34577                                347
 7 Cypress                              112
 8 Magnolia                             114
 9 Pine                                 121
10 36711                                 99
11 Centerville                           99
12 38928                                228
13 Cedar Falls                          110
14 Elm Heights                          118

When you use the MEMBERS function with a hierarchy, it returns the set of members in hierarchical order. The first member is the All member, if present. After that, each member is one of the following:

  • The first child of the previous member.

  • The next sibling of the previous member.

For another example, the following query shows all the measures (apart from %COUNT):

SELECT gend.gender.MEMBERS ON 0, MEASURES.MEMBERS ON 1 FROM demomdx

                              Female                 Male
1 Age                          18,413               17,491
2 Avg Age                       37.73                34.16
3 Allergy Count                   326                  332
4 Avg Allergy Count              1.08                 1.07
5 Test Score                   29,542               31,108
6 Avg Test Score                73.49                74.42

Using Parent-Child Relationships

The system provides the following MDX functions that directly use parent-child relationships:

  • CHILDREN returns the children, if any, of a given member. The returned value is a set of members, in the default order specified for the level. For example:

    SELECT MEASURES.[%COUNT] ON 0, homed.zip.[34577].CHILDREN ON 1 FROM demomdx
    
                                        %COUNT
    1 Cypress                               112
    2 Magnolia                              114
    3 Pine                                  121
    
    

    For another example:

    SELECT MEASURES.[%COUNT] ON 0, homed.pine.CHILDREN ON 1 FROM demomdx
    
                                        %COUNT
                                      No Result
    
    
  • PARENT returns the parent, if any, of a given member. For example:

    SELECT MEASURES.[%COUNT] ON 0, homed.city.[Elm Heights].PARENT ON 1 FROM demomdx
     
                                        %COUNT
    38928                                   228
    
    
  • FIRSTCHILD returns the first child, if any, of a given member. For example:

    SELECT MEASURES.[%COUNT] ON 0, homed.zip.[34577].FIRSTCHILD ON 1 FROM demomdx 
    
                                        %COUNT
    Cypress                                 112
    
    
  • LASTCHILD returns the last child, if any.

Accessing Siblings

The system provides the following MDX functions that access siblings of a member:

  • FIRSTSIBLING returns the first sibling, if any, of a given member. For example:

    SELECT MEASURES.[%COUNT] ON 0, birthd.[Q1 1920].FIRSTSIBLING ON 1 FROM demomdx
    
                                        %COUNT
    Q1 1920                                   *
    
    
  • LASTSIBLING returns the last sibling, if any.

  • SIBLINGS returns the given member and all its siblings. For example:

    SELECT MEASURES.[%COUNT] ON 0, homed.cypress.SIBLINGS ON 1 FROM demomdx
     
                                        %COUNT
    1 Cypress                               112
    2 Magnolia                              114
    3 Pine                                  121
    
    

Accessing Cousins

The COUSIN function enables you to access a cousin, given a member at a higher level.

For example, the following query finds the cousin of Q1 1943, within the year 1990:

SELECT MEASURES.[%COUNT] ON 0, COUSIN(birthd.[Q1 1943],birthd.1990) ON1 FROM demomdx
 
                                    %COUNT
Q1 1990                                   5

To determine relative positions, the system uses the default order of the members within the level, as determined by the cube definition.

Accessing Descendant Members

You can use the DESCENDANTS function to obtain descendents of a given member, within one or more lower levels. For example, the following query gets all the descendents of the year 1990, within the [BirthD].[H1].[Period] level:

SELECT DESCENDANTS(birthd.1990,birthd.period) ON 1 FROM demomdx
 
 1 Jan-1990                               *
 2 Feb-1990                               2
 3 Mar-1990                               1
 4 Apr-1990                               1
 5 May-1990                               1
 6 Jun-1990                               *
 7 Jul-1990                               2
 8 Aug-1990                               2
 9 Sep-1990                               1
10 Oct-1990                               3
11 Nov-1990                               1
12 Dec-1990                               *

The DESCENDANTS function provides many options for accessing descendents within different parts of the hierarchy, but the preceding usage is the most common scenario.

Accessing the Current Member within an Iteration

In a typical query, you iterate through a set of members, perhaps displaying each as a row. Sometimes you want to do something specific with each member in turn. To do so, you use the CURRENTMEMBER function, which accesses the member used in the current context.

For example, consider the following query:

SELECT MEASURES.[%COUNT] ON 0, homed.city.MEMBERS ON 1 FROM demomdx
 
                                    %COUNT
1 Cedar Falls                           110
2 Centerville                            99
3 Cypress                               112
4 Elm Heights                           118
5 Juniper                               122
6 Magnolia                              114
7 Pine                                  121
8 Redwood                               111
9 Spruce                                 93

This query has one row for each city. The data shown is the %COUNT measure. Suppose that instead we would like to show the city’s population, which we access via the PROPERTIES function. This function requires a reference to the member used in the row; for that, we use the CURRENTMEMBER function, which we can call as follows:

[dimension_name].[hierarchy_name].CURRENTMEMBER

With this function, we can create the following variation of our query:

SELECT homed.h1.CURRENTMEMBER.PROPERTIES("Population") ON 0, homed.city.MEMBERS ON 1 FROM demomdx
 
                                        H1
1 Cedar Falls                        90,000
2 Centerville                        49,000
3 Cypress                             3,000
4 Elm Heights                        33,194
5 Juniper                            10,333
6 Magnolia                            4,503
7 Pine                               15,060
8 Redwood                            29,192
9 Spruce                              5,900

For another example, the following query shows the internal keys for the members of Doctor:

SELECT docd.h1.CURRENTMEMBER.PROPERTIES("KEY") ON 0, docd.[doctor].MEMBERS ON 1 FROM demomdx
 
                                       KEY
 1 None                              <null>
 2 Ahmed, Thelma                         34
 3 Alton, Chad                           35
 4 Black, Ashley                          4
..

FeedbackOpens in a new tab