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.
