Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 24, 2003

MDX Essentials: MDX Members: Introducing Members and Member - Page 3

By William Pearson


We can reinforce our understanding of how the .Members function operates by constructing expressions that call it into action. We will use the MDX Sample Application (see the second article in our series, Structure of the MDX Data Model, for more information about the Sample Application) to construct and execute our expressions, and to see the results 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 HR cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that shown below, complete with the information from the HR cube displaying in the Metadata tree (in the left section of the Metadata pane, between the Query pane at the top of the application window, and the Results pane, at the bottom.).

Illustration 2: The MDX Sample Application Window (Compressed)

We will begin with a simple illustration that involves the .Member operator: Let's say that an information consumer in the Human Resources department wants to know total organizational salaries for 1997 and 1998 (the two years captured in our corporate HR cube - and therefore the full "membership" of the Year level of the Time dimension).

5.             Type the following simple query into the Query pane:

-- MDX04-1:  Tutorial Query No. 1


{ [Time].[Year].Members } ON COLUMNS,

{ [Measures].[Org Salary]} ON ROWS


6.             Click Query on the top menu, and then select Run.

We see the results below, which appear in the Results pane as soon as Analysis Services fills the cells that it determines to be specified by the query.

Illustration 3: The Query Result Dataset

Note: For an explanation of the comments line, which appears atop the query, together with a general discussion regarding attributes of the basic MDX query structure, see the first article of the series. For a discussion of the operators involved, here and going forward, see the first three articles of the series.

The query delivers the results that we requested.

7.             If it is desirable, save the query by selecting File ` Save As, and call the file something meaningful.

Now let's delve a bit deeper, and undertake an exercise to illustrate the use of the .Members function in varying levels of the same hierarchy.

8.             Type the following query into the Query pane:

-- MDX04-2:  Tutorial Query No. 2


   { [Measures].[Units Shipped], [Measures].[Units Ordered] } ON COLUMNS,

[Store].Members ON ROWS

FROM [Warehouse]   -- Remember to switch to the Warehouse cube!

9.             Click Query on the top menu, and then select Run.

The Results pane appears as partially shown below.

Illustration 4: The Query Results

The result dataset contains many empty members, intermingled with populated members, as we can see by scrolling down. Empty cells occur in MDX statements when data for the intersection of multiple dimensions (in our example, the intersection of the Units Shipped and Units Ordered measures, and the Store dimension) does not exist. To make our points with the .Members operator a bit clearer to see, let's issue instructions in our query to eliminate the empties, by adding the NON EMPTY keyword as follows:

10.         Type in the NON EMPTY keyword just before the following line of the query above:

[Store].Members ON ROWS

The Query pane of the Sample Application appears as shown in Illustration 5.

Illustration 5: The Query Pane with the Added NON EMPTY Keyword

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