MDX Part-5("Family" Member Functions)

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.

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.

-------------------------------------------------##-----------------------------------------

.Parent() Function:

The .Parent function returns the parent of a specified member using the syntax we describe below. The function is especially useful in calculated members.

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

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

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

Example:

Let's begin with a basic query to extract total Frozen Foods Units Shipped for 1997 and 1998 (the only years in our cube).

SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods]} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

A quick look at the hierarchy illustrates the relationships between the members and levels under consideration, as shown below:

SELECT
{[Time].[Year].Members} ON COLUMNS,
{[Product].[Product Family].[Food].[Frozen Foods].Parent} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

The Results pane appears as shown below.
The result dataset displays the totals at the Food level, which is somewhat obviously the Parent of the Frozen Foods level.
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 .Parentfunction to the Frozen Foods level.
The .Children Function:

Much like the .Parent function, the .Children function works within a "vertical" scope, moving, 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.

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:
 
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:



The Ancestor() Function:
The Ancestor() function retrieves the member, at the specified level, or at the specified distance,that is the ancestor .Like the .Parent and .Children functions, it travels within a "vertical" scope, movingup between hierarchical levels from the member to which it is applied.


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)

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:

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