In this Level, we will continue the introduction to the general MDX member functions I began in Level 5: Members, and an Introduction to the MDX Members Functions. In particular, we’ll continue our focus on the “family” basic member functions, this time taking up the .LastChild and .FirstSibling functions. We’ll discuss the syntax underlying each, together with the information it returns. We’ll then get some hands-on exposure to each function, creating queries that put it to work. Finally, we’ll analyze the data set that the queries return, to confirm our understanding of the use and operation of the respective function.

Let’s take a look at each of these two “family” functions individually, beginning with the .LastChild function.

The .LastChild Function

Quite similar in operation to the .FirstChild function we explored above, only working from the “opposite end” of the range of the children of a specified member, the .LastChild function returns the last child of the specified member using the syntax we’ll describe next. To illustrate our meaning of the term “last child,” we will return to the visual representation we constructed for .FirstChild in Level 7: Member “Family” Functions: Ancestor() and .FirstChild to view the impact of the .LastChild function, working within the Date.Calendar dimension hierarchy once again for a readily understandable structure.

Syntactically, the .LastChild function is appended to the right of the member, as shown in the following :

<member>.LastChild

A simple example of the .LastChild function in action follows:

SELECT
{[Date].[Calendar].[Calendar Year].[CY 2007].LASTCHILD}
ON COLUMNS
FROM
[Adventure Works]
WHERE
([Measures].[Internet Sales Amount])

The dataset returned would appear as shown:

Illustration 01: Example Result Dataset from Using the .LastChild Function

A quick glance at the Date hierarchy for the source member (CY 2007), to which the .LastChild function is appended above, will make the illustration more meaningful. In the view below, we scan see that H2 CY 2007 is one of the two members of the Semesters / Half-Year sublevel of the Calendar Year level of Date.Calendar dimension hierarchy. The order of the members within the database determines the status of H2 CY 2007 as “last” (the “last” of two children, of which H1 CY 2007 was “first”). H2 CY 2007 is, therefore, returned as the “last child” of the CY level 2007.

Illustration 02: The Hierarchy under Consideration

In the simple example above, as we might have expected, we can easily see that the parent member is CY 2007.

Let’s reinforce our understanding of how .LastChild works by working within a simple, hands-on exercise. To this end, we will assume that a client colleague has made a request: He states that management would like to see the total Internet Sales Amount for each of the Product Categories for the last Semester of Calendar Year 2008. The ultimate intent, he tells us, is to parameterize the year, and so forth. This exercise is to help him understand the options as to the available functions within which he can embed the necessary report / query parameters..

We see this as an opportunity to demonstrate ways to use another MDX function, .LastChild, and proceed to do so with the following steps. We will, as usual, work with the MDX Query Editor in SSMS. If you are not sure how to set up SSMS in Analysis Services to access the Adventure Works DW 2008R2 database, then click here for guidance.

Select File New from the top menu.

Select Query with Current Connection from the cascading menu that appears next.

Type (or copy and paste) the following query into the Query pane:

-- SMDX008-001: Basic Use of .LASTCHILD
SELECT
{[Date].[Calendar].[Calendar Year].[CY 2008].LASTCHILD} ON AXIS(0),
{[Product].[Category].MEMBERS} ON AXIS(1)
FROM
[Adventure Works]
WHERE
([Measures].[Internet Sales Amount])

The query appears in the Query pane.

Illustration 03: The Query, Using .LastChild, in the Query Pane

Click the Execute (!) button in the toolbar, as before.

Analysis Services fills the cells that it determines to be specified by the query, once again, with the following results:

Illustration 04: Results of the Query Using .LastChild()

The query delivers the results that we have requested; we see the totals for the enumerated members of the Product Category level of the Product dimension.

As our example illustrates above, the last child of level member CY 2008 is H2 CY 2008.

Let’s examine a graphical illustration of the operation we have just witnessed, via the results returned by our query, of the .LastChild function. A quick look at the hierarchy illustrates the relationships between the members and levels under consideration that we saw earlier:

Illustration 05: [Date].[Calendar].[Calendar Year].[CY 2008].LASTCHILD is H2 CY 2008

The query delivers the results that we have requested; we see the totals for the enumerated members of the Product Category level of the Product dimension the last child of level member CY 2008, H2 CY 2008.

Save the query by selecting File Save MDXQuery1.mdx As …, naming the file SMDX008-001.mdx, and placing it in a convenient location.

Now let’s try another query employing .LastChild. This time, we’ll write a basic query to return total Internet Sales Amount for the member children of the Product Category level, by quarters of the second half of the year, and thus for each of the children of the member H2 CY 2008 (the last child of CY 2008, as we demonstrated in our last example). Using the .Children function again, as we did with the .FirstChild example earlier, we will enumerate the quarters contained within H2 CY 2008. The intent will be to create a column for each quarter in the second half of CY 2008, and a row for each child of the Category level of the Product dimension.

Select File New from the top menu, as we did earlier.

Select Query with Current Connection from the cascading menu that appears next, once again.

Type (or copy and paste) the following query into the Query pane:

-- SMDX008-002: LASTCHILD with .CHILDREN to Descend Another "Generation"
SELECT
{[Date].[Calendar].[Calendar Year].[CY 2008].LASTCHILD.CHILDREN}
ON AXIS(0),
{[Product].[Category].MEMBERS} ON AXIS(1)
FROM
[Adventure Works]
WHERE
([Measures].[Internet Sales Amount])

The query appears in the Query pane, as shown.

Illustration 06: The Query with .LastChild and .Children Combination

Click the Execute (!) button in the toolbar atop the Management Studio, once again.

The Results appear.

Illustration 07: The Query Results Dataset, Using the .LastChild / .Children Combination

The query delivers the totals for the enumerated members of the Category level of the Product dimension, as before, by quarters, the children of the last child of CY 2008, H2 CY 2008. We see have obtained summaries at the level of the members of the Product Category level for the Internet Sales Amount within the third quarter of CY 2008, because we affixed the .LastChild function to the CY 2008 year level of the Date.Calendar dimension hierarchy, and then suffixed .LastChild with the .Children function.

Save the query by selecting File Save MDXQuery2.mdx As …, naming the file SMDX008-002.mdx.

Now that we’ve seen how to use the .FirstChild function, let’s take a look at one more “family” member function, .FirstSibling, before concluding this Step .

The .FirstSibling Function

Books Online states that the .FirstSibling function “returns the first child of the parent of a member.” If this seems a little confusing, when compared to .FirstChild (another member function that we introduced in Level 7: Member “Family” Functions: Ancestor(), .FirstChild and .LastChild), consider that the definition does not say that .FirstSibling represents the” first child” of a given member, but that it comprises the “first child of that member’s parent,” which in a human family would mean a brother or a sister (hence “sibling.”) As we mentioned in Level 6: Member “Family” Functions: .Parent and .Children, the .FirstSibling (as well as the .LastSibling) function operates within a “horizontal” scope for the hierarchy involved. It effectively “travels” within the same level of the hierarchy (“across,” versus “up and down,” as was the case with the .FirstChild and .LastChild functions, which we saw in the previous Level.

An example sibling relationship, within the context of a given dimensional member and level of the Adventure Works sample cube, is presented here.

Illustration 08: Example of .FirstSibling in the Date.Calendar Dimension Hierarchy

A potentially confusing characteristic of the “family naming” lies in the fact that the first child (January 2007 in the example illustrated above) also fills the role of first sibling – it is “first” within the horizontal level, and is the first sibling of any member residing at the same level. This is an ordering concept, and becomes straightforward once the meaning is clear. To amplify the concept with another illustration, let’s examine the relationships when we consider the first sibling of March 2007 within the same hierarchical structure.

Illustration 09: First Sibling of March 2007 is January 2007

We see in the illustration above that the first sibling of March 2007 is January 2007 – the first member in the level, plain and simple.

Syntactically, the .FirstSibling function is appended to the right of the member, as shown in the following:

<member>.FirstSibling

A simple example of the .FirstSibling function in action follows:

SELECT
{[Date].[Calendar].[Calendar Quarter].[Q2 CY 2007].FIRSTSIBLING}
ON COLUMNS
FROM
[Adventure Works]
WHERE
([Measures].[Internet Sales Amount])

The results dataset returned would appear as shown:

Illustration 10: Example Result Dataset from Using the .FirstSibling Function

A look at the Date hierarchy for the source member (Q2 CY 2007), which is acted upon by the .FirstSibling function above, might make the example above more meaningful. As we can see below, Q2 CY 2007 is a member of the Quarters sublevel of the Calendar Year level, within the Date.Calendar dimension hierarchy. The order of the members within the database determines the status of Q1 CY 2007 as “first;” Q1 CY 2007 is a sibling (one of four members) of the Quarter level within CY2007, and, as it is “first in line,” it is returned via the .FirstSibling function. (It might also be described as “the ‘first in line’ of those members sharing the same level as member Q2 CY 2007.”)

Illustration 11: [Date].[Calendar].[Calendar Year].[Q2 CY 2007].FIRSTSIBLING is Q1 CY 2007

Let’s get some hands-on practice with the .FirstSibling function, keeping an eye on the results we obtain with each query we construct. For purposes of our practice session, let’s assume, once again that we have been given a business requirement by a client representative. We are asked by an analyst in the Sales and Marketing department of the Adventure Works organization to provide the total Internet Sales Amount, for each of the various Bike Product Categories sold by the organization, over Quarter 3 of Calendar Year 2008. We are asked to provide the information in a two-dimensional grid, with the specified quarter in the column heading and the three Bike categories (the “children” of the Category level of the Product dimension) on the rows.

Let’s create a rudimentary query designed to return total Internet Sales Amount for each child member of the Bikes product category for Q3 of 2007, by taking the following steps.

With the Adventure Works cube selected, click the New Query button.

Type (or copy and paste) the following query into the Query pane:

-- SMDX008-003: Basic Use of .FIRSTSIBLING
SELECT
{[Date].[Calendar].[Calendar Quarter].[Q4 CY 2007].FIRSTSIBLING}
ON AXIS(0),
{[Product].[Product Categories].[Category].[Bikes].CHILDREN}
ON AXIS(1)
FROM
[Adventure Works]
WHERE
([Measures].[Internet Sales Amount])

The query appears within the Query pane.

Illustration 12: Query, Employing .FirstSibling, in the Query Pane

Click the Execute (!) button in the toolbar, as before.

The Results pane appears next.

Illustration 13: The Query Results, Using .FirstSibling

The results dataset displays a summary of Internet Sales Amount for each constituent member of the Bikes category for the third quarter of Calendar Year 2007, because we affixed the .Children function to the Bikes member of the category level. As we have stated to be the case with other functions we have examined in this and other Levels of this series, we will find the .Children function far more powerful at later junctures in our series, when we are using a relative member, such as .CurrentMember, where the calculation within which .Children is placed will determine its context.

Save the query by selecting File Save MDXQuery3.mdx As …, naming the file SMDX008-003.mdx.

Next, let’s assume that our client colleague brings us another request that involves a .FirstSibling / .Children combination, albeit with a narrower focus: this time, the analyst states that he’d like to see the total Internet Order Quantity, associated with customer accounts in the United Kingdom, for the Touring bike product line, by month for the three months of the third quarter of Calendar Year 2007. Employing the .Children function again, we can easily enumerate the months contained within Q3 CY 2007, which we have already determined, in the query immediately above, to be the first sibling of member Q4 CY 2007 (the first of the peer members in its semester horizontal level) of the Date dimension.

To restate, our intent will be to create a column for each month in Q3 CY 2007, and a row for each child member of the Touring subcategory level of the Bikes product line.

Select File New from the top menu.

Select Query with Current Connection from the cascading menu that appears next.

Type (or copy and paste) the following query into the Query pane:

-- SMDX008-004: .FIRSTSIBLING with .CHILDREN to Descend
-- Another "Generation"
SELECT
{[Date].[Calendar].[Calendar Quarter].[Q4 CY 2007].FIRSTSIBLING.CHILDREN}
ON AXIS(0),
{[Product].[Product Model Lines].[Product Line].[Touring].CHILDREN}
ON AXIS(1)
FROM
[Adventure Works]
WHERE
([Measures].[Internet Order Quantity],
[Customer].[Customer Geography].[Country].[United Kingdom])

In addition to using the .Children function with another source member, we are also expanding the WHERE statement to include, in turn, the slicer dimension to specify not only the measure (Internet Order Quantity ), as we did in the earlier query, but also a single customer Country, the United Kingdom. The cube is therefore filtered, or “sliced” for the Internet Order Quantity and the United Kingdom members, within the context in which they are specified.

The query we have added appears:

Illustration 14: The Query, Combining .FirstSibling and .Children …

Click the Execute (!) button in the toolbar, as before.

Analysis Services returns the specified results:

Illustration 15: The Query Results from the .FirstSibling / .Children Combination

The results dataset displays the Internet Order Quantity totals for the children of the Touring bike product line, for the three months comprising Quarter 3 of Calendar Year 2007, as purchased by customers in the United Kingdom.

Save the query by selecting File Save MDXQuery4.mdx As …, naming the file SMDX008-004.mdx.

The results shown above present an opportunity to make our presentation more user-friendly to its ultimate audience. Blank / null spaces often confuse information consumers (although we’d want to ascertain that they don’t want them to appear, as consumers do sometimes wish to see them), so let’s remove them with a minor adjustment to the query above.

Modify the comment line (to lines 1 through 3 of the syntax below), and add the keywords NON EMPTY to the far left of the rows specification (line 7) of the query we crafted above, as follows:

-- SMDX008-005: .FIRSTSIBLING with .CHILDREN to Descend
-- Another "Generation" [ADDING "NON EMPTY" TO ROW
-- AXIS TO REMOVE EMPTIES]
SELECT
{[Date].[Calendar].[Calendar Quarter].[Q4 CY 2007].FIRSTSIBLING.CHILDREN}
ON AXIS(0),
NON EMPTY{[Product].[Product Model Lines].[Product Line].[Touring].CHILDREN}
ON AXIS(1)
FROM
[Adventure Works]
WHERE
([Measures].[Internet Order Quantity],
[Customer].[Customer Geography].[Country].[United Kingdom])

The modified query, with adjusted sections circled, appears within the Query pane as shown.

Illustration 16: The Query with “Non Empty” Modifications in the Query Pane

Press F5 to execute the newly modified query.

Analysis Services returns the dataset depicted:

Illustration 17: Results of the Modified Query – Non Empty in Action

As before, the results dataset displays the Internet Order Quantity totals for the children of the Touring bike product line, for the three months comprising Quarter 3 of Calendar Year 2007 (“children of the first sibling” of Quarter 4 of the same year), as purchased by customers in the United Kingdom. But this time the empty intersects are removed. The NON EMPTY keywords facilitate the suppression of empties; any tuples on the affected axis that are associated with empty intersects are efficiently stripped out before the results are displayed.

Save the query by selecting File Save SMDX008-004.mdx As …, naming the file SMDX008-005.mdx.

We will continue our examination of the “family” member functions in the next Level of Stairway to MDX.

Summary …

In this Level, we continued the introduction to the general MDX member functions begun in Level 5: Members, and an Introduction to the MDX Members Functions. In this Step, we extended our focus on the “family” basic member functions, exploring the .LastChild and .FirstSibling functions, discussing for each the syntax within which it is employed, as well as the information it returns and other details. We gained the know-how needed to take advantage of these useful functions through the typical Stairway practice exercises, whereby we constructed queries that used each function, and then examined together the results datasets those queries delivered.