Home > Sample chapters > Programming > SQL Server

Working with Time in Microsoft SQL Server 2008 MDX

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

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

    httpatomoreillycomsourcemspimages409551.jpg

    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.

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

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

    httpatomoreillycomsourcemspimages409555.jpg

    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.

    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.

  8. 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]
  9. Execute the query and review the new Quarter To Date Reseller Sales values.

    httpatomoreillycomsourcemspimages409557.jpg

    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.

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

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

    httpatomoreillycomsourcemspimages409563.jpg

    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.

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

    httpatomoreillycomsourcemspimages409565.jpg

    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.

  6. 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]
  7. Execute the query and compare the monthly reseller sales values to the three-month rolling average values.

httpatomoreillycomsourcemspimages409567.jpg

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.