MDX Essentials: Member Functions: More "Family" Functions - Page 3

Practice

Let's construct an expression that calls the .FirstChild function into action to reinforce our understanding of how it operates. As we have done in previous lessons, we will use similar core expressions throughout sections of this article, to explore the different results we obtain in a way that they can be contrasted against each other.

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 will allow us to view the result datasets we obtain.

We will begin with a simple illustration that involves the .FirstChild function: a basic query designed to extract total Warehouse Sales for each member of the Product Departments Level of the Product dimension for Q1 of 1997.

-- MDX06-1:  Tutorial Query No. 1
SELECT
{[Time].[Year].[1997].FirstChild} ON COLUMNS,
{[Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

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

The query delivers the results that we requested; we see the totals for the enumerated members of the Product Department hierarchy.

As to the use of the .Members operator, recall from Lesson 4 of this series that the .Members operator gives us the members of the dimension.

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

As our straightforward example makes quite obvious, the first child member of level member 1997 is Q1.

Now let's try another query using .FirstChild. This time, we will write a basic query to return total Warehouse Sales by Product Department, by month for each of the children of the member Q1. Recall our discussion surrounding the .Children function in Lesson 5. From its use we will enumerate the months contained within Q1, which we saw earlier was the first child of member 1997 of the time dimension. Our objective will be to create a column for each month in Q1 and a row for each product department.

Type the following query into the Query pane:

-- MDX06-2:  Tutorial Query No. 2
SELECT
{[Time].[Year].[1997].FirstChild.Children} ON COLUMNS,
{[Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

The Results pane appears as shown in Illustration 6.

The query delivers the totals for the enumerated members of the Product Department level of the Product dimension, for each of the children (that is, 1, 2, and 3) of the first child (Q1)of the 1997 member of the year level of the Time dimension. The months are expressed as numbers, as the cube was designed in this way. We could easily convert the numbers to the string names, but we'll leave that for another lesson.

We see that we have obtained a summary for each of the Product Department member's Warehouse Sales, for each month of the first quarter of 1997, because we affixed the .FirstChild function to the 1997 year level of the Time dimension, and then appended the .Children function to the .FirstChild function. As we determined was the case in our exposure of the .Parent function of Lesson 5, as well as elsewhere, we will find the .FirstChild and other "family" functions even more powerful at a later juncture in our series, when we are using a relative member, such as .CurrentMember, where the calculation within which the respective family function is placed will determine its context.