MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions

Tuesday Jul 8th 2003 by William Pearson
Share:

Join author Bill Pearson in the first of a set of three articles surrounding the time series functions, a specialized group of functions designed to meet the pervasive business need to analyze data within the context of time. In this lesson, we will overview the PeriodsToDate() function, then we will introduce specialized 'shortcut' functions that are based upon it.

About the Series ...

This is the ninth article of the series, MDX Essentials. The primary focus of this series is a hands-on introduction to the fundamentals of the Multidimensional Expressions (MDX) language. Each tutorial progressively adds features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements needed for getting the most out of the lessons included, please see the first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

What We Accomplished in our Last Article

In the eighth article of the series, MDX Member Functions: "Relative" Member Functions, we performed an overview of additional "relative" member functions, expanding upon our earlier exposure to the powerful .Members function, and extending our exploration to the .CurrentMember, .PrevMember and .NextMember functions. I introduced calculated members briefly, primarily as a vehicle for demonstrating the use of the "relative" member functions. I also stated that we will take up calculated members repeatedly, and in far more detail, as the series progresses. The opportunity for exposure to them in our last article, and our recurring use of them going forward to achieve various objectives, will serve to make their characteristics and uses familiar as we progress in the series.

In this lesson, we will introduce the time series functions group. As we discussed in our last lesson, many business requirements revolve around the performance of analysis within the context of time. We saw simple approaches to meeting examples of these requirements by using the CurrentMember, .PrevMember and .NextMember functions, mainly because the time dimension provides an intuitive platform for the demonstration of many functions such as these.

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 needs. In this lesson, we will overview the PeriodsToDate() function, then we will discuss the specialized "shortcut" functions that are based upon it, including the YTD(), QTD(), MTD(), and WTD(). In subsequent lessons, we will explore other time series functions and expressions, together with other capabilities of MDX to help us to meet typical business needs.

Introduction

In performing virtually any type of data analysis with a cube, we become acquainted early on with the need to do so with reference to time. We might receive a request to compare revenue or expense across months, quarters, or years, for example. An information consumer might need to analyze growth over periods in time, as another illustration. And common to any of us who have worked within accounting and finance circles is the requirement to present year-to-date totals, averages or other accumulations.

The time series functions are specifically designed to support time-based analysis. They are largely applied to dimensions of the "time" type, but can be applied to other dimensions as well. We can use the time series functions to produce elaborate calculations and aggregations, some of which we will introduce as the series progresses. Although, as an implementer, I have found different client environments to require differing numbers of levels within the time hierarchy (most accounting and financial scenarios have required that we present time down to the month level, but others have required the capability to report to the hour, minute, and even second levels), the time dimension within all of these environments has held one common attribute: the descendants of any given member in a time level are the same as the descendants of its peer levels. As an example, the descendants of the Year member 1997, in a typical scenario, are composed of Quarters 1 through 4, and Months January through December, while the descendants of peer Year member 1998 are identical.

The similarity in structure between members in a time dimension makes comparisons simple and efficient. The time series functions allow us to perform comparisons and other operations upon peer members that exist throughout the hierarchy (a simple example would be [Time].[1997].Q3 and [Time].[1998].Q3), sequential members within a given level (such as the months indicated by [Time].[1998].[Q3].7 and [Time].[1998].[Q3].8), and other relationships based upon hierarchical similarities. Illustration 1 depicts some of the possibilities.


Illustration 1: Time Hierarchy as Found in the HR Sample Cube (Partially Expanded)

In this lesson we will introduce the ubiquitous PeriodsToDate() function, addressing various common considerations of time series functions as a part of our exploration. We will also reference other specialized time series functions that are based upon the PeriodsToDate() function. Our lesson will include an introduction to the PeriodsToDate() and related specialized functions, with:

  • an examination of the syntax surrounding the use of each;
  • an illustrative example of the use of each in a practice exercise;
  • a brief discussion of the MDX results we obtain in each practice example;
  • a review of the use of calculated members, primarily as a means of exploring the time series functions of this lesson;
  • an introduction to the Sum function, and its use within a calculated member.

The PeriodsToDate() Function

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.

Discussion

The PeriodsToDate() function allows us to meet very common business needs, including the calculation of a year-to-date total. The calculation of this total requires accumulation over a range of members of the Time dimension. This set of time members is easily assembled using the PeriodsToDate() function, although other, less direct approaches exist to meet this requirement.

Syntax

Syntactically, the source member is placed within the parentheses to the right of PeriodsToDate, as shown in the following illustration:

PeriodsToDate([«Level»[, «Member»]])

The function returns the number of periods within the «Level», up to and including «Member». The following simplified example expression would return the twelve months of year 1998:

PeriodsToDate(Year, [Time].[1998].[12]

In the above, 12 is the month of December as it exists in the HR sample cube, which is provided with the typical Analysis Services installation. The members returned using the above expression within a query would be the same as those returned using the following range expression:

Time.[1998].[1]: Time.[1998].[12]

In the above, much as we see in other languages, the colon operator denotes a range; in our case, the range is members of the Time dimension, Year 1998, Month level, members 1 (January) through 12 (December).

Let's look at a more involved example, using a calculated member as the means to our ends. As we saw in our last lesson, a calculated member is an excellent vehicle for familiarizing ourselves with the syntax.

WITH

   MEMBER [Measures].[YTD Salaries] AS
   'Sum(PeriodsToDate([Time].[Year]),[Org Salary])'

SELECT
 
   {[Measures].[Org Salary],[Measures].[YTD Salaries]} ON COLUMNS,
     [Time].[Quarter].Members ON ROWS

FROM HR

The result dataset returned would appear as shown in Illustration 2:


Illustration 2: Example Result Dataset Using the PeriodsTodate() Function

For a review of what we learned in the previous lesson about calculated members, we need look no further than the above example. The WITH section contains the definition of the calculated member / measure YTD Salaries. We note, as well, that the PeriodsToDate() function is used, together with, and within the context of, the Sum statement; all are used within the definition of the calculated member. Let's "parse" the action here into plain English, as the combination we see above is certainly typical enough in the real world.

First, the YTD Salaries measure is being defined, via the Sum statement, as the "sum of a numeric expression evaluated over a set." The syntax of a simple sum statement is relatively straightforward, and might be represented as follows:

Sum((«Set»[, «Numeric Expression»]))

The Org Salary measure inhabits the Numeric Expression portion of the Sum statement. The Numeric Expression portion of the Sum function in effect serves as the column axis in the case of our calculated measure. We are asking for Org Salary to be displayed on the column axis, and all Quarter-level members of the Time dimension (which, in the HR sample cube, are the quarters of 1997 and 1998 only) to be displayed on the rows axis.

The first half, or Set portion of the Sum statement (in our example (PeriodsToDate([Time].[Year])) serves as the row axis for a subquery that is generated by the Sum function. When all is done, its effect is to ask for a set of members to be returned a set of members that we can use in the Sum function as a basis for accumulation.

In addition to selecting Org Salary as a component in our function, we bring it into our query as the standard measure ([Measures].[Org Salary]); this is done to illustrate the actual quarterly totals, so that we can grasp the effects of the Sum function visually. It's easy to see what is happening, as the YTD Salaries calculated measure obviously increases each quarter by the amount of the quarterly Org Salary expense, illustrating the cumulative effect that is brought about through our use of the PeriodsToDate() function as the Set portion of the Sum statement. We note, too, that the accumulated YTD Salaries value "resets" at the first quarter of 1998, because our scope is defined as Year.

Practice

Let's begin the hands-on portion of the lesson by creating a calculated member to reinforce our understanding of the basics we have covered so far, and by using the PeriodsToDate() function in a manner that assists our ends. The MDX Sample Application (see the second article in our series, Structure of the MDX Data Model, for more information about the Sample Application) will, as usual, be our tool for constructing and executing the MDX we examine, and for viewing the result datasets we obtain.

1.             Start the MDX Sample Application.

2.             Clear the top area (the Query pane) of any queries or remnants that might appear.

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

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

We will begin with an illustration that creates a calculated member and uses the PeriodsToDate() function in a manner similar to the example we cited earlier, but with a difference in time levels to enrich the example: our query will focus on quarter-to-date organization salaries, presented on a monthly basis, to present a result set comprising the two years stored in the HR sample cube.

First, let's run a simple query to gain an understanding of our data. We will ascertain the values for monthly total salaries values first, and build an inherent explanation into our ultimate objective by approaching the logic we reviewed above, this time from a perspective of construction.

5.             Type the following query into the Query pane:

-- MDX09-1:  Tutorial Query No. 1
SELECT    
   {[Measures].[Org Salary]} ON COLUMNS,    
     [Time].[Month].Members ON ROWS
FROM HR

Analysis Services fills the Results pane, presenting the dataset depicted in Illustration 3.


Illustration 3: Result Dataset - Monthly Total Salaries

The query delivers the results that we requested; we see the total organization salary amounts returned for each month. Keep these amounts in mind as we progress.

We are asked to produce values for Quarter-to-Date Salaries for each month, accumulating the totals for each month from the beginning of each quarter. The information consumers requesting this information want the quarter-to-date cumulative salaries to appear in a column to the right of the month salary totals. Let's set about meeting this business need with a query containing the Sum statement and other components that we have already previewed.

6.             Type the following query into the Query pane:

-- MDX09-2:  Tutorial Query No. 2
WITH
   MEMBER [Measures].[QTD Salaries] AS
   'Sum(PeriodsToDate([Time].[Quarter]),[Org Salary])'
SELECT 
   {[Measures].[Org Salary] , [Measures].[QTD Salaries]} ON COLUMNS, 
     [Time].[Month].Members ON ROWS
FROM HR

7.             Click the Run button on the toolbar atop the Sample Application, to execute the query.

As soon as the specified cells in the Results pane are filled by Analysis Services, we see the result dataset depicted in Illustration 4.


Illustration 4: The Query Result Dataset

The query delivers the results that we requested; we see the total monthly salaries (Org Salary) amounts aligned side by side with the cumulative quarter-to-date totals (QTD Salaries). Note that the quarter-to-date totals "reset" every three months, beginning with the total of the first month and accumulating thereafter until the next quarter begins. The PeriodsToDate () function was designed to provide this highly popular result for business users.

To restate our request in words, we are asking, within the QTD Salaries calculated measure, for the sum of Org Salaries, returned within the scope of quarters, for the set of periods on the level of quarters. When we request that the results be presented in terms of months, as we do in the SELECT clause of the query, the result is an accumulation that states the total for each quarter (the scope of the PeriodsToDate() function) within the context of each succeeding month period. Since we specify a level in our function, ([Time].[Quarter]), the function returns the set of periods on the level of quarters, beginning with the first quarter Q1 (the first member of the quarter level) and ending with [Time].[Quarter].CurrentMember (because we didn't specify a member in the function), or the quarter in which the monthly total is displayed.

Note: For a discussion of the .CurrentMember function, see my earlier articles on the members functions within this series, as well as other series' articles I have published within the Database Journal library.

If it is desirable to save this or subsequent queries, we can do so by selecting File -> Save As, and give the file a meaningful name and location.

Now let's take a look at the "shortcut functions" that derive from the PeriodsToDate() function.

The PeriodsToDate() Shortcut Functions

As we learned above, the PeriodstoDate() function is used within the following basic syntax:

PeriodsToDate([«Level»[, «Member»]])

Several "shortcut functions" are available in MDX to allow us to perform time-based summaries on period-to-date levels. Among these levels are the following, displayed in Table 1, that correspond to common levels of measurement in the business environment, together with the associated, specialized shortcut function that exists in MDX.

Level of Measurement

Shortcut Function

Year to Date

YTD()

Quarter to Date

QTD()

Month to Date

MTD()

Week to Date

WTD()

Table 1: The PeriodToDate() Shortcut Functions

Discussion

The above functions are equivalent to the PeriodsToDate() function, with the appropriate levels fixed for the specialized context within which each might be used. A composite of the syntax that each uses in the sample cube is as follows:

PeriodsToDate([Year] | [Quarter] | [Month] | [Week] , [«Member»]])

Because the syntax is similar to all the shortcut functions, and because the operation of each is identical, with the exception of the specific levels involved, we will examine one sample of the group to confirm our understanding of general concepts. To this end, let's examine the QTD() function specifically.

Syntax - QTD() Function

As is the case with each member of the set of shortcut functions listed in Table 1 above, the QTD () function is a shortcut function derived from the PeriodsToDate () function. The QTD() function defines the PeriodsToDate () function's «Level» argument to be Quarter. If no member is specified, the default is Time.CurrentMember. In short, therefore:

Qtd ([«Member»])

is equivalent to

PeriodsToDate(Quarter, «Member»)

The following example returns the set of members from the beginning of the Quarter level in the Time dimension, from the ancestor of Time.CurrentMember , through Time.CurrentMember:

Qtd()

Characteristically for all PeriodsToDate() derivatives, if no member is specified, the default is Time.CurrentMember.

Practice

Let's practice with the QTD() function, combining its use with that of a calculated member, in a manner that assists us in performing analysis of performance from one year to the next. We will rejoin the MDX Sample Application, creating a new query for the purposes of this exercise.

8.             Select File --> New from the top menu of the MDX Sample Application.

9.             Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar, and that the HR cube remains selected in the Cube drop-down list box.

10.         Type the following query into the Query pane:

-- MDX09-3:  Tutorial Query No. 3
WITH 
   MEMBER [Measures].[QTD Salaries] AS
   'Sum(QTD(),[Org Salary])'
SELECT 
   {[Measures].[Org Salary] , [Measures].[QTD Salaries]} ON COLUMNS,  
     [Time].[Month].Members ON ROWS
FROM HR

11.         Select Query --> Run from the top menu to execute the query.

The query runs and the result dataset appears, as shown in Illustration 5.


Illustration 5: The Query Result Dataset

The query delivers the results as requested; we see the results above to be identical to those shown in Illustration 4 above. The reason is simple - we have substituted the QTD() function for

(PeriodsToDate([Time].[Quarter])

in the previous example, primarily to illustrate that the two functions deliver the identical result. QTD() is, as we have learned, a shortcut function for the PeriodsToDate() function with the «Level» argument set to Quarter. We thus prove the fact that the functions are equal by substituting the shortcut QTD() for PeriodsToDate().

The remaining shortcut functions behave in a similar manner.

We can easily see the utility of the shortcut functions for cases where the <<level>> argument, such as Quarter in our last example, can be predefined. Again, if no member is specified in the argument, the default is fixed as Time.CURRENTMEMBER by MDX.

Next in Our Series ...

In this lesson, we introduced the time series functions group, a specialized group of time series functions that MDX provides to perform analysis within the context of time. After discussing the common business need to analyze data over time, we overviewed the PeriodsToDate() function in detail, illustrated the syntax that is appropriate for its effective use, and then tested our understanding of how to leverage this important time series function by undertaking a relevant practice exercise.

As part of our use of the PeriodsToDate() function, we revisited the construction of a calculated member, using a calculated measure as a vehicle for illustration of the operation of the PeriodsToDate() function. We introduced the Sum statement as a part of building the calculated member, and discussed its constituent parts, as well. (We will revisit the Sum function many times as we progress in our series.) We then discussed the specialized "shortcut" functions that are based upon PeriodsToDate(), including the YTD(), QTD(), MTD(), and WTD() functions.

In our next lesson, we will introduce two additional time series functions, OPENINGPERIOD() and CLOSINGPERIOD(). Next, we will conclude our three-article time series functions group with an examination of the LASTPERIODS() and PARALLELPERIODS() functions.

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

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

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