Working with Time in Microsoft SQL Server 2008 MDX

2/25/2009

Contents

Calculating an Accumulating Total

In business, metrics are quite frequently reported as accumulating totals. For example, consider reseller sales in the month of October. Although sales in this month alone are interesting and important, the accumulation of sales over the months of the year up to and including October may be more interesting, especially if you are tracking sales against an annual target.

To calculate accumulating totals, you must determine the set of time members over which a value is to be aggregated. This is done using the PeriodsToDate function:

PeriodsToDate( [Level , [Member]] )

The PeriodsToDate function returns the set of members from the start of a given period up to and including a specified member. The Level argument identifies the level of the hierarchy representing the period over which the returned set should span, whereas the Member argument identifies the set’s ending member. You can think of Analysis Services as starting with the specified member, navigating up to its ancestor in the specified level and then back down to the first sibling of the specified member under this shared ancestor. The set returned represents the range of members between and including these two members.

If the Member argument is not specified but the Level argument is, Analysis Services infers the current member of the hierarchy for the Member argument. If neither the Member nor the Level argument is specified, Analysis Services infers the current member of a hierarchy in a time dimension for the Member argument and the parent level of this member for the Level argument. For most applications of the PeriodsToDate function, you are encouraged to supply both arguments to ensure clarity.

Calculate year-to-date reseller sales

Open the MDX Query Editor to the MDX Step-by-Step database.

In the code pane, enter the following query to retrieve reseller sales for the periods to date for the month of April 2002:

SELECT
    {([Measures].[Reseller Sales Amount])} ON COLUMNS,
    {
        PeriodsToDate(
            [Date].[Calendar].[Calendar Year],
            [Date].[Calendar].[Month].[April 2002]
            )
        } ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

In the preceding query, you use the PeriodsToDate function to retrieve all months in the year 2002 prior to and including the month of April. By specifying the Calendar Year level of the Calendar hierarchy, Analysis Services moves from the member April 2002 to its ancestor along this level, CY 2002. It then selects the CY 2002 member’s first descendant within the Month level—the level occupied by the specified member April 2002. This first descendant, January 2002, and the specified member, April 2002, then are used to form a range, [Date].[Calendar].[Month].[January 2002]:[Date].[Calendar].[Month].[April 2002], which resolves to the set presented along the ROWS axis.

This query demonstrates the basic functionality of the PeriodsToDate function, but your goal is to calculate a year-to-date total for reseller sales. Instead of using PeriodsToDate to define a set along an axis, you can use the function to define the set over which you aggregate values in a calculated member. As a starting point towards this goal, re-factor the query to return all months along the ROWS axis.

Modify the query to retrieve reseller sales for each month:

SELECT
    {([Measures].[Reseller Sales Amount])} ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

Modify the query to calculate the year-to-date cumulative reseller sales for each member along the ROWS axis:

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[Calendar Year],
            [Date].[Calendar].CurrentMember
             ),
        ([Measures].[Reseller Sales Amount])
        )
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Year to Date Reseller Sales])
        } ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

For each member along the ROWS axis, the PeriodsToDate function returns the set of members from the start of its calendar year up to and including this member. Over this set, the current measure, Reseller Sales Amount, is aggregated to calculate year-to-date sales. Comparing the year-to-date totals to the monthly sales values for previous months, you can verify this logic.

NOTE

The preceding calculation employs the Aggregate function to calculate a running total. For more information on this and the other MDX aggregation functions, see Chapter 7, “Performing Aggregation.”

As you review these results, notice between December 2001 and January 2002 the value of the accumulating total “resets.” This is because these two members have differing ancestor members within the Calendar Year level. This pattern of accumulation and reset is observed whenever transitions between ancestors occur, as demonstrated in the following calculations of quarter-to-date totals.

Add a quarter-to-date total for reseller sales to the query:

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[Calendar Year],
            [Date].[Calendar].CurrentMember
            ),
        ([Measures].[Reseller Sales Amount])
        )
MEMBER [Measures].[Quarter to Date Reseller Sales] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[Calendar Quarter],
            [Date].[Calendar].CurrentMember
            ),
        ([Measures].[Reseller Sales Amount])
        )
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Year to Date Reseller Sales]),
        ([Measures].[Quarter to Date Reseller Sales])
        } ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

Execute the query and review the new Quarter To Date Reseller Sales values.

Reviewing the results, you can see the same pattern of accumulation and reset with the Quarter To Date Reseller Sales calculated measure as you do with the Year To Date Reseller Sales calculated measure. The only difference is that the pattern is based on a quarterly cycle as opposed to an annual one.

Simplifying Periods-to-Date Calculations

Many of the attributes in a time dimension are assigned Type property values at design time, identifying the attributes as representing years, quarters, months, or weeks. Analysis Services can return period-to-date sets based on these type assignments without the identification of a level by name. This functionality is provided through the specialized Ytd, Qtd, Mtd, and Wtd functions returning year-to-date, quarter-to-date, month-to-date, and week-to-date sets, respectively:

Ytd( [Member] )
Qtd( [Member] )
Mtd( [Member] )
Wtd( [Member] )

These functions, collectively referred to as the xTD functions, are logically equivalent to the PeriodsToDate function with hard-coded level arguments. Their reliance on the proper assignment of Type property values at design time makes them more succinct but also makes them dependent on settings into which you may have little insight. If you use the xTD functions, it is important for you to verify the set returned.

To demonstrate the use of the xTD functions, the last query of the previous exercise is rewritten using Ytd and Qtd to derive the year-to-date and quarter-to-date sets, respectively:

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
    Aggregate(
        Ytd([Date].[Calendar].CurrentMember),
        ([Measures].[Reseller Sales Amount])
        )
MEMBER [Measures].[Quarter to Date Reseller Sales] AS
    Aggregate(
        Qtd([Date].[Calendar].CurrentMember),
        ([Measures].[Reseller Sales Amount])
        )
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Year to Date Reseller Sales]),
        ([Measures].[Quarter to Date Reseller Sales])
        } ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

Calculating Inception-to-Date

The period-to-date calculations return a value based on a range that is restricted to a particular period, such as a quarter or year. Occasionally, you may wish to calculate an accumulating value across all periods for which data is recorded. This is referred to as an inception-to-date value.

You can retrieve the inception-to-date range using the PeriodsToDate function with the calendar’s (All) member’s level as the period identifier, as demonstrated in the following expression:

PeriodsToDate(
    [Date].[Calendar].[(All)],
    [Date].[Calendar].CurrentMember
    )

Although this expression is perfectly valid, many MDX developers typically calculate inception-to-date sets employing a range-based shortcut:

Null: [Date].[Calendar].CurrentMember

The Null member reference forces Analysis Services to evaluate the range from a position just prior to the first member of the level on which the current time member resides. The result is the same set returned by the previous expression that employed the PeriodsToDate function.

Whichever technique you employ, measures are aggregated over the set just as with other period-to-date calculations, as demonstrated in the following example:

WITH
MEMBER [Measures].[Inception to Date Reseller Sales - PTD] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[(All)],
            [Date].[Calendar].CurrentMember
            ),
         ([Measures].[Reseller Sales Amount])
         )
MEMBER [Measures].[Inception to Date Reseller Sales - Range] AS
    Aggregate(
        NULL:[Date].[Calendar].CurrentMember,
        ([Measures].[Reseller Sales Amount])
        )
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Inception to Date Reseller Sales - PTD]),
        ([Measures].[Inception to Date Reseller Sales - Range])
        } ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

Calculating Rolling Averages

Analysts often look for changes in values over time. Natural variability in most data can make it difficult to identify meaningful changes. Rolling averages are frequently employed to smooth out some of this variation, allowing more significant or longer-term changes to be more readily identified.

A rolling average is calculated as the average of values for some number of periods before or after (and including) the period of interest. For example, the three-month rolling average of sales for the month of February might be determined as the average of sales for February, January, and December. A three-month rolling average calculated in this manner is common in business analysis.

The heart of the rolling average calculation is the determination of the set of periods over which values will be averaged. To support the retrieval of this set, the MDX function LastPeriods is provided:

LastPeriods( n [, Member] )

The LastPeriods function returns a set of n members before or after (and including) a specified member of a time hierarchy. If a positive n value is provided, the set returned includes the members preceding the member of interest. If a negative n value is provided, the set returned includes the members following the member of interest.

The function’s second argument is optional. If the second argument is not supplied, Analysis Services assumes the current member of a hierarchy in a time dimension. For most applications of the LastPeriods function, you are encouraged to employ the Member argument to ensure clarity.

Calculate the three-month rolling average for reseller sales

Open the MDX Query Editor to the MDX Step-by-Step database.

In the code pane, enter the following query to retrieve reseller sales for the three periods preceding and including January 2002:

SELECT
    {([Measures].[Reseller Sales Amount])} ON COLUMNS,
    {
        LastPeriods(
            3,
            [Date].[Calendar].[Month].[January 2002]
            )
        } ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

In this query, you use the LastPeriods function to retrieve the three-month period preceding and including January 2002. Analysis Services starts with the specified member, January 2002, and treats this as period 1. This leaves n-1 or 2 members to return in the set. Because n is a positive number, Analysis Services retrieves the January 2002 member’s two preceding siblings to complete the set. (Notice that the November and December 2001 siblings were selected without regard for the change in the Calendar Year ancestor between them and the January 2002 member.)

This query demonstrates the basic functionality of the LastPeriods function, but your goal is to calculate a rolling average for reseller sales. Instead of using LastPeriods to define a set along an axis, you can use the function to define the set over which you will average values in a calculated member. As a starting point towards this goal, re-factor the query to return all months along the ROWS axis.

Alter the query to retrieve reseller sales for various months:

SELECT
    {([Measures].[Reseller Sales Amount])} ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

Reseller sales vary considerably between various months. For example, take a look at the six-month period between October 2001 and March 2002. The wild swings between monthly sales make it difficult to determine any general upward or downward trends during this period. The same is true of the months between June 2002 and December 2002.

Alter the query to calculate a three-month rolling average for reseller sales:

WITH
MEMBER [Measures].[Three Month Avg Reseller Sales Amount] AS
    Avg(
        LastPeriods(
            3,
            [Date].[Calendar].CurrentMember
            ),
        ([Measures].[Reseller Sales Amount])
        )
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Three Month Avg Reseller Sales Amount])
        } ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

Execute the query and compare the monthly reseller sales values to the three-month rolling average values.

The three-month rolling average smoothes out some of the variability in the data, making general trends more easily observed. The period from October 2001 to March 2002 that reflected so much variability based on monthly sales totals now appears to be trending only slightly upward. The period from June 2002 and December 2002 that also displayed considerable variability appears to be trending more significantly upward. Without the smoothing effect of the rolling average, these trends would be harder to observe and differentiate.

Save to your account