In this Level, I will introduce the concept of members, within the context of Analysis Services.  We’ll preview the member functions as a general group (to be explored individually in later levels), and then look specifically at the .Members function,  within MDX. We’ll focus on the composition and nature of these important components of the language, and obtain hands-on exposure to their use in simple expressions that we will run to view their output.  I’ll emphasize rules of syntax as we move along: the knowledge we gradually build of these rules will provide a basis for progressively more sophisticated query building as we continue through the Stairway to MDX series.

Let’s begin by discussing members in general, and by performing an overview of member functions as a group.

Members and the Member Functions

As many of us are aware, members represent an important and pervasive concept within a practical understanding of MDX.  A member is, simply, an item in a dimension; members compose the values of the attributes that belong to a dimension.  (Keep in mind that measures are themselves members of a dimension - the “measures” dimension).  To illustrate, for a dimension based upon geography, which might contain Country, State and City as levels, USA, Idaho, and New Orleans might represent, respectively, valid members.

MDX contains a set of functions, known as member functions, each of which enables us to perform operations upon any member of a dimension.  Member functions return a member or members, where they exist.  A simple illustration that follows our geography example above would be as follows:  The .Parent function, applied to the member New Orleans  - as in New Orleans.Parent – would equal Louisiana.

As we’ll discover, member functions allow us to perform operations, based upon a member’s relative position, either up (“above” the member upon which the function is being performed) or down (“below” the member upon which the function is being performed) a dimensional hierarchy.  We’ll see numerous examples of this in the coming Member Functions discussions, in this and other levels of the series, that focus upon “family” member functions.

For now, let’s look at the details surrounding the .Members function.

The .Members Function

The .Members function provides a ready means of obtaining the “membership” for a given level, hierarchy or dimension (unless the dimension under consideration contains more than one hierarchy, in which case a hierarchy must be explicitly specified).  The importance of the .Members function becomes clear when we get enough exposure to MDX under our belts to realize that this is a very common point from which we conduct more involved operations.

A simple illustration of the .Members function in action follows:

  SELECT
  [Measures].MEMBERS ON COLUMNS, 
  [Product].[Product Categories].MEMBERS ON  ROWS
  FROM
  [Adventure Works]

Here’s a partial view of the result dataset:

Illustration 1:  Example Result Dataset (Partial View) from Using the .MEMBERS Function

As we can see, the .Members function produces a result dataset containing:

• All members of the Measures dimension (as columns);
• All members of the Product dimension – Product Categories hierarchy (as rows).

It is important to remember that the .Members function must be applied at a level within a dimension where hierarchy (and thus the “membership” we are requesting) is not ambiguous.  If multiple hierarchies exist within a dimension, we must apply the .Members function at or below the level of the “split,” in a manner of speaking; that is, if we attempt an operation similar to that which we performed above, and multiple hierarchies exist within the dimension selected (this can occur in a Time / Date dimension, for example, where fiscal and calendar hierarchies often share the same dimension), the “membership” we are requesting is not precise, and our attempt will end with an error.

Let’s examine the syntax.  The .Members function is appended to the right of the level, hierarchy or dimension, as in the following illustration (from the earlier example):

 
  [Product].[Product Categories].MEMBERS

It might also have been appended in the same manner to enumerate the members of the Categorylevel within the same Productdimension – Product Categories hierarchy, as in the following example:

 [Product].[Product  Categories].[Category].MEMBERS

We’ll illustrate the use of the .Members function at various levels in the Practice section that follows.

NOTE:  While we have yet to introduce Calculated Members to any real extent within the series, it is important to be aware that Calculated Members will not appear in the result dataset returned by the .Members function.  As we’ll discover in later levels of the Stairway to MDX series, the .AllMembers function, among other means, exist to include Calculated Members in our result datasets.

Let’s get some hands-on practice with the .Members function, keeping an eye on the results we get with each query we construct. We will be working with the MDX Query Editor in SSMS.  If you are not sure how to set up SSMS in Analysis Services to access the Adventure Works DW 2008R2 database, then click here for guidance.

For purposes of our practice session, let’s assume that we have been given a business requirement by a hypothetical client.  We are asked by an information consumer in the Sales and Marketing department of the Adventure Works organization to provide the total Internet Order Quantity, together with Internet Sales Amount, for each of the various Product Categories sold by the organization, over Calendar Year 2008.  We are asked, moreover, to provide the information in a two-dimensional grid, with the measures (Internet Order Quantity and Internet Sales Amount) in the column headings and all products (the “full membership” of Product Category) on the rows.

• Type (or copy and paste) the following query into the Query pane:

--  SMDX005-001:  Basic Use of .MEMBERS
SELECT
  { [Measures].[Internet Order Quantity],
  [Measures].[Internet Sales Amount] } ON  AXIS(0),
  { [Product].[Product Categories].[Category].MEMBERS  } ON AXIS(1)
FROM
  [Adventure Works]
WHERE
  ([Date].[Calendar Year].[CY 2008])

The Query pane appears, with our input.

Illustration 2:  The Query in the Query Pane

• Select Query on the top menu, and then select Execute (!).

Illustration 3:  Executing the Query from the Main Menu...

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

Illustration 4:  The Initial Query Results

NOTE:  For an explanation of the comment line, together with other general discussion surrounding attributes of the basic MDX query structure, see  my charter article, Stairway to MDX - Level 1: Getting Started with MDX.

The query delivers the results that were requested by the information consumer.

• Select File -- Save MDXQuery1.mdx As …, name the file SMDX005-001, and place it in a meaningful location.

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.  This time, let’s say that we are asked by the Adventure Works Sales and Marketing department to provide the total Reseller Order Quantity, together with total Reseller Sales Amount, for the organization, over the years of operation represented by our cube.  The ultimate use for the information, they say, will be to support a simple drilldown report, but, for the time being, the consumers want to understand how to break down the values for all members of the Calendar hierarchy of the Date dimension.

• Select File --> New from the top menu.
• Select Query with Current Connection from the cascading menu that appears next.
• Type (or copy and paste) the following query into the Query pane:

--  SMDX005-002:  Another Basic Use of  .MEMBERS
SELECT 
  { [Measures].[Reseller Order Quantity],
  [Measures].[Reseller Sales Amount] } ON AXIS(0),
  { [Date].[Calendar].MEMBERS } ON AXIS(1)
FROM 
  [Adventure Works]
  The query appears within the Query pane.

Illustration 5:  The Query in the Query Pane

• Click the Execute (!) button in the toolbar atop the Management Studio.

The Results pane appears as partially shown.

Illustration 6:  The Query Results Dataset (Partial View)

The results 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 Reseller Order Quantity and the Reseller Sales Amount, measures, and the Date dimension) does not exist. To make our points with the .Members function a bit clearer to see, let’s issue instructions in our query to eliminate the empties, by adding the NON EMPTY keyword as follows:

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

{  [Date].[Calendar].MEMBERS } ON ROWS

The query appears, with the added keyword circled, in the Query pane as depicted here.

Illustration 7:  The Query Pane with the Added NON EMPTY Keyword (Circled)

We’re using the NON EMPTY keyword simply to issue instructions to Analysis Services:  exclude Date dimension (Calendar hierarchy) members in the rows where totals for Reseller Order Quantity and Reseller Sales Amount do not exist. The empty tuples will thereby be screened out of the results dataset of the MDX query.

• Click the Execute (!) button in the toolbar atop the Management Studio, once again.

The new Results pane appears, as partially shown.

Illustration 8:  The Query Results (Partial View) – “Empties” Removed

• Save the query by selecting File --> Save MDXQuery2.mdx As …, naming the file SMDX005-002.mdx, and placing it in a convenient location.

Now, to continue with the focus of our lesson, the .Members function, we can see in the example we have created that the use of .Members in the above query gives us every member of the Date dimension – Calendar hierarchy, including levels and the members for each of those levels.  This result might tend to confuse more than help an information consumer, because total numbers would not necessarily agree with a total of the values appearing in the results dataset for either measure.  In short, a “dump” of the entire list of hierarchy objects with their associated values might not serve to be useful from an analysis perspective to an uninformed consumer.

Let’s explore going to a specific level in the Calendar hierarchy for the Date dimension.

• Select File --> New from the top menu, once again.
• Select Query with Current Connection from the cascading menu that appears next, as we did earlier.
• Type  (or copy and paste) the following query into the Query pane:

--  SMDX005-003:  Using .MEMBERS at a  Hierarchy Level
SELECT   
  { [Measures].[Reseller Order Quantity],  
  [Measures].[Reseller Sales Amount] } ON  AXIS(0),   
  NON EMPTY { [Date].[Calendar].[Calendar  Year].MEMBERS } ON AXIS(1)
FROM 
  [Adventure Works]

The query appears within the Query pane.

Illustration 9:  The Query in the Query Pane

We are now using the .Member function to ask for the “membership” of the Calendar Year level (of the Date dimension – Calendar hierarchy) in the row axis.

• Click the Execute (!) button in the toolbar, once again.

The Results pane appears.

Illustration 10:  The Query Results – Members of the Calendar Year Level Appear

We see that we have now obtained a summary, by Calendar Year, of the two measures – and by Calendar Year only – because we made the more specific request for members of the Calendar Year level.

• Save the query as SMDX005-001.mdx.

We can easily replicate the effects we have obtained at the Calendar Semester, Calendar Quarter, and other levels of the Date dimension and its associated hierarchies, as well as in the other dimensions and their levels.  As we progress into the multi-article Member Functionssegment of our series, and the coverage of many of the member functions available in MDX, as well as into even more advanced stages of query building, we will revisit the .Members function often.  Practice with this powerful function will make its use easy and natural; we will come to value it highly in the more advanced MDX exercises we undertake in later levels.

• Select File -- Exit to leave the SQL Server Management Studio, when ready.

Summary

In this level, we introduced the concept of members in Analysis Services, and then moved into an exploration of the .Members function, as well as setting the scene for the examination of the general member functions that we will undertake in the next levels of the series. We focused  upon the composition of these important components of the MDX language in general, and provided hands-on exposure to the .Members function in simple expressions we crafted together, and for each of which we discussed the results delivered.

I emphasized throughout the level the importance of understanding the member concepts and the use of the member functions. A thorough grasp of these important MDX components is essential for developing increasingly more sophisticated queries as we continue through the Stairway to MDX series.