MDX Member Functions: The "Family" Functions

Monday Mar 24th 2003 by William Pearson
Share:

Author Bill Pearson continues his MDX Essentials Series with an introduction to the Member "Family" Functions. In this lesson, we discuss the concept of the "family" functions, and how they allow our queries to navigate cube structures. We then begin our exploration with the .Parent, .Children and Ancestor() functions.

About the Series ...

This is the fifth article of the series, MDX Essentials. The primary focus of this series is an introduction to the MDX language. 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: Beginning with this lesson, 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 fourth article of the series, MDX Members: Introducing Members and Member Functions, we introduced the concept of members, and discussed their pervasive significance within MDX. In an article that served to launch the series' first discreet group of articles, Member Functions, we began our exploration of the powerful .Members operator. Our exposure of this robust and highly useful operator included a discussion of the syntax within which we can best employ it; we then illustrated some of its uses within MDX expressions through practice exercises.

In this lesson, we continue 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 will expose the .Parent, .Children and Ancestor() functions, discussing the information they return, together with syntactical points surrounding their use. We will illustrate further how to take advantage of these useful functions by performing practice exercises, and commenting on the result datasets we obtain.

Introduction to the "Family" Member Functions

In the first lesson of the Member Functions segment of our series, we will take some time to "get to know the family." I like to call one specific subset of the member functions and operators the "family" functions, because many of their names resemble those of family members. The functions that compose this group include:

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

The "family" metaphor is appropriate, because these functions perform operations on dimensions, hierarchies, and levels in a manner that mirrors movement about a "family tree." There are other "family" functions that return sets, but we will focus primarily on the member functions in this segment of the series.

We will discuss some of the concepts that underlie the "family" functions, and discuss the appropriate syntax involved in using each effectively. Next, we will provide hands-on exposure to the use of each in simple expressions / queries that we will subsequently run to view the output and reinforce our understanding of the function / component from the perspective of the result dataset that it returns.

This lesson will include:

  • Further general discussion surrounding members and member functions and their roles in MDX, when relevant to the component under consideration.
  • An introduction to the .Parent, .Children and Ancestor() 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.
  • An introduction to the WHERE clause, and the specification of slicer dimensions.
  • A look ahead to the additional member "family" functions that we will explore in forthcoming articles.

As we mentioned in Lesson 4, 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. A zero is returned if we attempt to go beyond the boundaries of a defined hierarchy an example of this might be trying to apply the .Children function to a member at the bottom / leaf level of the hierarchy (which is childless, in this context). Like most member functions, the "family" functions can be quite valuable in helping us to simplify our MDX queries and make them more compact.

Working with the Member "Family" Functions

The "family" functions, like other member functions, belong to two general groups, from the perspective of the result datasets they return. One group works within a "vertical" scope, traveling up or down between hierarchical levels, as we will see in the respective Practice section for each function. Examples include the following functions:

  • Ancestor()
  • .Children
  • .Parent
  • .FirstChild
  • .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 include:

  • .Cousin()
  • .FirstSibling
  • .LastSibling

As we have intimated, the capability to perform operations within the vertical and horizontal scopes of the hierarchy can mean more efficient, simpler MDX queries. We will take a look at the "family" functions individually to obtain a good understanding of their workings in the sections devoted to each that follow.

The .Parent Function
Discussion:

As we will see, the .Parent function returns the parent of a specified member using the syntax we describe below. The function is especially useful in calculated members, which we will explore later in the series.

Syntax

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

<member>.Parent

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

SELECT

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

{[Booker].Parent} ON ROWS

FROM [Warehouse]

WHERE ([Measures].[Units Shipped])

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


Illustration 1: Example Result Dataset from Using the .Parent Function

Perhaps a look at the hierarchy for the source member (Booker) for the .Parent function above will make the illustration more meaningful. In Illustration 2 below, we see that Booker is a member of the Milk sublevel of the Drinks level, within the Product Family hierarchy of the Product dimension.


Illustration 2: The Hierarchy Containing the Milk Level, Parent to the Booker Member

In the simple example above, we can easily see that the parent member is Milk. We will see far more clearly the significance of the .Parent function at a later point, when we are not applying the function to a specific source member, but are using a relative member, such as .CurrentMember, where the calculation within which we find it determines its context.

Another item that we have introduced in our illustration is the WHERE clause. The WHERE clause is optional, and determines the member or dimension to be used as the Slicer Dimension. This specification limits the data returned to specific dimension(s) or member(s). Our illustration uses a WHERE clause to limit the data extracted for the axis dimensions to a specific member of the Measures dimension, Units Shipped. Our use of the WHERE clause will take many forms as we progress through the series, and we will address more elaborate uses as they arise.

Practice

Let's reinforce our understanding of how the .Parent function operates by constructing an expression that calls it into action. We will use the same core expression throughout the lesson, to explore the different results we obtain in a way that they can be contrasted against each other.

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 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 .Parent function: Let's begin with a basic query to extract total Frozen Foods Units Shipped for 1997 and 1998 (the only years in our cube).

5.             Type the following query into the Query pane:

-- MDX05-1:  Tutorial Query No. 1
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods]} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

6.             Click the Run button (the green "arrowhead" button) 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 specified by the query.


Illustration 3: The Query Result Dataset

The query delivers the results that we requested; we see the totals for the Frozen Foods level of the Products hierarchy.

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

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


Illustration 4: The Hierarchy under Consideration

8.             Type the following query into the Query pane:

-- MDX05-2:  Tutorial Query No. 2
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods].Parent} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

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

The Results pane appears as shown below.


Illustration 5: The Query Results

The result dataset displays the totals at the Food level, which is somewhat obviously the Parent of the Frozen Foods level (the source member upon which the .Parent function is enacted).

We see that we have now obtained a summary at the Food level for the Units Shipped for the years 1997 and 1998, because we affixed the .Parent function to the Frozen Foods level. As we have stated, we will find the .Parent function far more powerful at a later juncture in our series, when we are using a relative member, such as .CurrentMember, where the calculation within which .Parent is placed will determine its context.

The .Children Function

Much like the .Parent function, the .Children function works within a "vertical" scope, moving, as we noted in the introductory section of this lesson, down between hierarchical levels from the member to which the function is applied. As its name implies, the .Children function returns the children of the source member, using the syntax we describe below. Again resembling the .Parent function, the .Children function is especially useful in calculated members, among other scenarios, some of which we will explore later in the series.

Syntax

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

<member>.Children

A simple illustration of the .Children function, using our first example above and replacing the .Parent function with the .Children function, follows:

 SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

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


Illustration 6: Example Result Dataset from Using the .Children Function

Referring again to the hierarchical structure within which our source member lies, we can see that the .Children function has moved in the opposite direction to the .Parent function above, as depicted in the illustration below:


Illustration 7: Downward Movement along the Hierarchy under Consideration

Practice

Let's reinforce our understanding of how the .Children function operates by using it in a query. We will create a query very similar to the one we used above for the .Parent function to accomplish our ends.

We will return to the MDX Sample Application to craft our query. We'll begin with a basic query to return a set of members one level below Frozen Foods to determine the Units Shipped for 1997 and 1998, much as we did in our first practice exercise for the .Parent function above.

10.         Type the following query into the Query pane:

-- MDX05-3:  Tutorial Query No. 3
SELECT
{[Time].[Year].members} ON COLUMNS,
{[Booker].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

11.         Click the Run button 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 specified by the query.


Illustration 8: The Query Result Dataset

The query delivers the results that we requested: The children members for Booker appear, together with the Units Shipped, where applicable.

Now let's illustrate the operation of the .Children function once again, with another example.

12.         Type the following query into the Query pane:

-- MDX05-4:  Tutorial Query No. 4
SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped], [Warehouse].[USA])

In addition to using the .Children function with another source member, we are also expanding the WHERE statement (see above for a discussion) to expand, in turn, the slicer dimension to specify not only the Units Shipped measure, but also the USA warehouses. The cube is therefore filtered, or "sliced" for the Units Shipped and USA members, within the context in which they are specified.

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

The Results pane appears as shown below.


Illustration 9: The Query Results

The result dataset displays the children of the Frozen Foods level, enumerating them along with the Units Shipped data as appropriate.

We see that we have now obtained a set of members on a level below Frozen Foods for Units Shipped, because we affixed the .Children function to the Frozen Foods level. As is the case with the .Parent function, we will find the .Children function to be a powerful tool at a later juncture in our series, when we are using a relative member, such as .CurrentMember, in combination with the function.

The Ancestor() Function

The Ancestor() function retrieves the member, at the specified level, or at the specified distance, that is the ancestor (the parent, parent of parent, or higher) of the source member under consideration. Like the .Parent and .Children functions, it travels within a "vertical" scope, moving up between hierarchical levels from the member to which it is applied.

A difference exists between the functions, however, in that the Ancestor() function can contain the aforementioned level, or distance, component, as we will discuss in the Syntax section below.

Syntax

The Ancestor() function is applied differently than the .Parent and .Children functions, in that it is not "appended" to the source member. The source member is placed within the parentheses to the right of the word Ancestor, as shown in the following illustration:

Ancestor(member, level)

Or

Ancestor(member, distance)

An illustration of the Ancestor() function (using the level target) follows:

SELECT
{(Measures.[Warehouse Sales]), (Measures.[Warehouse Cost])} ON COLUMNS,
{(Ancestor([Time].[Year].[1997].[Q2].[4], [Time].[Quarter]))} ON ROWS
FROM [Warehouse]

The result dataset returned would appear as shown in the illustration below:


Illustration 10: The Results of the Ancestor() Function in a Query

In the illustration above, a target level that equaled the level of the member within the function would have returned the member itself.

If a distance target is specified, the distance component is represented by a number. The number represents "number of hierarchical steps above the source member." The member itself is returned if the number is zero, reasonably enough.

Practice

Let's reinforce our understanding of how the Ancestor() function operates by using it in a couple of queries. We will use the second of the syntax structures (Ancestor(member, distance) in our first example.

We will return to the MDX Sample Application, and create a query to return the ancestor member two levels above the source member that we used in the Syntax illustration above. We will be specifying the Units Shipped measure as the slicer dimension in this example.

14.         Type the following query into the Query pane:

-- MDX05-5:  Tutorial Query No. 5
SELECT
{(Measures.[Warehouse Sales]), (Measures.[Warehouse Cost])} ON COLUMNS,
{(Ancestor([Time].[Year].[1997].[Q2].[4], 2))} ON ROWS
FROM [Warehouse]

15.         Click the Run button to execute the query.

We see the result dataset below, which appears in the Results pane as soon as Analysis Services fills the cells specified by the query.


Illustration 11: The Query Result Dataset

The query delivers the results that we requested: The target member, specified as "two steps above the source member," is Year (two steps above Month in the Time dimension hierarchy), together with the respective total Units Shipped.

Now let's illustrate the operation of the Ancestor() function once again, with another example.

16.         Type the following into the Query pane:

-- MDX05-6:  Tutorial Query No. 6
SELECT
{(Measures.[Units Shipped]), (Measures.[Units Ordered])} ON COLUMNS,
{(Ancestor([Store].[Store Country].[USA].[OR].[Portland], 9
 [Store].[Store Country]))} ON ROWS
FROM [Warehouse]

Here, we're specifying two unit measures in our columns, and don't, at least in this case, require the slicer specification (WHERE). We are also using the Ancestor() function, this time with the level target, versus the distance target we used in the first practice exercise. Because we specify the Store Country level, with Portland as the source member, we would expect to see USA in the result dataset. We will not be disappointed in our expectations.

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

The Results pane appears as shown below.


Illustration 12: The Query Results

The result dataset displays the USA member, along with the corresponding Units totals.

We see that we can obtain the targeted ancestor member with two different approaches. As will be the case with the .Parent and .Children functions that we have explored earlier, we will find the Ancestor() function to be a powerful tool later when we call upon it for use with relative members.

Next in Our Series ...

In this lesson, MDX Member Functions: The "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 explored the .Parent, .Children and Ancestor() functions, discussing the information they return, together with syntactical points surrounding their use. We illustrated further how to take advantage of these useful functions by performing practice exercises, and commenting on the result datasets we obtained. As a part of the lesson, we also introduced the WHERE clause, within the context of specifying a slicer dimension for some of our queries.

In our next lesson, MDX Member Functions: More "Family" Functions, we will expose the .FirstChild, .LastChild, .FirstSibling and .LastSibling member functions. We will discuss each function, illustrate the appropriate syntax required for its use, and test our understanding of how to take advantage of each of these useful functions by performing practice exercises, and commenting on the result datasets we obtain.

» 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