MDX Member Functions: The "Family" Functions - Page 3

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:

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.

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.

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:

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.

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.