Using MDX with DeepSee
Working with Dimensions and Hierarchies
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter discusses hierarchies and dimensions. These elements are containers for levels but also have their own purposes. This chapter discusses the following topics:

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 DeepSee 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:
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 DeepSee 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:
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
DeepSee provides the following MDX functions that directly use parent-child relationships:
Accessing Siblings
DeepSee provides the following MDX functions that access siblings of a member:
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, DeepSee 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
..