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 Jun 2, 2003

MDX Essentials: MDX Member Functions: "Relative" Member Functions - Page 3

By William Pearson

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.


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.


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

	   MEMBER [Measures].[Warehouse Margin] AS
	   '([Time].CurrentMember , [Measures].[Warehouse Sales])-   
	       ([Time].Currentmember, [Measures].[Warehouse Cost])'
	   {(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!


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
   MEMBER [Measures].[Warehouse Margin] AS
   '([Time].CurrentMember , [Measures].[Warehouse Sales])-([Time].Currentmember,    
       [Measures].[Warehouse Cost])'


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

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