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.
