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

May 24, 2004

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:


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

insert the following expression:


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.

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:

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


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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers