MDX Essentials: Member Functions: More "Family" Functions

Monday Apr 21st 2003 by William Pearson
Share:

Author Bill Pearson continues his MDX Essentials Series with an introduction to more Member "Family" Functions. In this lesson, we will continue our exploration of the "Family" functions with the .FirstChild, .LastChild, .FirstSibling and .LastSibling functions.

About the Series ...

This is the sixth 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 fifth article of the series, MDX Member Functions: The "Family" Functions, we exposed three member functions that belong to a group to which I often refer as the "family" functions: .Parent, .Children and Ancestor(). We discussed the information that these functions return, and covered syntactical points surrounding their use. We illustrated how to take advantage of these useful functions by performing practice exercises, and then commented on the result datasets we obtained.

Our last lesson served as the second session within our Member Functions group of articles. In this lesson, we continue our examination of the member functions and operators, one at a time, contrasting the uses and characteristics of each. Specifically, we will expose the .FirstChild, .LastChild, .FirstSibling and .LastSibling functions, considering their output and the syntax applicable to some of their uses. We will practice putting these valuable functions to work by undertaking practice exercises, and then we will examine the datasets that are returned in each case.

Introducing More "Family" Member Functions

We took a moment to "get to know the family" (in my meaning of the term "family functions"), stating that the functions and operators that compose this group (for the purposes of this set of articles) include:

  • .Parent
  • .Children
  • Ancestor()
  • Cousin()
  • .FirstChild
  • .LastChild
  • .FirstSibling
  • .LastSibling

We mentioned that the "family" metaphor is appropriate to this group, because these functions perform operations on dimensions, hierarchies, and levels in a manner that simulates movement about a "family tree." As we stated in our last article, our focus in this segment of the series is primarily the member functions, although other "family"-like functions are available that return sets.

This lesson will include:

  • An introduction to the .FirstChild, .LastChild, .FirstSibling and .LastSibling functions, with
    • an examination of the syntax that surrounds each function's use;
    • an illustrative example of its use in a practice exercise;
    • a brief discussion of the MDX results we obtain in the practice example.
  • Further examination of the WHERE clause, and its specification of slicer dimensions;
  • Illustrations of the use of combinations of the "family" functions;
  • An examination of the use of the NON EMPTY keywords to suppress empty intersects from our result sets;

As we discussed in Lesson 5, many member functions, particularly those of the "family" group, 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. Like most member functions, the "family" functions can be quite valuable in helping us to make our MDX queries simpler and more compact.

Working with the Member "Family" Functions

We discussed two general groups (from the perspective of the result datasets they return) to which the members of the "family" functions belong in our last lesson. One group works within a "vertical" scope, traveling up or down between hierarchical levels, as we will see in the Practice section for each respective function. The functions in this article (like the Ancestor(), .Children and .Parent functions of our last lesson) that act "vertically" include:

  • .FirstChild, and
  • .LastChild.

The second general group of "family" functions operates within a "horizontal" scope of the hierarchy involved. These functions travel within the same level of the hierarchy ("across" versus "up and down"), and, within the context of this article, include:

  • .FirstSibling, and
  • .LastSibling.

As we have already determined, the capability to perform operations within the vertical and horizontal scopes of the hierarchy can mean more efficient, simpler MDX queries. We'll take a look at our current selection of "family" functions individually, to obtain an appreciation for their capabilities, in the sections devoted to each that follow.

The .FirstChild Function

Discussion:

As we will observe, the .FirstChild function returns the first child of a specified member, using the syntax we describe below. An illustration of this concept might be helpful in getting an understanding of just what we mean by a "first child," as well as to provide a visual platform upon which to understand the other "family" functions that we will explore in this lesson.

I chose the Time dimension for this illustration because we all understand the time hierarchy, perhaps making a grasp of the core concepts less subject to being fettered by a need to study the hierarchical setup of a sample database. The illustration to which I refer appears below.


Illustration 1: Time Dimension Hierarchy, Showing Year 1997 with Expanded Q4

 

Syntax

The .FirstChild function is appended to the right of the member, as shown in the following illustration:

<member>.FirstChild

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

SELECT
{[Time].[Year].[1997].FirstChild} ON COLUMNS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

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


Illustration 2: Example Result Dataset from Using the .FirstChild Function

A look at the Time hierarchy for the source member (1997) within the .FirstChild function above will make the illustration more meaningful. In Illustration 3 below, we see that Q1 is a member of the Quarters sublevel of the Year level, within the Time dimension. The order of the members within the database determines the status of Q1 as "first;" Q1 is the child of the Year level 1997, and as it is "first in line," it is returned.

Illustration 3 below displays the foregoing graphically.


Illustration 3: [1997].FirstChild is Q1

In the simple example above, we can easily see that the parent member is 1997.

We introduced the WHERE clause in our last lesson, in a discussion of the .Parent function. As we discussed at that point, the WHERE clause is optional; it specifies the Slicer Dimension, and limits the data returned to specific dimension(s) or member(s). The WHERE clause in our illustration above restricts the data extracted for the axis dimensions to a specific member of the Measures dimension, Warehouse Sales.

Practice

Let's construct an expression that calls the .FirstChild function into action to reinforce our understanding of how it operates. As we have done in previous lessons, we will use similar core expressions throughout sections of this article, to explore the different results we obtain in a way that they can be contrasted against each other.

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 be our tool for constructing and executing our expressions, and will allow us to view 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 involves the .FirstChild function: a basic query designed to extract total Warehouse Sales for each member of the Product Departments Level of the Product dimension for Q1 of 1997.

5. Type the following query into the Query pane:

-- MDX06-1:  Tutorial Query No. 1
SELECT
{[Time].[Year].[1997].FirstChild} ON COLUMNS,
{[Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

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 below, which appears in the Results pane as soon as Analysis Services fills the cells that the query specifies.


Illustration 4: The Query Result Dataset

The query delivers the results that we requested; we see the totals for the enumerated members of the Product Department hierarchy.

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.

As to the use of the .Members operator, recall from Lesson 4 of this series that the .Members operator gives us the members of the dimension.

Now let's illustrate the operation of the .FirstChild function. A quick look at the hierarchy illustrates the relationships between the members and levels under consideration, as shown below:


Illustration 5: The Hierarchy under Consideration

As our straightforward example makes quite obvious, the first child member of level member 1997 is Q1.

Now let's try another query using .FirstChild. This time, we will write a basic query to return total Warehouse Sales by Product Department, by month for each of the children of the member Q1. Recall our discussion surrounding the .Children function in Lesson 5. From its use we will enumerate the months contained within Q1, which we saw earlier was the first child of member 1997 of the time dimension. Our objective will be to create a column for each month in Q1 and a row for each product department.

Type the following query into the Query pane:

-- MDX06-2:  Tutorial Query No. 2
SELECT
{[Time].[Year].[1997].FirstChild.Children} ON COLUMNS,
{[Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

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

The Results pane appears as shown in Illustration 6.


Illustration 6: The Query Results

The query delivers the totals for the enumerated members of the Product Department level of the Product dimension, for each of the children (that is, 1, 2, and 3) of the first child (Q1)of the 1997 member of the year level of the Time dimension. The months are expressed as numbers, as the cube was designed in this way. We could easily convert the numbers to the string names, but we'll leave that for another lesson.

We see that we have obtained a summary for each of the Product Department member's Warehouse Sales, for each month of the first quarter of 1997, because we affixed the .FirstChild function to the 1997 year level of the Time dimension, and then appended the .Children function to the .FirstChild function. As we determined was the case in our exposure of the .Parent function of Lesson 5, as well as elsewhere, we will find the .FirstChild and other "family" functions even more powerful at a later juncture in our series, when we are using a relative member, such as .CurrentMember, where the calculation within which the respective family function is placed will determine its context.

9. Leave the Sample Application open throughout the next sections.

The .LastChild Function

Similar in operation to the .FirstChild function above, only working from the "opposite end" of the range of the children of a specified member, the .LastChild function returns the last child of the specified member using the syntax we describe below. To illustrate our meaning of the term "last child," we will return to the visual representation we constructed for .FirstChild to view the impact of the .LastChild function, working within the Time dimension once again for a readily understandable hierarchy.

Illustration 7 below depicts the relationships between the members and levels under consideration:


Illustration 7: Revisiting the Time Dimension Hierarchy to Display .LastChild

Syntax

The .LastChild function is appended to the right of the member, as shown in the following illustration:

<member>.LastChild

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

SELECT
{[Time].[Year].[1997].LastChild} ON COLUMNS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

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


Illustration 8: Example Result Dataset from Using the .LastChild Function

Practice

We will construct an expression that calls the .LastChild function into action to reinforce our grasp of its operation. We will begin with a simple illustration that involves the .LastChild function: a basic query designed to extract total Warehouse Sales for each member of the Product Family level of the Product dimension for Q1 of 1997.

1. Type the following query into the Query pane:

-- MDX06-3:  Tutorial Query No. 3
SELECT
{[Time].[Year].[1997].LastChild} ON COLUMNS,
{[Product].[Product Family].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

2. Click the Run button to execute the query.

We see the dataset pictured below appear in the Results pane.


Illustration 9: The Query Result Dataset

The query delivers the results that we requested; we see the totals for the enumerated members of the Product Family level of the Product hierarchy.

As our example illustrates above, the last child of level member 1997 is Q4.

Next, we will write a basic query to return total Warehouse Sales for the Non-Consumables children of the Product Family level, by month (and thus for each of the children of the member Q4). Using the .Children function again, we will enumerate the months contained within Q4, which we determined, in the query immediately above, to be the last child of member 1997 of the Time dimension. The intent will be to create a column for each month in Q4, and a row for each child of the Non-Consumables level of the Product dimension.

Type the following query into the Query pane:

-- MDX06-4:  Tutorial Query No. 4
SELECT
{[Time].[Year].[1997].LastChild.Children} ON COLUMNS,
{[Product].[Product Family].[Non-Consumable].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

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

The Results pane appears as shown in Illustration 10.


Illustration 10: The Query Results

The query delivers the totals for the enumerated children of the Non-Consumable level of the Product dimension, as before, by Q4 month (that is, 10, 11, and 12), the children of the Q4 level of the Time dimension.

We see that we have obtained summaries at the level of the children of the Non-Consumable level for the Warehouse Sales for each month of the last / fourth quarter of 1997, because we affixed the .LastChild function to the 1997 year level of the time dimension, and then suffixed .LastChild with the .Children function.

The .FirstSibling Function

The .FirstSibling function is a bit more confusing than .FirstChild to some who are new to MDX, mainly in the way that it works for the first child of a given member-parent, but it may help again to relate the concept to a "family" scenario. As we discussed in the introductory section for this article, the .FirstSibling (as well as the .LastSibling) function operates within a "horizontal" scope for the hierarchy involved. It effectively "travels" within the same level of the hierarchy ("across" versus "up and down," as it does in the case of the .FirstChild and .LastChild functions.

Illustration 11 below depicts the relationships between the members and levels under consideration, as shown below:


Illustration 11: Revisiting the Time Dimension Hierarchy to Display .FirstSibling

The possibly confusing characteristic lies the fact that the first child (Q1 in the example illustrated above) also fills the role of first sibling - it is "first" within the horizontal level, and is the first sibling of any member residing at the same level. This is an ordering concept, and becomes straightforward once the meaning is clear. To amplify the concept with another illustration, let's examine the relationships when we consider the first sibling of Q3, as shown in Illustration 12 below.


Illustration 12: First Sibling for Q3 is Q1

We see in the illustration above that the first sibling of Q3 is Q1 - the first member in the level, plain and simple.

Syntax

The .FirstSibling function is affixed to the right of the member, as in the following illustration:

<member>.FirstSibling

A basic example of the .FirstSibling function in action follows:

SELECT
{[Time].[Year].[1997].[Q2].FirstSibling} ON COLUMNS
FROM [Warehouse]
WHERE [Measures].[Units Ordered]

The dataset returned would appear as shown in Illustration 13 below:


Illustration 13: Result Dataset from Using the Example .FirstSibling Function

Practice

Let's confirm our understanding of the .FirstSibling function with a quick query. We will create a rudimentary query designed to return total Units Shipped for each member of the Beverages Product Department Level of the Product dimension for Q1 of 1997.

4. Type the following query into the Query pane:

-- MDX06-5:  Tutorial Query No. 5
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling} ON COLUMNS,
{[Product].[Product Department].[Beverages].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

5. Select the F5 key (an alternative way to run the query).

The query executes, and the following result dataset appears in the Results pane.


Illustration 14: The Query Result Dataset

The query delivers the results that we requested; we see the Q1 (the first sibling of specified member Q3) totals for the enumerated children of the Beverages level, within the Product Family level of the Product hierarchy.

Let's write another query to return total Units Shipped for the Canned Foods Product Department, by month, for Q1, using what we have learned. Employing the .Children function again, we will enumerate the months contained within Q1, which we have determined, in the query immediately above, to be the first sibling of member Q3 (the first of the peer members in its horizontal level) of the Time dimension. To restate, our intent will be to create a column for each month in Q1 and a row for each child member of the Canned Foods Product Department level of the Product dimension.

6. Type the following query into the Query pane:

-- MDX06-6:  Tutorial Query No. 6
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling.Children} ON COLUMNS,
{[Product].[Product Department].[Canned Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

7. Press F5 to run the query.

The Results pane appears as shown in Illustration 15.


Illustration 15: The Query Results

The query delivers the totals for the enumerated children of the Canned Foods product department by Q1 month (that is, 1, 2, and 3), the children of the Q3 member's first sibling Q1.

The results shown in Illustration 15 present an opportunity to examine another nuance we can use to make our presentation of data perhaps more user-friendly to its ultimate audience. Blank spaces often confuse information consumers, so let's remove them with a minor adjustment to Query No. 6 above.

8. Add the keywords NON EMPTY to Query No. 6 as shown below:

-- MDX06-6M:  Tutorial Query No. 6 [MODIFIED TO REMOVE EMPTIES]
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling.Children} ON COLUMNS,
NON EMPTY {[Product].[Product Department].[Canned Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

9. Press F5 to execute the newly modified query.

The Results pane appears as shown in Illustration 16 below.


Illustration 16: The Query Results

The query delivers the totals for the enumerated children of the Canned Foods product department by Q1 month, as before, but with the empty result intersects removed. Using the NON EMPTY query in this fashion allows us to suppress the empties; any tuples on the affected axis that are associated with empty intersects are efficiently removed before the results are displayed.

The .LastSibling Function

The .LastSibling function resembles the .FirstSibling function in its capacity to induce confusion to those new to MDX: the way that it behaves with the last child of a given member-parent is the interesting part, and, here again, it may help to relate to a "family" scenario. Like .FirstSibling, the .LastSibling function operates within a "horizontal" scope from the perspective of the hierarchy to which it belongs. It behavior mimics "travel" within the same level of the hierarchy ("across," again unlike the .FirstChild and .LastChild functions that we discussed in earlier parts of the lesson).

Illustration 17 below depicts the relationships under consideration, as shown below:


Illustration 17: .LastSibling in the Now-Familiar Time Dimension Hierarchy

The possibly confusing characteristic lies in the fact that the last child (Q4 in the example illustrated above) also fills the role of last sibling - it is "last" within the horizontal level, and is the last sibling of any member residing at the same level.

To amplify the concept with another illustration, let's examine the relationships when we consider the last sibling of Q2, as shown in Illustration 18 below.


Illustration 18: Last Sibling for Q2 is Q4

We see in the illustration above that the last sibling of Q2 is also Q4 - the last member in the level - and the same concepts as those we discussed for .FirstSibling earlier, just a "different direction."

Syntax

The .LastSibling function, like the other functions we have examined in this lesson, is affixed to the right of the member, as in the following illustration:

<member>.LastSibling

A basic example of the .LastSibling function in action follows:

SELECT
{[Time].[Year].[1997].[Q2].LastSibling} ON COLUMNS
FROM [Warehouse]
WHERE [Measures].[Warehouse Cost]

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


Illustration 19: Result Dataset from Using the Example .LastSibling Function

Practice

A practice exercise will help us to confirm our grasp of the concepts surrounding .LastSibling. We will design a query to return total Warehouse Cost for each member of the Stores group that is located in the USA, for Q4 of 1997.

10. Type the following query into the Query pane:

-- MDX06-7:  Tutorial Query No. 7
SELECT
{[Time].[Year].[1997].[Q1].LastSibling} ON COLUMNS,
{[Store].[Store Country].[USA].Children} ON ROWS
FROM [Warehouse]
WHERE [Measures].[Warehouse Cost]

11. Select the F5 key to run the query.

The following result dataset appears in the Results pane, after query execution.


Illustration 20: The Query Result Dataset

As requested, we see the Q4 (the last sibling of specified member Q1) totals for the enumerated children of the USA level, within the Store Country level of the Store hierarchy.

To practice further, let's request total Warehouse Cost in the same context as above, but for the months of Q4, with a minor alteration to our query. We'll employ the .Children function again to enumerate the months contained within the fourth quarter, A.K.A. the last sibling of member Q2 - the last of the peer members in its horizontal level of the Time dimension. To reword the objective, we wish to create a column for each month in Q4 and a row item for each child (or state) of the USA level of the Store dimension.

12. Type the following query into the Query pane:

-- MDX06-8:  Tutorial Query No. 8
SELECT
{[Time].[Year].[1997].[Q1].LastSibling.Children} ON COLUMNS,
{[Store].[Store Country].[USA].Children} ON ROWS
FROM [Warehouse]
WHERE [Measures].[Warehouse Cost]

13. Click the Run Query button to execute the query.

The Results pane appears as shown in Illustration 21.


Illustration 21: The Query Results

The query delivers the totals for the enumerated children of the USA Store Country (and thus the States) by months 10, 11, and 12, the children of the fourth quarter, the last sibling of Time dimension member Q1.

 

Next in Our Series ...

In this lesson, MDX Member Functions: More "Family" Functions, we continued the multiple-article Member Functions segment that focuses upon the member functions (and operators, as appropriate) one at a time, contrasting the uses and effects of each. Specifically, we exposed the .FirstChild, .LastChild, .FirstSibling and .LastSibling functions, examining the output returned from each and syntax considerations involved in their use. We practiced putting these additional "family" functions to work through practice exercises, and then reviewed individually the datasets that were returned.

As a part of the lesson, we also examined the effects of using combinations of various "family" functions, and used the NON EMPTY keywords to remove empty intersects from our result sets.

In our next lesson, MDX Member Functions: the Cousin () Function, we will explore a slightly more challenging member function. We will discuss the Cousin() function in detail, illustrate the syntax that is appropriate for its effective use, and then test our understanding of how to leverage this remaining "family" function by undertaking relevant practice exercises. Finally, we will comment on the results we obtain using the Cousin() function, and discuss some of the limitations that are inherent in its use.

» 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