MDX in Analysis Services: Mastering Time: Change across Periods - Page 5

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.

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

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

MEMBER 
  [Measures].[Delta-to-Last] 
AS
  '[Measures].[Warehouse Sales]- 
    ([Measures].[Warehouse Sales], 
     [Time].CurrentMember.PrevMember)'

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.

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

[Measures].[Delta-to-Last]

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.

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

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:

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.

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