MDX in Analysis Services: Mastering Time: Period - to - Date Aggregations

Monday Jun 28th 2004 by William Pearson

Accomplish year-to-date and other aggregations periods in the Time dimension using MDX. Join author Bill Pearson in a hands-on introduction to period-to-date aggregations at multiple hierarchical levels.

About the Series ...

This is the sixteenth tutorial article of the series, MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services ("MSAS"); our primary focus is the manipulation of multidimensional data sources, using MDX expressions, in a variety of scenarios designed to meet real-world business intelligence needs.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see the first lesson of this series: MDX Concepts and Navigation.

Note: At the time of writing, Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. The screen shots that appear in this article were taken from a Windows 2003 Server, and may appear somewhat different from coinciding views in other operating systems.


In our last article, Mastering Time: Change across Periods, we began a focus on the Time dimension from the perspective of our MDX queries. Our intent, then and in subsequent articles, is to begin an exploration of ways to effectively report change over time, as well as to accumulate those changes to present snapshots, trends and other time-based metrics in a precise manner to meet typical business requirements.

In Change across Periods, we began with a discussion of general business needs as related to the concept of relative time. We then undertook a practical, multi-step exercise, based upon a hypothetical business need, to illustrate a potential solution for a stated requirement. We captured changes over time, while applying the same MDX logic to all levels of the Time hierarchy, within an illustration where we met a stated set of business needs.

In this article, we will examine "period-to-date" aggregations, a common business requirement. Year-to-Date totals top the list in popularity here, but the same concept of accumulation over a period range applies at subordinate time levels, as well. We will examine the means of managing such requirements, using MDX within MSAS to accomplish our ends. We will then undertake a multi-step practice example that activates the underlying concepts, discussing our objectives with each step, as well as the results we obtain with each.

Time and Accumulation

As we discussed in our last article, many business requirements revolve around the performance of analysis within the context of time. In performing virtually any type of data analysis with a cube, we are typically confronted with the need to do so with reference to time. In our last session, we discussed, and worked through a hands-on example surrounding, meeting a need to compare a measure or measures across months, quarters, or years. In addition to analyzing growth over periods in time, a need upon which we focused in Change across Periods, another need, familiar to most of us who have worked within accounting and finance circles, is the requirement to present year-to-date totals, averages or other accumulations.

Because of pervasive business needs to analyze data within the context of time, MDX provides a specialized group of time series functions to meet these and many other needs. The article MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions, in my Database Journal MDX Essentials series, focuses in detail on the syntax and operation of the PeriodsToDate() function, together with the specialized "shortcut" functions that are based upon it, including the YTD(), QTD(), MTD(), and WTD() functions.

If you haven't read MDX Time Series Functions, Part I, it might act as a great syntopic reinforcement, either before or after completing this lesson, as examining concepts from multiple perspectives often tends to activate them more effectively. Following this approach, together with PhotoReading and other tools, helps me to master new material quickly. I heartily suggest it to anyone who shares my opinion that time (in general) is the only thing that is really important.

As we mentioned in our last article, MSAS provides excellent tools for presenting data within the relative time concepts so valued by information consumers, and the same applies within our examination of period-to-date concepts. The implements MSAS offers include:

  • Time dimension(s);
  • Alternate hierarchy capabilities;
  • MDX time-series functions;
  • Calculated members.

The PeriodsToDate() function, according to the Analysis Services Books Online, "returns a set of periods (members) from a specified level, starting with the first period and ending with a specified member." Within the scope of a level that is specified within the function, the function returns a set of periods that exist on the same level as the member that is also specified in the expression. In other words, it returns the number of periods within the specified level, up to and including the specified member. When no level or member is specified, the value of the member is Time.CurrentMember (a concept we have discussed earlier in our series), with the Level value being the parent level of Time.CurrentMember.

As we attempted to do in Mastering Time: Change across Periods, this article will treat the practical presentation of data with time as a consideration. While the specialized time-series functions are called upon, we will look at a combination of MDX elements to bring about the results we are called upon to obtain.

Handling Time-based Accumulation at Numerous Levels

In this article, we will focus on time-based accumulation. By "accumulation," I mean the calculation of a period-to-date total, through summing or other aggregation options over a range of members of the Time dimension. As in our last article, we will manage this over general levels of the time hierarchy, and not strictly within the domain of years, although most of us probably see "Year-to-Date" accumulations most frequently in business - largely as a means of showing "where the business is at this point in the year," and quite often as a means of providing comparison to "where the business was at this point last year," or some other year. The "period" in "period-to-date" can obviously be days, weeks, months, quarters, years, and other levels that occur between, and externally to, these. An important objective to consider within our coding efforts is to make our solution work at multiple levels, so as to take advantage of the general power of OLAP.

With the foregoing in mind, we will pursue a solution that handles a requirement to accumulate periods, but which does so in a way that the accumulation occurs at all relevant levels. We will do this within the frame of an exercise where we build the MDX from scratch to meet a hypothetical business requirement, as we do in most of our MDX articles. This way, we can assimilate the concepts in a logical fashion and see the working components in meaningful increments. This avenue also allows us to avoid the "distraction of dissection" that often accompanies the "documentation" and procedural articles that we sadly find to be the norm these days.

We will start with a hypothetical scenario that illustrates a need for a period-to-date accumulation. Let's say that a group of FoodMart information consumers have come to us with a business need that they wish to meet using Warehouse (one of the sample cubes that install with a typical MSAS installation) data. The consumers want to be able to report upon the "to-date" warehouse profit, certainly within a "year-to-date" context, but also from the perspective of quarters and months, for year 1998.

We will approach this in steps, again using the Sample Application (which also accompanied our MSAS installation), as a platform from which to perform our practice exercise.

1.  Start the MDX Sample Application.

We are initially greeted by the Connect dialog.

2.  Click OK, after ensuring that appropriate settings are in place for the local environment.

The MDX Sample Application window appears.

3.  Click File --> New.

A blank Query pane appears.

4.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

5.  Select the Warehouse cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that depicted in Illustration 1, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).

Illustration 1: The MDX Sample Application Window (Compressed View)

As we did in the previous session, we will begin creating our query with a focus on building the Time hierarchy we need in the row axis, only this time with the single Warehouse Profit measure, initially returning the "direct pulls" that require no calculated members. Once the core query is in place, we will enhance it, in the next phase, to add in a calculated member for what we will call "Profit-to-Date," meaning "accumulated Warehouse Profit through the current period," which will reflect the appropriate accumulated value at any level of the hierarchy.

1.  Create the following new query:

-- MXAS16-1:  Simple Core Query (Time Dim Axis and Regular Measure)
   { [Measures].[Warehouse Profit]} ON COLUMNS,
   { [Time].[Year].[1997].[Q1].Children,
                                       [Time].[Year].[1997]} ON ROWS

2.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 2.

Illustration 2: The Results Dataset - Simple Time Members in Row Axis and Basic Measure

3.  Save the query as MXAS16-1.

The core query allows us to get the Time levels aligned in the row axis. We are now free to focus on the relative time concepts involved with meeting the information consumer's stated business need. The enhancements we graft in next will add the Profit-to-Date calculated member on the column axis, to the right of the Warehouse Profit measure,

4.  Within the query we have saved as MXAS16-1, replace the top comment line of the query with the following:

-- MXAS16-2:  Complete Query  (Adding Profit-To-Date Calculated Measure on Column Axis)

5.  Save the query as MDX16-2, to preserve MDX16-1.

6.  Under the comment line we have adjusted, and before the SELECT statement, add the following expression:

MEMBER [Measures].[Profit-to-Date] 
      PERIODSTODATE([Time].[Year], [Time].CurrentMember),
         [Measures].[Warehouse Profit]

The purpose here is to create a Year-to-Date aggregation, using a summed PeriodsToDate() function to generate a set that delivers what we need. Now, let's add the calculated member we have defined above to the existing SELECT statement, in the appropriate position to present it to the right of the Warehouse Profit measure, by taking the following step:

7.  Within the first line of the SELECT statement, which currently appears as follows:

{ [Measures].[Warehouse Profit]} ON COLUMNS,

Insert a comma between the right-most bracket in the line (the "]" just to the right of Warehouse Profit) and the right-most curly brace ("}").

8.  Insert a space, then the following expression, between the comma just inserted, and the right-most curly brace:


We are simply adding the new calculated member to the right of the existing [Measures].[Warehouse Profit] measure, within the brackets of the ON COLUMNS line of the SELECT statement. The modified line will appear as shown:

{ [Measures].[Warehouse Profit], [Measures].[Profit-to-Date]} ON COLUMNS,

The Query pane appears as shown in Illustration 3, with the newly inserted coding circled in red.

Illustration 3: The Query with Modifications Circled

9.  Execute the query using the Run Query button.

The results dataset appears, with new calculated member circled in red, as shown in Illustration 4.

Illustration 4: The Results Dataset - New Calculated Member Circled

10.  Save the query as MXAS16-2.

A review of the dataset that is returned reveals that our Profit-to-Date calculated member meets the expectations of our information consumers The mechanics behind the calculation reflect a combination of the Sum() function and the PeriodsToDate() function, together with .CurrentMember, used in a manner similar to the way we used it in our last article. The nucleus of the calculation is as follows:

      PERIODSTODATE([Time].[Year], [Time].CurrentMember),
         [Measures].[Warehouse Profit])'

NOTE: For a detailed introduction to the PeriodsToDate() function, please see MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions, and for more details surrounding the .CurrentMember function, see MDX Member Functions: "Relative" Member Functions, in the Database Journal MDX Essentials collection.

As we have observed numerous times, within this and other series, it is often necessary, in building MDX statements, to relate a current member value to others in the cube hierarchy, just as we have done in the above expression. Although MDX has numerous methods that can be applied to a member to traverse hierarchies, the most commonly used ones certainly include .CurrentMember and .PrevMember. We have used .CurrentMember, in this instance, to obtain precisely the value we seek, the accumulated "to-date" total of Warehouse Profits, at the current period (composed of the intersect of a given point in the time hierarchy and [Measures].[Warehouse Profit].

11.  Exit the MDX Sample Application and Analysis Manager when ready.

Summary and Conclusion ...

In this article, we continued in the direction in which we began in our previous article, focusing on the Time dimension from the perspective of MDX queries. We examined "period-to-date" aggregations, initially with a discussion of their pervasiveness as a common business requirement. We mentioned that, although Year-to-Date totals are, perhaps, the most common manifestation of period-to-date constructs, the same concept of accumulation over a period range applies at subordinate time levels, as well. We illustrated this point in the practice example that we undertook next.

We activated the concepts that we explored in a hands-on example, which illustrated the satisfaction of a hypothetical business requirement we received from a group of information consumers. Within the steps of the example, we used MDX to establish a core result set, then to generate the period-to-date accumulation at every level of the Time hierarchy, discussing our objectives with each step, as well as the results we obtained with each.

» See All Articles by Columnist William E. Pearson, III

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved