Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 28, 2004

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

By William Pearson

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM