MDX Member Functions: The Cousin () Function

Monday May 19th 2003 by William Pearson
Share:

Author Bill Pearson concludes the Member 'Family' Functions group of articles with an examination of the Cousin () Function.

About the Series ...

This is the seventh 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 sixth article of the series, 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.

Our last lesson served as the third session within our Member Functions group of articles. In this lesson, we complete our examination of the member functions and operators, introducing and exploring the Cousin () Function. We will discuss the slightly more challenging 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.

Introduction

Beginning with the fifth article of the MDX Essentials series, Member Functions: The "Family" Functions, we began an examination of the "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 Article Five, our focus in this group of three articles is primarily a subset of the member functions, although other "family"-like functions are available that return sets.

This lesson will include an introduction to the .Cousin() function, with:

  • an examination of the syntax that surrounds its use;
  • an illustrative example of its use in a practice exercise;
  • a brief discussion of the MDX results we obtain in the practice example.

As we discussed in Lessons Five and Six, 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. In this sense, as we previously observed, there are two general groups (from the perspective of the result datasets they return) to which the members of the "family" functions belong. One group works within a "vertical" scope, traveling up or down between hierarchical levels, while the second group operates within a "horizontal" scope ("across" versus "up and down") of the hierarchy involved, while traveling within the same level of the hierarchy.

The Cousin() Function

As we have shown in previous articles, the capability to perform operations within the vertical and horizontal scopes of the hierarchy can mean more efficient, simpler MDX queries. The Cousin() function belongs to the latter of the two general groups, as it returns a member that is "parallel" in position under a given ancestor member, as we will see. We'll take a look at the Cousin() function in this article, to obtain an appreciation for its capabilities.

Discussion:

As we will observe, the Cousin() function returns the child member that occupies the same relative position under a parent member as the source member (under its own respective parent) that is specified. The operation of the function is based upon the order and position of members within levels. Let's say that two dimensions exist, in which dimension one has three levels, and dimension two has five levels. In this case, the cousin of the second level of dimension one is the second level of the dimension two.

An illustration of this concept might be helpful in providing a visual platform upon which to understand the workings of the function. As in previous articles, we'll consider the Time dimension for this illustration because we all understand the Time hierarchy, and can therefore focus on the core concept instead of the structure of the hierarchy we rely upon as an example. The illustration to which I refer appears below.


Illustration 1: Time Dimension Hierarchy, Showing an Example Cousin Relationship.

We will rely heavily upon the Time dimension in illustrating the syntax and use of the Cousin() function for another related reason: the Time dimension, composed of years, quarters, months, and so forth, works well with Cousin(), because, in most cases, an identical number of descendant members reside under any given ancestor. This scenario provides an excellent basis for using the Cousin() function. There are certainly situations where even typical Time hierarchies are not identical (an example would be the cousin of May 31 in September: September has only thirty days, so there is no valid cousin for May 31 in this instance. But for the most part, the Time dimension is about as predictable as it gets, with regard to consistency in arrangement.

Syntax

The Cousin() function is written in standard function format, which is to say Function(Arguments). The parentheses enclose the arguments, in this case the member that the function specifies (the member for which we seek to return the cousin), and the ancestor member upon whose "lineage" we wish to find the cousin member. The two members within the argument are separated by a comma.

Let's look at the syntax in the following example:

	SELECT 
	{Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS
	FROM [BUDGET]
	WHERE ([Measures].[Amount])

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


Illustration 2: Example Result Dataset from Using the Cousin() Function

As we can see above, the Cousin() function within the example yields the member [1998].Q1. In the Budget cube, the 1997 and 1998 levels both contain Q1, Q2, Q3, and Q4. [1998].Q1 is returned because it occupies the same relative position (the first child position) within the 1998 Year level as is occupied by Q1 (again, the first child) in Year 1997.

A graphic view of the Time hierarchy for the source member ([Time].[1997].[Q1]) within the 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 1997 Year level, within the Time dimension. The order of the members within the database determines the status of Q1 as "first;" Q1 is the first child member of the 1997 Year level. Therefore, when we specify in the second part of the function that we want the "same relative position for the 1998 level of the Time hierarchy," the first child member (which happens again to be Q1, in this example) is returned.

Illustration 3 displays the foregoing graphically.


Illustration 3: Graphical Results of Using the Cousin() Function

In the simple example above, we can easily see that the cousin member is Q1 of 1998.

Practice

Let's construct an expression that calls the Cousin() function into action to reinforce our understanding of how it operates. 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 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 Budget cube in the Cube drop-down list box.

We will begin with a simple illustration that involves the Cousin() function: a basic query designed to extract the total of the measure Amount (the sole measure in the Budget cube) for Q1 of 1998. While this is a relatively meaningless total, we will refine the query to a more practical format as we develop it in subsequent steps.

5.  Type the following query into the Query pane:

 -- MDX07-1:  Tutorial Query No. 1
 SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS
 FROM [BUDGET]
 WHERE ([Measures].[Amount])

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 as soon as Analysis Services fills the specified cells in the Results pane.


Illustration 4: The Query Result Dataset

The query delivers the results that we requested; we see the total for the Amount measure for Q1 of 1998 (the cousin of Q1 1997, as we can clearly see).

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.

Next, we'll develop our approach further to make it a bit more useful. This time, we will write a basic query to return Total Expense by Account, for Q1 1998 (via the Cousin() function again). We will again reference our introduction of the .Children function in Lesson 5. From its use we will enumerate the Accounts contained within Total Expense level of the Account dimension. Our objective will be to create a column for Q1 1998 and a row for each Expense Account.

8.  Type the following into the Query pane:

 -- MDX07-2:  Tutorial Query No. 2
 SELECT {Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS,
 {[Account].[All Account].[Net Income].[Total Expense].Children} ON ROWS
 FROM [BUDGET]
 WHERE ([Measures].[Amount])

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

The Results pane appears as shown in Illustration 5.


Illustration 5: The Query Results

The query delivers the totals for the enumerated children of the Total Expense level of the Account dimension.

Now let's go yet a step further and build a result set that provides the basis of a comparison between Q1 1997 (our source specification) and Q1 1998 (the cousin of the source). Our objective will be to create a column each for Q1 1997 and Q1 1998, providing again a row for each Expense Account.

10.  Type the following into the Query pane:

 -- MDX07-3:  Tutorial Query No. 3
 SELECT {[Time].[1997].Q1, Cousin([Time].[1997].[Q1],[Time].[1998])} ON COLUMNS,
 {[Account].[All Account].[Net Income].[Total Expense].Children} ON ROWS
 FROM [BUDGET]
 WHERE ([Measures].[Amount])

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

Analysis Services populates the cells that the query specifies, and we see the result dataset as depicted in Illustration 6.


Illustration 6: The Result Dataset

The query delivers the total for each enumerated child of the Total Expense level of the Account dimension.

Next in Our Series ...

In this lesson, we explored the slightly more challenging Cousin() function. We discussed the Cousin() function in detail, illustrated the syntax that is appropriate for its effective use, and then tested our understanding of how to leverage this remaining "family" function by undertaking relevant practice exercises. Finally, for each practice exercise, we commented on the results we obtained using the Cousin() function, and discussed some of the limitations that are inherent in its use.

In our next lesson, we will begin an overview of additional "relative" member functions, refreshing and expanding upon our earlier exposure to the powerful .Members function, then extending our exploration to the .CurrentMember, .PrevMember and .NextMember functions.

» 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