MDX in Analysis Services: Mastering Time: Change across Periods

Monday May 24th 2004 by William Pearson

Understand the basics of comparing periods in the Time dimension using MDX. Join author Bill Pearson in a practical introduction to the mechanics of time and the analysis of change, from period to period.

About the Series ...

This is the fifteenth 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 tutorial, Introducing DISTINCT COUNT, we introduced the concept of distinct counts, discussing why they are often a requirement in our analysis efforts and those of the information consumers whom we support. In our introduction and throughout our examination of the MDX syntax we assembled to achieve our illustrative objectives, we highlighted the challenges that accompany the use of distinct counts. We performed practice exercises, to illustrate solutions for hypothetical business needs that called upon the use of distinct count capability, obtaining exposure to the options afforded us by the MSAS user interface, as well the MDX syntax involved with using the alternative solutions that we proposed.

In this article and others subsequent to it, we will focus on time considerations in our MDX queries, and how we can successfully report change over time, as well as to accumulate those changes to present the precise snapshots, trends and other time-based metrics so dearly appreciated in business. We will briefly discuss common needs with regard to relative time, and then undertake a multi-step practical exercise, built around a hypothetical business need, to illustrate a potential solution for that need.

Time and Relative Measurement

As virtually any accountant or finance manager knows, the need to present results in reference to time is important. It is hard to imagine a cube without a time dimension (although I have seen it, it is rare, indeed), because time is a pervasive concept in analysis. Business is measured by activity and activity is based heavily upon the concept of time. Because we measure virtually everything at a point in time (such as an asset, or other balance, within a balance sheet), or over a period of time (such as revenues within an income statement), time becomes a consideration that relates with most other common dimensions we see in cubes.

Time also serves as the foundation that underlies another important concept, growth. Growth of course, can be a positive or negative quantity, the desirability of which is dictated by the dimension and measure intersects that are posed in a given scenario. Revenues, production volumes, returns on equity and other measures are commonly seen to be good candidates for growth, especially when that growth is happening within the scope of our own interests. Likewise, decreases, shrinkage and other antonyms of growth can be equally delightful news, when announced within the context of overhead expenses, operating costs, tax liabilities, customer complaints and many others. To have meaning, growth and its opposites must have referential context, and often the more "comparability" we can obtain, the better. It is thus vital to be able to ascertain the change in measures over time. Second in importance only to the positive / negative nature of those changes are the tandem considerations of "how much" and "how rapidly."

The cyclical nature of business (more in some industries than others, of course) is another compelling reason to be able to provide period-based analysis. As is somewhat obvious, a cyclical business will obtain more useful "period over period" analysis if they are able to compare "apples and apples." An example might be the sales of those frivolous, transitory items that seem to appear in advertising only around the Christmas season; things one would purchase as a gift to in-laws, if at all, such as "hotdog toasters" and the like. Month-to-month comparisons of revenues / units sold information for these products would not be scrutinized as heavily as, say, November and December sales for the most recent year compared to the sales of the same months in the previous year.

MSAS provides excellent tools for presenting data within the relative time concepts so valued by information consumers. The implements it offers us in this arena include:

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

NOTE: For detailed information on time-series functions, see the following Database Journal articles in my MDX Essentials series:

This article, as well as subsequent articles in this series, 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, explaining each within a multi-step practice exercise.

Handling Period Comparisons at Numerous Levels

In this article, we will focus on period-related comparisons. By "period," I mean general levels of the time hierarchy, and not strictly months (some accounting systems refer to months as "periods"). Periods can be days, weeks, months, quarters, years, and others that occur between and external to these.

In addition, we will pursue a solution that handles a requirement to compare periods, but does so in such a way that the comparison will occur 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, so that we can assimilate the concepts in a logical fashion, and see the working components in meaningful increments, rather than examining an omnibus query that requires the "distraction of dissection" that many of us have come to despise in today's pop technology media.

Let's take a look at a scenario that illustrates a need for a period-to-period comparison, using a hypothetical business need to add practical value. Let's say that a group of information consumers within the FoodMart organization have approached us with a need that they wish to meet using data that is housed within the Warehouse cube. The consumers want to be able to report upon the change in Warehouse Sales from month to month, but also want the ability to see the same delta at the quarter, semi-annual and annual levels of the Time hierarchy for year 1997.

We will approach this in steps, beginning with the following, and using our old friend, the Sample Application, as a platform from which to perform our practice exercises.

1.  Start the MDX Sample Application.

We are initially greeted by the Connect dialog, shown in Illustration 1.

Illustration 1: The Connect Dialog for the MDX Sample Application

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

2.  Click OK.

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 2, complete with the information from the Warehouse cube displaying in the Metadata tree (left section of the Metadata pane).

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

We will begin creating our query with a focus on building the Time hierarchy we need into the row axis, with the single Warehouse Sales measure, initially returning the "direct pulls" that require no calculated members. We will then add the additional Time aggregations we need, in the next step. Finally, in the last enhancements to the core query, we will add a calculated member, which we will call "Delta-to-Last," meaning "change over the last period." Delta-to-Last will reflect the appropriate change value at any level of the hierarchy.

6.  Create the following new query:

-- MXAS15-1:  Simple Time Hierarchy on Row Axis
   { [Measures].[Warehouse Sales] } ON COLUMNS,
   { [Time].[Year].[1997].[Q1].Children,
                                        [Time].[Year].[1997]} ON ROWS

7.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 3.

Illustration 3: The Results Dataset - Simple Time Members in Row Axis with Basic Measure

8.  Save the query as MXAS15-1.

This is about as simple as it gets, with the idea, again, to get the core Time levels enumerated and aligned in the row axis. Now let's add the components that are missing from the information consumers' specification: First and Second Half-Year aggregations.

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

-- MXAS15-2:  Expanded Time Hierarchy (Adding Half-Year Aggregations) on Row Axis

10.  Save the query as MDX15-2, to preserve MDX15-1.

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

     [Time].[1st Half-1997] 
    '[Time].[1997].[Q1] + [Time].[1997].[Q2]'
MEMBER [Time].[2nd Half 1997] 
    '[Time].[1997].[Q3] + [Time].[1997].[Q4]'

The purpose here is to create the two half-year aggregations, giving us the capability to present a "semi-annual" level within our Time row axis. Now, let's add the calculated members to the existing SELECT statement, in the appropriate positions for a logical presentation, by taking the following step:

12.  Under the following line within the SELECT statement of the existing query:


and before the following line, already in place beneath it:


insert the following expression:

[Time].[1st Half-1997],

We thus insert the first Half-Year aggregation at a logical point in the Time hierarchy in the rows axis. Now let's add the second semi-annual aggregation.

13.  Under the following line within the SELECT statement of the existing query:


and before the following line, already in place beneath it:

[Time].[Year].[1997]} ON ROWS

insert the following expression:

Time].[2nd Half 1997],

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

Illustration 4: The Query with Modifications Circled

14.  Execute the query using the Run Query button.

The results dataset appears, with new calculated members circled in red, as shown in Illustration 5.

Illustration 5: The Results Dataset - New Calculated Members Circled

15.  Save the query as MXAS15-2.

We now have the Time hierarchy in place in accordance with the information consumer specifications. We will next add the logic that returns the period-to-period delta, regardless of the hierarchy level that the time member inhabits, as we shall see.

16.  Within the query we have saved as MXAS15-2, replace the top comment line of the query with the following:

-- MXAS15-3:  Complete Query  
  (Adding Period-to-Period Change 
  on Column Axis)

17.  Save the query as MDX15-3, to preserve MDX15-2

18.  Under the WITH keyword, and before the following line, already in place beneath it:

  [Time].[1st Half-1997] 
  '[Time].[1997].[Q1] + 

insert the following expression:

  '[Measures].[Warehouse Sales]- 
    ([Measures].[Warehouse Sales], 

We have thus inserted another calculated member, ahead of the calculated members we created in MDX15-2. Now, let's add it to the SELECT statement so as to retrieve the period-to-period difference as part of the query result dataset.

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

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

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

20.  Insert 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 Sales] measure, within the brackets of the ON COLUMNS line of the SELECT statement. The modified line will appear as shown:

{ [Measures].[Warehouse Sales], [Measures].[Delta-to-Last] } ON COLUMNS,

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

Illustration 6: The Query with Modifications Circled

21.  Execute the query using the Run Query button.

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

Illustration 7: The Results Dataset New Calculated Member Circled

22.  Save the query as MXAS15-3.

A close examination reveals that the calculated member we have created "delivers the goods." The mechanics behind the calculation are a mix of simple math and a combination of the .CurrentMember / .Prevmember functions. The nucleus of the calculation is as follows:

'[Measures].[Warehouse Sales]- ([Measures].[Warehouse Sales], [Time].CurrentMember.PrevMember)'

NOTE: For a detailed introduction to the .CurrentMember and .PrevMember functions, see MDX Member Functions: "Relative" Member Functions , in the Database Journal MDX Essentials collection.

As many of us know, 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 the combination of the two to obtain just the value we seek, the difference between Warehouse Sales at the current period (composed of the intersect of a given point in the time hierarchy and [Measures].[Warehouse Sales]), and the same value for the "previous period" no matter what level we "currently" occupy: we are simply asking for "the previous to the current" period through the construct of [Time].CurrentMember.PrevMember. We are thus obtaining the same intersect as in the first use of Warehouse Sales, only with a forced intersect at [Measures].[Warehouse Sales], and the "previous to the current" period.

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

Summary and Conclusion ...

In this article, we began a focus on the time dimension from the perspective of our MDX queries. Our intent was 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.

We undertook a discussion of general business needs with regard to the concept of relative time. We then participated in a practical, multi-step exercise, based upon a hypothetical business need, to illustrate a potential solution for the stated requirement: We captured changes over time, while applying the same MDX logic to all levels of the Time hierarchy.

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

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