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

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.

We are initially greeted by the Connect dialog.

The MDX Sample Application window appears.

A blank Query pane appears.

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).

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.

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

The results dataset appears as shown in Illustration 2.

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,

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

WITH
MEMBER [Measures].[Profit-to-Date] 
AS
   'SUM(
      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:

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

[Measures].[Profit-to-Date]

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.

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

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:

   'SUM(
      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].

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