MDX Member Functions: The Cousin () Function - Page 2

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.

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:

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.

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