Use the Time dimension and MDX functions to slice your data by time periods

Of the several misconceptions surrounding the Time dimension, one of the biggest is that an OLAP cube must have a Time dimension. The truth is that some OLAP applications, such as activity-based costing, don't have a Time dimension at all. Activity-based costing usually doesn't need a Time dimension because the costing is based on activity, not time. The second most common misconception is that an OLAP cube can have only one Time dimension. Some insurance applications have several Time dimensions, including Underwriting Date and Accident Date. Cash-flow applications also usually have several time dimensions.

A basic rule of the OLAP model is that all dimensions must be equivalent in structure and operational capabilities. However, most OLAP tools, including SQL Server 2000 Analysis Services and SQL Server 7.0 OLAP Services, allow one exception: You can declare more than one dimension as a Time dimension, which has attributes that other dimensions don't. After you've designated a dimension as a Time dimension, Analysis Services and OLAP Services can automatically create levels such as Year, Quarter, and Month and can also generate member names. In addition, you can use many time-aware MDX functions, such as Ytd(), to analyze your data according to time periods. Let's look at how to create and work with Time dimensions in Analysis Services and OLAP Services.

Creating a Time Dimension

Some people create a view from the fact table, such as

SELECT \[Fact_Table\].\[Date\]
FROM \[Fact_Table\]
GROUP BY \[Fact_Table\].\[Date\]

and use the view as a source for the Time dimension. This method has a couple of drawbacks. First, it's inefficient: The fact table is usually much bigger than the dimension table, and accessing a view of the fact table is the same as accessing the underlying base table. Another disadvantage of using the fact table as a source for the Time dimension is that the dimension won't contain a date that had no activity. Thus, this method can create gaps in the dimension sequence by skipping weekends, holidays, and so on. If you want these gaps, remember to exclude irrelevant dates from your Time dimension table.

A better way to create a Time dimension is to create a special Time dimension table in your data warehouse to hold all relevant dates. Simply create the table in Microsoft Excel, then use Data Transformation Services (DTS) to import the table into the data warehouse. This approach to creating a Time dimension significantly improves dimension and cube processing because you don't need to query the fact table to get the Time dimension members. And if the table's date field is of any time data type (e.g., smalldatetime), Analysis Services' and OLAP Services' Dimension Wizard, which you use to create dimensions, detects that the dimension could be a Time dimension and prompts you to confirm its choice, as Figure 1 shows. After you confirm that the dimension is a Time dimension, the Dimension Wizard helps you create the Time dimension's levels (e.g., Year, Quarter, Month, Day), as Figure 2 shows. You can also define the first day and month of the year; the default is January 1.

The Dimension Wizard also creates member names for you. Although these generated names are accurate, they don't always include the member's specific time period. For example, if you select 2 years of data at the Month level, you'll have two members named January, two members named February, and so on—with no way to distinguish which month belongs to which year. The solution is to customize your Time dimension member names. The sidebar "Customize Time Dimension Member Names," below, shows you how to give your members meaningful names, such as Q1/1999 for the first quarter of 1999, 1/1999 for January 1999, and 5/1/1999 (European notation) or 1/5/1999 (US notation) for January 5, 1999. You should also ensure that for all levels, the ORDER BY property is set to Key; otherwise, Analysis Services and OLAP Services sort the members by name instead of by date. To ensure that ORDER BY is set to Key, open the Dimension Editor by selecting and right-clicking the Time dimension in the tree pane and selecting Edit. Then for each level, select the Advanced tab in the Properties pane, and examine the ORDER BY property.

Even if you skipped these wizard options because you marked the dimension as Standard instead of Time or because the date field's data type isn't a date type, you can always convert a Standard dimension to a Time dimension. From the Dimension Editor, select the root of the tree pane, and in the Properties pane, select the Advanced tab, then change the dimension type from Standard to Time. Next, for each level, change the level type from Standard to the relevant type (e.g., Month).

To make reporting easier, you can add properties such as Month and Day In Week to the Time dimension members' leaf level. For example, if your leaf level is Day, you can easily add a property such as Day In Week, which lets you analyze your data by the day of the week. Figure 3 shows how to define properties such as Month and Day In Week. From the Dimension Editor, select Insert, Member Property, then the date field; enter the property's name in the Name field; and adjust the source column and data type as necessary. Table 1 shows the source column and data type information for the Month and Day In Week properties.

If you use the Month property, you can create a virtual dimension called Month based on this property. A virtual dimension is a logical dimension that you base on a member property, on columns in the physical dimension's tables, or on a column from a physical dimension. Adding a virtual dimension to a cube doesn't increase the cube's size because a virtual dimension, unlike a physical dimension, doesn't have aggregation data. Virtual dimensions also don't affect cube processing time because Analysis Services and OLAP Services calculate them in memory when needed. However, queries that use virtual dimensions might run slower than queries that use physical dimensions. After you create the virtual dimension Month, you can chart Months versus Years, as the screen in Figure 4 shows. (I used the Knosys ProClarity client tool to create this chart, but almost any client tool would work.) This chart shows the Month virtual dimension on the horizontal axis and the Time dimension's Year level charted against the month. So although the Time dimension is a single dimension, we can chart two levels of the dimension—one versus the other—by using a virtual Time dimension.

Time-Aware MDX Functions

Some MDX functions, such as the Ytd() function, are time-aware, meaning they understand time logic and the various levels of a Time dimension. The Ytd() function returns a set of periods (members) from a specified member level in a Time dimension that has the same ancestor in the Year level, starting with the first period and ending with a specified member. If the specified member is 5/1999, the ancestor is the year 1999 and the Ytd() function would return all the months (the specified member level) starting with the first period (1/1999) and ending with the specified member (5/1999):


The Ytd() function has a single, optional parameter—Member—which defaults to the current member of the Time dimension you're interested in. If you have only one Time dimension in your cube, you can omit the Member parameter; however, if you have more than one Time dimension, you must specify this parameter to tell Analysis Services or OLAP Services which dimension contains the set of members you want.

The Ytd() function is a shortcut to another function, PeriodsToDate(), which returns a set of periods (members) from a specified level, starting with the first period and ending with a specified member. Other shortcuts to PeriodsToDate() are Qtd(), Mtd(), and Wtd(). These functions are all set functions, because they return a set of periods. Another set function is LastPeriods(), which returns a set of members before and including a specified member. The syntax for the LastPeriods() function is

LastPeriods(Index \[, Member\])

As with the Ytd() function, if you don't specify Member, the LastPeriods() function defaults to Time.CurrentMember. If Index is positive, the LastPeriods() function returns the set of index members ending with the Member parameter you specified and starting with the member that is the Member parameter's Index value minus 1. In other words, if Time.CurrentMember is 5/1999 and Index is 3, you receive a set of three periods, starting 2 (that is, 3 minus 1) periods before the current one and ending with the current one:


If your current member is Q2/1999, the LastPeriods(3) function returns the set


Note that Index can be negative, in which case the returned set starts with Member and the set's size is the absolute value of Index. If Index is 0, LastPeriods() returns an empty set.

You usually use set functions in conjunction with other MDX functions, such as Avg() and Sum(). For example, to calculate year-to-date total sales, you would use the Ytd() and Sum() functions in the formula

Sum(\{Ytd()\} , (\[Measures\].\[Sales\]))

To calculate year-to-date average stock, you would use the Ytd() and Avg() functions:

Avg(\{Ytd()\} , (\[Measures\].\[Stock\]))

For more information about where to use these types of calculations, see the sidebar "The Right Place for Calculated Members."

Unlike the set functions, which return sets of members as the result, other time-aware MDX functions return just one member. One such MDX function is ParallelPeriod(), which returns a member from a prior period in the same relative position as a specified member. ParallelPeriod's syntax is

ParallelPeriod(\[Level\[, Numeric_Expression\[, Member\]\]\])

In Microsoft terms, the Level parameter takes the ancestor of Member, and the Numeric_Expression parameter is the sibling of this ancestor minus 1; the function returns the parallel period of Member among the descendants of that sibling. For example, ParallelPeriod(Year,1,\[05/2000\]) returns \[05/1999\]: The ancestor is \[2000\], minus 1 is \[1999\], and because \[5/2000\] is the fifth child of \[2000\], the function returns the fifth child of \[1999\], which is \[5/1999\].

As with the other time-aware functions, the Member parameter's default value is the current member of the Time dimension. However, if you specify Level, the default value is the current member in the dimension to which Level belongs. The default value of Numeric_Expression is 1, and the default value of Level is the level of the parent of Member. If your current time member's level is Month, to get the parallel month of the previous year, you could use


If you want to calculate the growth in sales this year versus last year, you could use the calculation

(\[Measures\].\[Sales\],\[Time\].CurrentMember) -
   (\[Measures\].\[Sales\], ParallelPeriod (\[Time\].\[Year\])

Two other functions that return one Time dimension member are ClosingPeriod(), which returns the last sibling among the descendants of a member at a specified level, and OpeningPeriod(), which returns the first sibling among the descendants of a specified level (optionally for a specified member).

You can also create a set as a range of periods; just specify two periods from the same level as endpoints with a colon between them, like a range in Excel. For example, the range \{\[2/1999\]:\[5/1999\]\} includes 2/1999, 3/1999, 4/1999, and 5/1999. Companies commonly need to calculate a rolling year, the total of the last 12 months (or last 4 quarters). To calculate a rolling year, people often use the formula

Sum(LastPeriods(12), \[Measures\].\[Sales\])

Although this formula works fine for a Month level, the LastPeriods(12) calculation doesn't work at any other level. You'd have to create a separate formula to calculate the rolling year based on Quarter, Day, or even Year. Based on what you've learned about MDX time functions, you can define this more generic calculation:

Sum(\{ParallelPeriod(\[Time\].\[Year\],1).NextMember :

This calculation is independent of the number of level members in a year, and it returns a correct range at each level.

Several other MDX functions aren't time-specific but are frequently used in conjunction with a Time dimension. These functions include Head(), which returns the first specified number of elements in a set; Tail(), which returns a subset from the end of a set; Lag(), which returns the member that is a specified number of positions before a specified member along the dimension of the member; and Lead(), which returns the member that is a specified number of positions after a specified member along the dimension of the member.

If your Time dimension includes only periods for which you have data and the last period in your dimension is the last period for which you have data, the following MDX query always returns the past 3 months of data in the columns axis:

SELECT \{Tail(\[Time\].\[Month\].Members,3)\} ON Columns ...

And the following query slices the data for the most recent quarter:

WHERE (Tail(\[Time\].\[Quarter\].Members,1).Item(0))

Wealth of Functions

Analysis Services and OLAP Services let you declare any number of dimensions as Time dimensions and let you use a wealth of time-aware functions to work with these dimensions. These capabilities make reporting and analysis easier for both the developer and the end user.