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 May 24, 2004

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

By William Pearson

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

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