MDX Essentials: MDX Member Functions: "Relative" Member Functions

Monday Jun 2nd 2003 by William Pearson
Share:

Join author Bill Pearson in an overview of a few 'major players' in the 'relative' member functions. This article examines the .CurrentMember, .PrevMember and .NextMember functions. In addition, calculated members are introduced as a preview to their examination in future articles.

About the Series ...

This is the eighth article of the series, MDX Essentials. The primary focus of this series is an introduction to MDX. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements needed for getting the most out of the lessons included, please see the first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

What We Accomplished in our Last Article

In the seventh article of the series, MDX Member Functions: The Cousin () Function, we explored the Cousin() function, concluding a set of articles where we examined what I call the "family" set of functions. We discussed the Cousin() function in detail, illustrated the syntax that is appropriate for its effective use, and then tested our understanding of how to leverage this remaining "family" function by undertaking relevant practice exercises. Finally, for each practice exercise, we commented on the results we obtained using the Cousin() function, and discussed some of the limitations that are inherent in its use.

In this lesson, we will begin an overview of additional "relative" member functions, expanding upon our earlier exposure to the powerful member functions, and then extending our exploration to the .CurrentMember, .PrevMember and .NextMember functions. As a part of our examination of these functions, we will introduce calculated members briefly as an avenue to demonstrating the use of the "relative" member functions. We will take up calculated members in far more detail as the series progresses, but, for now, we will preview their characteristics and dive right into using them.

Introduction

In the last group of articles, we have worked with members, which we now know to be generally defined as any attribute that belongs to a dimension. In the Time dimensions we have examined in the last three articles specifically, for example, as a basis for our examination of several "family" functions, we recall that Year and Quarter existed as levels. We could, in our example, say that 1998 and Q3 are, therefore, members of the Time dimension. We used our examples to demonstrate the workings and characteristics of member functions, which, as we discovered, return another dimension member or a zero (an example of a scenario with a zero result would be the use of the .Parent function with a top level member -- who is parentless, as was the case in one of our examples). The "family" member functions allowed us to travel the dimensional hierarchy, as we witnessed in numerous examples, based upon the relative position of a given "family" member to the member matching the hierarchical relationship specified in the function.

We will next examine other, far more common "relative" member functions. Our lesson will include an introduction to the .CurrentMember, .PrevMember and .NextMember functions, with:

  • an examination of the syntax surrounding the use of each;
  • an illustrative example of the use of each in a practice exercise;
  • a brief discussion of the MDX results we obtain in each practice example;
  • a preliminary overview of calculated members, primarily to bring them "into the fold" of our growing MDX knowledgebase, and to initially make them available as a means of exploring the relative member functions of this lesson, as well as many MDX components in future articles.

Let's introduce calculated members now, and begin our examination of the "relative" member functions shortly after.

Calculated Members

Calculated members, in short, allow us to define new members, based upon dimensions or measures that exist within the cube in which we create the calculated member. They are members whose values depend on an expression rather than the value of a cell within a cube. The potential uses of calculated members are limited only by the imagination and experience of the developer. An example might be the somewhat common business concept of a variance measure. We could create this measure using, say, an actual sales measure and a budgeted sales measure that exist in a given cube.

Discussion:

To create a calculated measure, at least within the context of our simple overview, we will use the WITH clause, the syntax for which might resemble the following:

WITH MEMBER dimension.name AS 'Expression'

Dimension represents the dimension into which we are creating the new member. Name is my shorthand for the name of the member we are creating, and expression is the expression from which it is created.

Syntax

Let's look at a simple example to gain a feel for the syntax.

	WITH 
	   MEMBER [Measures].[Warehouse Margin] AS
	   '[Measures].[Warehouse Sales]-[Measures].[Warehouse Cost]'
	
	SELECT
	
	   {([Time].[Quarter].Members)} ON COLUMNS,
	   {([Warehouse].[Country].USA)} ON ROWS
	
	FROM Warehouse
	
	WHERE ([Measures].[Warehouse Margin])

The result dataset returned would appear as shown in Illustration 1 below:


Illustration 1: Example Result Dataset Using WITH to Create a Calculated Member

As we can see above, the WITH section contains the definition of the calculated member Warehouse Margin. The newly created Warehouse Margin is a member of the Measures dimension, and is composed of the total of Warehouse Sales less Warehouse Cost, intersecting all members of the remaining dimensions in the cube, but displaying the combined intersects with the USA warehouses for purposes of our query. The returned dataset, therefore, represents the Warehouse Profit (which we called Warehouse Margin in our query) for the USA warehouses, by quarter (for the years 1997 and 1998, as stored in the Warehouse sample cube).

The SELECT keyword comes at the end of the WITH section, where it begins the specification of the axes / cells to which we have become accustomed in previous lessons. The SELECT statement allows us to place the members we wish to display upon the respective axes of the display, including enumeration of the quarters through our use of the .Members function. The WHERE clause, as we know from previous lessons, acts as the slicer dimension, which, in the immediate example, allows us to hold the new Warehouse Margin member constant for purposes of our query results.

We will explore many aspects of calculated members as we proceed through the series. This lesson will use them in the practice examples for purposes of illustrating the use of the "relative" functions that we have targeted for the article; in this way, we will continue to stay on track with our examination of the member functions while becoming comfortable with the rudiments of calculated member usage and behavior. Calculated members will be with us for a long time to come, and will provide many uses and topics of discussion.

The .CurrentMember Function

The .CurrentMember function, according to the Analysis Services Books Online, "returns the current member along a dimension during an iteration." In other words, the function references the current member in use in the cube. The axis of the query that we are constructing provides the context within which "current" has meaning, and within which our calculation is in operation. While we can apply .CurrentMember as we feel it useful, it is important to remember that .CurrentMember is actually the default operator, and its specification is, therefore, optional.

Discussion:

Analysis Server traverses the cube structure when we execute our queries, evaluating the measures that exist at each intersect point within that structure. At any given point in the execution / evaluation process, the coordinates at which the process "currently" rests is made up of each dimension in the cube and a current member (dimension1.CurrentMember, dimension2.CurrentMember, dimension3.CurrentMember, and so on). The .CurrentMember function can be used in many ways, from simple default instances to sophisticated and powerful applications.

Syntax

Let's look at an example to familiarize ourselves with the syntax.

	WITH 
	   MEMBER [Measures].[Warehouse Margin] AS
	   '([Time].CurrentMember , [Measures].[Warehouse Sales])-   
	       ([Time].Currentmember, [Measures].[Warehouse Cost])'
	
	SELECT
	
	   {(Time.CurrentMember)} ON COLUMNS,
	
	   {([Warehouse].[Country].USA)} ON ROWS
	
	FROM Warehouse
	
	WHERE ([Measures].[Warehouse Margin])

The result dataset returned would appear as shown in Illustration 2 below:


Illustration 2: Example Result Dataset Using the .CurrentMember Function

In the example above, the WITH section again contains the definition of the calculated member Warehouse Margin. We note, as well, that the .CurrentMember function is used within the definition of the calculated member -- we are simply specifying the current member of the Time dimension, which turns out to be 1997. Indeed, we get the same result set if we omit the [Time].CurrentMember specification from the AS portion of the above example, where we are defining the calculated member. This is because .CurrentMember is the default for any dimensions that we do not explicitly specify; in effect, all unspecified dimensions in the example would also be defaulted to current member. We have, therefore, really been using .CurrentMember all along, perhaps without even knowing it!

Practice

Let's begin the hands-on portion of the lesson by creating a calculated member to reinforce our understanding of the basics we have covered so far, and by using the .CurrentMember function in a manner that assists our ends. The MDX Sample Application (see the second article in our series, Structure of the MDX Data Model, for more information about the Sample Application) will, as before, be our tool for constructing and executing the MDX we examine, and for viewing the result datasets we obtain.

1.             Start the MDX Sample Application.

2.             Clear the top area (the Query pane) of any queries or remnants that might appear.

3.             Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.             Select the Warehouse cube in the Cube drop-down list box.

We will begin with a simple illustration that creates a calculated member and uses the .CurrentMember function: our query will expand the scope of the example shown above, to present a result set comprising the calculated member and the two measures from which it is derived, this time for the year 1998.

5.             Type the following query into the Query pane:

-- MDX08-1:  Tutorial Query No. 1
WITH 
   MEMBER [Measures].[Warehouse Margin] AS
   '([Time].CurrentMember , [Measures].[Warehouse Sales])-([Time].Currentmember,    
       [Measures].[Warehouse Cost])'

SELECT

   {[Measures].[Warehouse Sales],[Measures].[Warehouse Cost],[Measures].[Warehouse Margin]} 
        ON COLUMNS,
   {([Warehouse].[Country].[USA])} ON ROWS
FROM Warehouse
WHERE ([Time].[1998])

6.             Click the Run button (the green "arrowhead" icon) on the toolbar atop the Sample Application, to execute the query.

We see the result dataset in Illustration 3, which appears as soon as the specified cells in the Results pane are filled by Analysis Services.


Illustration 3: The Query Result Dataset

The query delivers the results that we requested; we see the three related measures, total Warehouse Sales, Warehouse Cost, and Warehouse Margin, side-by-side, so we can do a quick visual verification of the effectiveness of the calculation. The total margin for the USA warehouses (or profit before selling, administrative and other expenses, in accounting lingo) for 1998 is shown; we specified 1998 as our slicer dimension in the WHERE clause. (See Lesson Five, MDX Member Functions: The "Family" Functions, for more information on the WHERE clause.) The calculation (defined in our calculated member Warehouse Margin) is applied to each of the dimensions successively, with 1998 specified as the "current" member of the Time dimension.

7.             If it is desirable to save this or subsequent queries, we can do so by selecting File -> Save As, and give the file a meaningful name and location.

Now let's develop our query a step further, while taking up the next function we have slated for this lesson, .PrevMember.

The .PrevMember Function

The .PrevMember function, according to the Analysis Services Books Online, "returns the previous member in the level that contains a specified member." In other words, the function returns the member in the dimensional hierarchy occurring earlier at the same level as the specified member. The utility of the .PrevMember function is obvious when we consider the business need in Accounting and Finance circles to determine change over a given period of time (such as the current year over the prior year, as we will demonstrate in the examples that follow, as well as the current month over the prior month, and so forth).

Discussion:

Keep in mind that Analysis Server traverses the cube structure when we execute our queries, evaluating the measures that exist at each intersect point within that structure. When we use the .PrevMember function, the execution / evaluation process determines the coordinates at which the process "previously" rests, and returns the previous member in the hierarchical level. The time dimension is an excellent instance to use to demonstrate this principle. In fact, using the .CurrentMember and .PrevMember functions together gives us a great illustration of the usefulness of the .PrevMember function.

Syntax

Let's look at an example to familiarize ourselves with the syntax.

	WITH 
	   MEMBER [Measures].[Warehouse Margin PY] AS
	   '([Time].PrevMember , [Measures].[Warehouse Sales])-([Time].PrevMember, 
	       [Measures].[Warehouse Cost])'
	
	SELECT
	
	   {[Measures].[Warehouse Margin PY]} ON COLUMNS,
	   {([Warehouse].[Country].[USA])} ON ROWS
	
	FROM Warehouse
	
	WHERE ([Time].[1998])

The result dataset returned would appear as shown in Illustration 4 below:


Illustration 4: Example Result Dataset Using the .PrevMember Function

The WITH clause in the example above contains the definition of the calculated member Warehouse Margin PY, my choice for indicating that it is a "prior year" margin figure. The .PrevMember function is used within the definition of the calculated member -- we are simply specifying the previous member of the Time dimension at the year level, which turns out to be 1997, as we have specified 1998 as the slicer in the WHERE clause.

Practice

Let's practice with the .PrevMember function, combining its use with that of the .CurrentMember function, in a manner that assists us in performing analysis of performance from one year to the next. We will rejoin the MDX Sample Application, creating a new query for the purposes of this exercise.

8.             Select File --> New from the top menu of the MDX Sample Application.

9.             Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar, and that the Warehouse cube remains selected in the Cube drop-down list box.

10.         Type the following query into the Query pane:

-- MDX08-2:  Tutorial Query No. 2
WITH 
   MEMBER [Measures].[Margin CY] AS
   '([Time].CurrentMember , [Measures].[Warehouse Sales])-([Time].CurrentMember, 
      [Measures].[Warehouse Cost])'
   MEMBER [Measures].[Margin PY] AS
   '([Time].PrevMember , [Measures].[Warehouse Sales])-([Time].PrevMember,  
       [Measures].[Warehouse Cost])'
   MEMBER [Measures].[$ Change] AS
   '([Time].CurrentMember , [Measures].[Margin CY])-([Time].CurrentMember, 
       [Measures].[Margin PY])'

SELECT
   { [Measures].[Margin PY],  [Measures].[Margin CY], [Measures].[$ Change]} ON COLUMNS,
   {([Warehouse].[Country].[USA])} ON ROWS

FROM Warehouse

WHERE ([Time].[1998])

11.         Select Query --> Run from the top menu to execute the query.

The query runs and the result dataset appear, as shown in Illustration 5.


Illustration 5: The Query Result Dataset

The query delivers the results we have requested; we see the three related calculated measures, Margin PY, Margin CY, and $ Change; the latter, $ Change, is a calculated measure that is made up of the subtraction of one of the first two calculated measures from the other. We see that Margin has decreased by $2,013.80 between 1997 and 1998.

Now let's develop our query a step further, while taking up the last of the functions we have targeted for this lesson, .NextMember.

The .NextMember Function

The .NextMember function, according to the Analysis Services Books Online, "returns the next member in the level that contains a specified member." In other words, the function returns the member in the dimensional hierarchy occurring later at the same level as the specified member. The .NextMember function works in a manner similar to the .PrevMember function, except that, instead of returning the member that occurs at the same level as the specified member, earlier in the hierarchy, it returns the same-level member that follows the specified member within the hierarchy.

Discussion:

The utility of the .NextMember function is intuitive for purposes of meeting business needs that include the example we have discussed earlier, the determination of change over a given pair of periods (next year over the current year, and so forth).

Syntax

Let's look at an example to familiarize ourselves with the syntax.

 WITH 
 MEMBER [Measures].[Warehouse Margin] AS
 '[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]'
 
 
 SELECT
     
    {([Time].CurrentMember),([Time].NextMember)} ON COLUMNS,
              {([Warehouse].[Country].USA)} ON ROWS
 
 FROM Warehouse
 
 WHERE ([Measures].[Warehouse Margin])

The result dataset returned would appear as shown in Illustration 6 below:


Illustration 6: Example Result Dataset Using the .NextMember Function

The .NextMember function is used within the definition of the calculated member, inside the WITH clause. In this case, we are specifying the next member of the Time dimension at the year level, which turns out to be 1998, because, unlike in previous examples, we have specified no slicer in the WHERE clause. The result is that the current member for the year is the default, 1997. Therefore, the "next" member of the time dimension at the same hierarchical level is 1998. We note that the results we obtain appear consistent with those obtained in previous queries, from slightly different approaches.

Practice

Let's practice with the .NextMember function, combining its use with that of the .CurrentMember function, in a manner that assists us in performing analysis of performance from one year to the next, just as we have done before. We return to the MDX Sample Application, creating a new query for the purposes of this exercise.

12.         Select File --> New from the top menu of the MDX Sample Application.

13.         Type the following query into the Query pane:

-- MDX08-3:  Tutorial Query No. 3
WITH 
   MEMBER [Measures].[Margin Base Year] AS
   '([Time].CurrentMember , [Measures].[Warehouse Sales])-([Time].CurrentMember, 
       [Measures].[Warehouse Cost])'
   MEMBER [Measures].[Margin Base + 1] AS
   '([Time].NextMember , [Measures].[Warehouse Sales])-([Time].NextMember, 
    [Measures].[Warehouse Cost])'
   MEMBER [Measures].[$ Change] AS
   '([Time].CurrentMember , [Measures].[Margin Base + 1]) - ([Time].CurrentMember, 
     [Measures].[Margin Base Year])'

SELECT

   { [Measures].[Margin Base Year],  [Measures].[Margin Base + 1], [Measures].[$ Change]} ON 
       COLUMNS,
   {([Warehouse].[Country].[USA])} ON ROWS

FROM Warehouse

14.         Select key F5 from the top menu to execute the query.

The query runs and the result dataset appears, as shown in Illustration 7.


Illustration 7: The Query Result Dataset

The query delivers the results as requested; we see the three related calculated measures, Margin Base Year, Margin Base + 1, and $ Change; the latter, $ Change, is again a calculated measure that is made up of the subtraction of one of the first two calculated measures from the other.

We see that the behavior of the .NextMember function is, therefore, very similar to that of the .PrevMember function, only it acts, conceptually, "in the opposite direction." We can also readily see the potential usefulness of the function with regard to its capability to support the analysis of measures that change over time, among other uses within our data sources.

Next in Our Series ...

In this lesson, we began an overview of additional "relative" member functions, expanding upon our earlier exposure to the powerful .Members function, and extending our exploration to the .CurrentMember, .PrevMember and .NextMember functions. We introduced calculated members briefly, as an avenue to demonstrating the use of the "relative" member functions. As we noted in the Calculated Members section above, we will take up calculated members in far more detail as the series progresses; I hope that our initial exposure to them in this article, and our recurring use of them going forward to achieve various objectives, will make their characteristics and uses familiar as we progress in the series.

In our next lessons, we will introduce the time series functions group. As we discussed in this lesson, many business requirements revolve around the performance of analysis within the context of time. We saw simple approaches to meeting examples of these requirements by using the CurrentMember, .PrevMember and .NextMember functions in this lesson, mainly because the time dimension provides an intuitive way to demonstrate many functions such as these.

Because of pervasive business needs to analyze data within the context of time, MDX provides a specialized group of time series functions to meet these needs. In the next lesson, we will overview the PeriodsToDate() function, then we will discuss the specialized "shortcut" functions that are based upon it, including the YTD(), QTD(), MTD(), and WTD(). In subsequent lessons, we will explore other time series functions and expressions, together with other capabilities of MDX to help us to meet typical business needs.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved