Using MDX with DeepSee
Working with Levels
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

This chapter provides more information on levels, as well as an overview of the key MDX functions for working with them. It discusses the following topics:

Overview of Levels
A level enables you to group the data, and a level has members.
A member selects a set of records from the cube. For the City level, the Juniper member selects the patients whose home city is Juniper. Conversely, a record in the cube belongs to one or more members.
Possible Member Overlap
The members of a level can overlap each other. That is, a given record can belong to more than one member; this occurs if the level is based on a list. For example, consider the Allergies level, which contains one member for each allergy. A patient can have multiple allergies and thus can belong to multiple members of this level.
Null Values and Null Members
A level can have a Null member; this member selects the records that have no value for the data used by this level. Typically the name of this member is None.
Hierarchies
Levels belong to hierarchies. For information, see the chapter Working with Dimensions and Hierarchies,” later in this book.
Accessing Single Members of a Level
You can select a single member by referring to it directly. The general syntax is as follows:
[dimension_name].[hierarchy_name].[level_name].[member name]
As noted previously, in DeepSee MDX, you can omit the hierarchy name. Similarly, you can omit the level name.
For example:
SELECT MEASURES.[%COUNT] ON 0, allerd.[ant bites] ON 1 FROM demomdx 

                                    %COUNT
ant bites                                47
Member Names
In a given level, member names are not required to be unique; that is, when the cube is built, no checking is performed to ensure that member names are unique in a given level. For example, the Doctor dimension can include multiple members with the same name.
Member Keys
In a well-defined cube, each member has a unique, case-sensitive key. To refer to a member by its key, use the following syntax:
[dimension_name].[hierarchy_name].[level_name].&[member_key]
In many cases, member_key is the same as the member name. For a generated Null member, the key is <null>.
For details on how DeepSee generates member keys, see the reference section Key Values in the DeepSee MDX Reference.
MDX provides a function (PROPERTIES), which you can use to access the key (or any other property) of a member; this function is discussed later in this chapter.
Accessing Multiple Members of a Level
You can access multiple members of a level in several different ways.
First, you can use the MEMBERS function. In this case, the syntax is as follows:
[dimension_name].[hierarchy_name].[level_name].MEMBERS
For example:
SELECT MEASURES.[%COUNT] ON 0, allerd.allergies.MEMBERS ON 1 FROM demomdx
 
                                    %COUNT
 1 No Data Available                    390
 2 additive/coloring agen                46
 3 animal dander                         34
 4 ant bites                             47
 5 bee stings                            36
 6 dairy products                        30
 7 dust mites                            35
 8 eggs                                  32
 9 fish                                  45
10 mold                                  51
11 nil known allergies                  140
12 peanuts                               58
13 pollen                                57
14 shellfish                             54
15 soy                                   36
16 tree nuts                             45
17 wheat                                 52
You can also specify a range that selects adjacent members of a level, as follows:
member1:membern
For example:
SELECT MEASURES.[%COUNT] ON 0, {birthd.1942:birthd.1947} ON 1 FROM demomdx
 
                                    %COUNT
1 1942                                    6
2 1943                                    7
3 1944                                    6
4 1945                                   11
5 1946                                   12
6 1947                                    9
In this case, you can omit the dimension, hierarchy, and level identifiers for the member that you use for the end of the range. For example:
SELECT MEASURES.[%COUNT] ON 0, {birthd.1942:1947} ON 1 FROM demomdx
You can select multiple, nonadjacent members. To do so, refer to them directly, and place them in a comma-separated list surrounded by curly braces:
SELECT MEASURES.[%COUNT] ON 0, {allerd.eggs,allerd.soy,allerd.mold} ON 1 FROM demomdx
 
                                    %COUNT
1 eggs                                   32
2 soy                                    36
3 mold                                   51
Order of Members in a Level
Within a cube definition, a level definition determines the members in that level, as well as their default order, which is as follows:
The MEMBERS function returns the members in their default order, as defined by the level. For example:
SELECT gend.gender.MEMBERS ON 0,homed.city.MEMBERS ON 1 FROM demomdx
 
                              Female                 Male
1 Cedar Falls                      58                   52
2 Centerville                      41                   58
3 Cypress                          51                   61
4 Elm Heights                      53                   65
5 Juniper                          58                   64
6 Magnolia                         58                   56
7 Pine                             64                   57
8 Redwood                          58                   53
9 Spruce                           47                   46
If you have a subset of the members of a level and want to return them to the default order, use the HIERARCHIZE function as in the following example:
SELECT MEASURES.[%COUNT] ON 0, HIERARCHIZE({allerd.eggs,allerd.soy,allerd.mold}) ON 1 FROM demomdx
 
                                    %COUNT
1 eggs                                   32
2 soy                                    36
3 mold                                   51
For a more thorough introduction to this function, see the chapter Working with Sets.”
Selecting a Level Member by Relative Position
The following MDX functions enable you to select specific members of a level, relative to a given member. These functions all use the default order of members in the level. Note that the details are different for time dimensions and data dimensions (as defined in the cube definition):
For time dimensions, each of these functions ignores any parent level. For example, the PREVMEMBER function can return a member that has a different parent. For data dimensions, however, each of these functions does consider the parent level. For example, the PREVMEMBER function considers only the previous member within the given parent member. (Note that the terms time dimension and data dimension refer specifically to the dimension type as defined in the cube. See Defining DeepSee Models.) For examples that show these differences, see the DeepSee MDX Reference.
Introduction to Time Levels
A time level groups records by time; that is, any given member consists of the records associated with a specific date and time. For example, a level called Transaction Date would group transactions by the date on which they occurred. There are two general kinds of time levels, and it is important to understand their differences:
The following figure compares these kinds of time levels:
You can use these kinds of levels together without concern; MDX will always return the correct set of records for any combination of members.
However, it is worth noting that some MDX functions are useful for timeline-based levels but not for date-part-based levels. These functions include PREVMEMBER, NEXTMEMBER, and so on.
For example, consider the following query, which refers to a date-part based level. When we use PREVMEMBER with Q2, the engine returns the data for Q1, as expected.
SELECT [BirthQD].[Q2].PREVMEMBER ON 1 FROM patients
 
 
Q1                                      219
However, when we use PREVMEMBER with Q1, which is at the start of the set, the engine returns nothing.
SELECT [BirthQD].[Q1].PREVMEMBER ON 1 FROM patients
 
 
                                          *
This result is correct, because the Q1 member refers to records related to quarter 1 in all years, and it is not meaningful to access records “earlier” than that.
In contrast, consider the following query, which refers to a timeline-based level:
SELECT [BirthD].[Q1 2011].PREVMEMBER ON 1 FROM patients
 
 
Q4 2010                                   4
In this case, the member refers to records in a specific part of the timeline, and it is meaningful to refer to earlier records.
Special Features for Use with Time Levels
DeepSee MDX includes extensions for use with time levels. These include the NOW member and the %TIMERANGE function.
Selecting a Member Relative to Today (Time Levels)
For date/time levels, DeepSee supports a special member called NOW, which uses the current date (runtime) and accesses the appropriate member of the level.
For example, the following query accesses the current year in the Year dimension:
SELECT birthd.year.NOW ON 1 FROM demomdx
 
2011                                      9
For another example:
SELECT birthd.[quarter year].NOW ON 1 FROM demomdx

                                          
Q2 2011                                   5
DeepSee also supports variations that indicate members that are offset from NOW. For example, [NOW-1] finds the member that precedes NOW by one position:
SELECT birthd.[quarter year].[NOW-1] ON 1 FROM demomdx

                                          
Q1 2011                                   1
You can use these variations within ranges of members like the following:
SELECT birthd.[quarter year].[now-1]:birthd.[quarter year].now ON 1 FROM demomdx

                                          
1 Q1 2011                                 1
2 Q2 2011                                 5
For more details, see NOW Member for Date/Time Levels in the DeepSee MDX Reference.
Selecting Ranges of Members of a Time Level
DeepSee provides an extension to MDX that enables you to define a range of members, for a time level. This extension is the %TIMERANGE function, which takes three arguments: a starting member, an ending member, and a keyword (either the default INCLUSIVE or EXCLUSIVE). You can om.it either but not both ends of the range
The following example uses both ends of the range:
SELECT NON EMPTY DateOfSale.YearSold.MEMBERS ON 1 FROM holefoods 
WHERE %TIMERANGE(DateOfSale.YearSold.&[2009],DateOfSale.YearSold.&[2011])
 
me 
1 2009                                  179
2 2010                                  203
3 2011                                  224
The next example shows another open-ended range, this time using the EXCLUSIVE keyword:
SELECT NON EMPTY DateOfSale.YearSold.MEMBERS ON 1 FROM holefoods 
WHERE %TIMERANGE(,DateOfSale.YearSold.&[2009],EXCLUSIVE)
 
 
1 2007                                  124
2 2008                                  156
Accessing Properties
In MDX, a level can have properties that are specific to the level. Each member of the level can have a different value for the property. You can access these properties and display them in your query results. There are two kinds of properties:
Names of properties are not case-sensitive.
To access the property of a member, use the PROPERTIES function. For example:
SELECT homed.city.magnolia.PROPERTIES("Principal Export") ON 0 FROM demomdx
                                           
                                 bundt cake
For another example:
SELECT homed.cypress.LEAD(1).PROPERTIES("name") ON 0 FROM demomdx

                                      name
                                   Magnolia
Properties As String Expressions
MDX treats property values as strings. MDX also supports string literals (for example, "my label") and a concatenation operator (+). Therefore, you can create expressions like the following:
"Next after Cypress: " + homed.cypress.LEAD(1).PROPERTIES("name")
And you can use such expressions in MDX queries. For example:
SELECT "Next after Cypress: " + homed.cypress.LEAD(1).PROPERTIES("name") ON 0 FROM demomdx
 
                                Expression
                         Next after Cypress: Magnolia
Properties and Attributes
Properties are not the same as attributes, which are often mentioned when MDX is discussed.
In some implementations of MDX, attributes are used to define a cube. No MDX functions, however, directly use attributes.
DeepSee does not use attributes.