Home > Sample chapters > Programming > SQL Server

Working with Time in Microsoft SQL Server 2008 MDX

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

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

  2. 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]
  3. Execute the query and review the results.

    httpatomoreillycomsourcemspimages409569.jpg

    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.

  4. 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]
  5. Execute the query and review the results.

    httpatomoreillycomsourcemspimages409571.jpg

    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.

  6. 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]
  7. Execute the query and review the results.

    httpatomoreillycomsourcemspimages409573.jpg

    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.

  8. 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]
  9. Execute the query and review the results.

    httpatomoreillycomsourcemspimages409575.jpg

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