Skip to main content

Working with Levels

This page provides more information on levels in Business Intelligence, as well as an overview of the key MDX functions for working with them.

Also see Accessing the BI Samples.

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 Working with Dimensions and Hierarchies.

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 InterSystems 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 the system generates member keys, see the reference section Key Values in the InterSystems 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 page.

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:

  • For non-date levels, members are sorted in increasing order alphabetically by name, unless the cube specifies a different sort order.

  • For date levels, members are sorted chronologically, in ascending order or descending order, depending on the definitions in the cube.

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 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):

  • NEXTMEMBER returns the next member from a given level. For example:

    SELECT MEASURES.[%COUNT] ON 0, birthd.[Q1 1920].NEXTMEMBER ON 1 FROM demomdx
    
                                        %COUNT
    Q2 1920                                   *
    
    
  • PREVMEMBER returns the previous member.

  • LEAD counts forward in the level and returns a later member. For example:

    SELECT MEASURES.[%COUNT] ON 0, birthd.[Q1 1920].LEAD(3) ON 1 FROM demomdx
    
                                        %COUNT
    Q4 1920                                   1
    
    
  • LAG counts backward in the level and returns an earlier member.

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 Models for InterSystems Business Intelligence.) For examples that show these differences, see the InterSystems 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:

  • Timeline-based time levels. This kind of time level divides the timeline into adjacent blocks of time. Any given member of this level consists of a single block of time. Or, more accurately, the member consists of the records associated with that block of time. For a level called Transaction Quarter Year, the member Q1-2011 would group all the transactions that occurred in any of the dates that belong to the first quarter of 2011.

    This kind of level can have any number of members, depending on the source data.

  • Date-part-based time levels. This kind of time level considers only part of the date value and ignores the timeline. Any given member consists of multiple blocks of time from different parts of the timeline, as shown in the following figure. Or, more accurately, the member consists of the records associated with those blocks of time. For a level called Transaction Quarter, the member Q1 would group all the transactions that occurred in any of the dates that belong to the first quarter of any year.

    This kind of level has a fixed number of members.

The following figure compares these kinds of time levels:

generated description: 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

InterSystems 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, the system 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

Business Intelligence 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 InterSystems MDX Reference.

Selecting Ranges of Members of a Time Level

The system 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:

  • User-defined properties. In Business Intelligence, these are defined within the cube definition. For example, in the DemoMDX cube, the City level has two properties called Population (population of the city) and Principal Export (the principal export of the city).

  • Intrinsic properties, which contain information such as the member name and the member’s key. For a list, see the reference section Intrinsic Properties in the InterSystems MDX Reference.

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.

The system does not use attributes.

FeedbackOpens in a new tab