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