Working with Time in Microsoft SQL Server 2008 MDX

2/25/2009

Contents

Performing Period-over-Period Analysis

Historical values are frequently used in data analysis to provide perspective on current values. When comparing historical to current values, it is important you select values from time periods relatively similar to one another. Although no two time periods are exactly alike, analysts often compare values from what are referred to as parallel periods to minimize differences resulting from cyclical, time-dependent variations in the data.

To understand parallel periods, consider the month of April 2003. This month is the fourth month of the calendar year 2003. In a business heavily influenced by annual cycles, you might compare values for this month to those for the month of April in a prior year. In doing so, you might accurately (or inaccurately) assume that differences in current and historical values are due to factors other than the annual cyclical influence.

Should you compare values for April 2003 to those of January 2003 or October 2002? Your first response may be to say no. However, if your business is heavily influenced by quarterly cycles, this might be completely appropriate. April 2003 is the first month of a calendar quarter. January 2003 is the first month of the prior quarter and is therefore a parallel member based on quarter. October 2002 is also a parallel member except that it is from two quarters prior. What constitutes an appropriate parallel period for your analysis is highly dependent upon the time-based cycles influencing your business.

To assist you with the retrieval of parallel period members, Analysis Services provides the ParallelPeriod function:

ParallelPeriod( [Level [,n [, Member]]] )

The function’s first argument identifies the level of the time hierarchy across which you wish to identify the parallel period member. If no level is identified, the parent level of the current time member is assumed.

The function’s second argument identifies how far back along the identified level you wish to go to retrieve the parallel member. If no value is provided, a value of 1 is assumed, indicating the prior period.

The function’s final argument identifies the member for which the parallel period is to be determined. The position of this member relative to its ancestor in the specified level determines the member retrieved from the historical period. If no member is identified, the current time member is assumed.

Calculate growth over prior period

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 months of calendar year 2003:

SELECT
    {([Measures].[Reseller Sales Amount])} ON COLUMNS,
    {
        Descendants(
            [Date].[Calendar].[Calendar Year].[CY 2003],
            [Date].[Calendar].[Month],
            SELF
            )
         } ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

The query returns reseller sales for the months of calendar year 2003. To assess the strength of these numbers in a business influenced by annual sales cycles, you might compare them to sales in the prior year. To do this, start by identifying the prior period for each month.

Alter the query to identify the parallel period in the prior year for each month:

WITH
MEMBER [Measures].[x] AS
    ParallelPeriod(
        [Date].[Calendar].[Calendar Year],
        1,
        [Date].[Calendar].CurrentMember
        ).Name
SELECT
     {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[x])
        } ON COLUMNS,
     {
        Descendants(
            [Date].[Calendar].[Calendar Year].[CY 2003],
            [Date].[Calendar].[Month],
            SELF
            )
         } ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

In the preceding query, the ParallelPeriod function is used to identify the parallel period in the prior year for each month in calendar year 2003 along the ROWS axis. The ParallelPeriod function returns a member and the name of that member is returned with a new calculated member to verify that the appropriate member is being identified. Now that you are comfortable the correct member is being located, you can use the returned member to determine prior period sales.

Alter the query to calculate prior period sales:

WITH
MEMBER [Measures].[Prior Period Reseller Sales Amount ] AS
    (
        ParallelPeriod(
            [Date].[Calendar].[Calendar Year],
            1,
            [Date].[Calendar].CurrentMember
            ) ,
        [Measures].[Reseller Sales Amount]
        )
    ,FORMAT="Currency"
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Prior Period Reseller Sales Amount ])
        } ON COLUMNS,
     {
        Descendants(
            [Date].[Calendar].[Calendar Year].[CY 2003],
            [Date].[Calendar].[Month],
            SELF
            )
         } ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

Using the member returned by the ParallelPeriod function to assemble a tuple allows you to retrieve reseller sales for the prior period. This newly calculated measure is returned along the COLUMNS axis for comparison against sales in the months displayed across the rows. To facilitate comparison, you might wish to present the percent change in sales from the prior period.

Alter the query to calculate the percent change in sales (growth) between the current and prior periods:

WITH
MEMBER [Measures].[Prior Period Reseller Sales Amount] AS
    (
        ParallelPeriod(
            [Date].[Calendar].[Calendar Year],
            1,
            [Date].[Calendar].CurrentMember
            ) ,
        [Measures].[Reseller Sales Amount]
        )
    ,FORMAT="Currency"
MEMBER [Measures].[Prior Period Growth] AS
    (
        ([Measures].[Reseller Sales Amount])-
            ([Measures].[Prior Period Reseller Sales Amount])
        ) /
        ([Measures].[Prior Period Reseller Sales Amount])
    ,FORMAT="Percent"
SELECT
    {
        ([Measures].[Reseller Sales Amount]) ,
        ([Measures].[Prior Period Reseller Sales Amount]),
        ([Measures].[Prior Period Growth])
        } ON COLUMNS,
     {
        Descendants(
            [Date].[Calendar].[Calendar Year].[CY 2003],
            [Date].[Calendar].[Month],
            SELF
            )
         } ON ROWS
FROM [Step-by-Step]

Execute the query and review the results.

The results show each month of calendar year 2003 experienced considerable growth in reseller sales from those of the month in the prior year.

A Word of Caution

As explained at the start of this chapter, the time-based MDX functions are not time-aware and simply employ basic navigation for their functionality. This is illustrated by rewriting the query in Step 4 of the previous exercise with the navigation functions Cousin, Ancestor, and Lag:

WITH
MEMBER [Measures].[x] AS
    Cousin(
        [Date].[Calendar].CurrentMember,
        Ancestor(
            [Date].[Calendar].CurrentMember,
            [Date].[Calendar].[Calendar Year]
            ).Lag(1)
        ).Name
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[x])
        } ON COLUMNS,
     {
        Descendants(
            [Date].[Calendar].[Calendar Year].[CY 2003],
            [Date].[Calendar].[Month],
            SELF
            )
         } ON ROWS
FROM [Step-by-Step]

As previously mentioned, the use of basic navigation to provide time-based functionality imposes some constraints on your time dimension. One of these is that all members of a time period should be provided in the cube. Again, the query in Step 4 from the previous exercise provides a very clear demonstration of why this is important. Here is that query adjusted to present the months of calendar year 2002 along the ROWS axis:

WITH
MEMBER [Measures].[x] AS
    ParallelPeriod(
        [Date].[Calendar].[Calendar Year],
        1,
        [Date].[Calendar].CurrentMember
        ).Name
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[x])
        } ON COLUMNS,
     {
        Descendants(
            [Date].[Calendar].[Calendar Year].[CY 2002],
            [Date].[Calendar].[Month],
            SELF
            )
         } ON ROWS
FROM [Step-by-Step]

Notice in the results of this query that the month of January 2002 has a parallel period of July 2001. January 2002 is the first month-level descendant of calendar year 2002. Its parallel period in the prior year is the first month-level descendant of calendar year 2001. Because the first month recorded in 2001 is July, July 2001 becomes the parallel period of January 2002 based on simple navigation. Apply this logic to July 2002, the seventh month-level descendant of calendar year 2002, and you see why it has no parallel period in 2001, a year in which only six months were recorded.

If all twelve months for calendar year 2001 had been recorded, this problem could have been avoided. However, this problem would now be deferred to the fiscal calendar whose years start prior to 2001. In other words, there is no way in this dimension to provide complete sets of members under each period.

So what’s the solution to this problem? The short answer is there really isn’t one. You as the query developer must be aware of boundary issues such as this when developing queries employing time-based functions. You might have data at the head and tail of the time dimension extended to cover periods for which no data is recorded to avoid misalignment as illustrated previously, but you still need to be aware that no data is recorded for those periods so that some forms of analysis, such as period-over-period growth, might not be appropriate.

Save to your account