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:
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 121For 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
..
