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 gaining our first exposure to the member functions, we will take some time to begin to “get to know the family.”

I began calling one specific subset of the member functions the “family” functions in MDX articles I published in the 2003 – 2004 timeframe, because many of their names resemble those of family members.  (This naming of the group has been widely adopted since.)   Some of the functions that comprise the “family” functions group include:

• .Parent
• .Children
• Ancestor()
• Cousin()
• .FirstChild
• .LastChild
• .FirstSibling
• .LastSibling

As I’ve stated often since then, 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, too, of course, but I will focus primarily on the respective member functions over the next few levels of our series.

I will discuss some of the concepts that underlie the “family” functions, and expose the appropriate syntax involved in using each effectively.  For each function, we will then get some hands-on exposure to its use in simple expressions / queries that we will, in turn, execute against the sample Analysis Services database to view the output and reinforce our understanding of the function / component from the perspective of the results dataset that it returns.

This Level will also include further general discussion surrounding members, and member functions and their roles in MDX, when relevant to the component under consideration.  I’ll also introduce the WHERE clause, and give some details on the general specification of slicers within an MDX query.  Finally, we will look ahead, throughout this level, to the additional member “family” functions that we will explore in forthcoming levels.

Let’s begin with an overview of the “family” member functions as a group.

Introducing the “Family” Member Functions

As a part of my introduction to the concept of members in Analysis Services in Level 5: Members, and an Introduction to the MDX Members Functions, where I introduced the highly useful .Members function for starters, I launched an examination of the MDX member functions that will span multiple levels of this series.  This and subsequent levels will focus upon a few member functions at a time, beginning with the “family” functions, contrasting the uses and effects of each.  In this level, I will expose the .Parent and .Children member functions, discussing the information they return, together with syntactical points of their use.  We will gain the know-how needed  to take advantage of these useful functions through the typical Stairway practice exercises, whereby we construct queries that use the functions, and then examine together the results datasets those queries deliver.

I’ve learned, after years of working with the language, and from the comments of many of my readers, that this is a great way to learn MDX.

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

The capability to perform operations within the vertical and horizontal scopes of the hierarchy can mean more efficient, simpler MDX queries – queries that effectively leverage multidimensional structures created to make information rapidly available for analysis and reporting.  We’ll 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

As we’re about to see, the .Parent function returns the parent of a specified member using the syntax that follows. (The function is especially useful in calculated members, with which we will work heavily later in the series.)

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
  NON EMPTY {[Date].[Calendar].[Calendar  Year].MEMBERS} ON COLUMNS,
  {[Product].[Category].[Bikes].PARENT} ON  ROWS
  FROM
  [Adventure Works]
  WHERE
  ([Measures].[Internet Sales Amount])

The results dataset returned when executing the above would appear as depicted here.

Illustration 1:  Example Result Dataset from Using the .Parent Function

Perhaps a look at the hierarchy for Bikes, the source - or “child” - member from the perspective of the .Parent function above, will make the illustration more meaningful.  From a browse of the cube, and specifically the Product dimension, we can see that Bikes is a child of the All Products level, within the Category attributehierarchy of the Product dimension.

Illustration 2:  The Attribute Hierarchy Containing the All Products Level, Parent to the Bikes Member

In this simple example we can also see that the parent member is All Products.  We will likely more deeply appreciate 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.  (More on this will come later).

As I have already mentioned in a previous level, and as many readers will already know, the WHERE clause is optional in a query, and determines the member or dimension to be used as the slicer. This specification limits the data returned to specific dimension(s) or member(s). The example I have cited here uses a WHERE clause to limit the data extracted for the axis dimensions to a specific member of the Measures dimension, Internet Sales Amount. This represents a simple WHERE clause:  our use of the WHERE clause will take many forms as we progress through the Stairway to MDXseries, and we will address more elaborate uses as they arise.

Let’s reinforce our understanding of how the .Parent function operates by constructing an expression that calls it into action.  We’ll use the same core expression, starting with an expression that does not use the function (and to which we will add it later) throughout the lesson, which will allow us to explore the different results we obtain in a way that the respective returned data sets can be contrasted against one another.  We will, as usual, work with the MDX Query Editor in SSMS.  If you are not sure how to set up SSMS to access the Adventure Works DW 2008R2 Analysis Services database, then click here for guidance.

To get started, let’s assume that we have, once again, been given a business requirement by a hypothetical client:  We have been asked by an analyst in the Sales and Marketing department of the Adventure Works organization to provide the total Internet Order Quantity for a specific subcategory of products, Mountain Bikes, sold by the organization over the Calendar Years whose data is contained in the Adventure Works cube.  In this case, we are asked to provide the information in a two-dimensional grid, once again, with the Calendar Years in the column axis (or “Axis (0)” ), and the specified product subcategory member of interest, the Mountain Bikes Product subcategory in the row axis ( “Axis (1)” ).

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

--  SMDX006-001:  Using .MEMBERS in Initial  Query to Prep for .PARENT 
  -- Demonstration
SELECT
  NON EMPTY {[Date].[Calendar].[Calendar  Year].MEMBERS} ON AXIS(0),
   {[Product].[Product Categories].[Subcategory].[Mountain  Bikes]} ON AXIS(1)
FROM
  [Adventure Works]
WHERE
  ([Measures].[Internet Order Quantity])

The query appears within the Query pane.

Illustration 3:  The Query in the Query Pane

• Click the Execute (!) button in the toolbar, once again.

The Results pane is populated with the dataset.

Illustration 4:  The Query Results – Preliminary “Core” Query Results Dataset

We see that we have obtained a summary, by Calendar Year (having once again applied the .Members function to the Calendar Year level of the Date dimension – Calendar hierarchy), of Internet Order Quantity sold for the Mountain Bikes product subcategory.

• Save the query by selecting File --> Save MDXQuery1.mdx As …, naming the file SMDX006-001, and placing it in a meaningful location.

Now let’s extend our basic query to illustrate the operation of the .Parent function.  A quick look at the hierarchy illustrates the relationships between the members and levels under consideration.

Illustration 5:  The Hierarchy under Consideration

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

--  SMDX006-002:  Using .PARENT Function in  Row Specification
SELECT
  NON EMPTY {[Date].[Calendar].[Calendar  Year].MEMBERS} ON AXIS(0),
  
  {[Product].[Product  Categories].[Subcategory].[Mountain Bikes].PARENT}
  ON AXIS(1)
FROM 
  [Adventure Works]
WHERE
  ([Measures].[Internet Order Quantity])

The query appears within the Query pane.

Illustration 6:  The Modified Query in the Query Pane

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

The Results pane is populated.

Illustration 7:  The Query Results – Extended Query Example

The results dataset displays the totals at the Bikes level, which is somewhat obviously the Parent of the Mountain Bikes member (the source member upon which the .Parent function is enacted in our query above).

We see that we have now obtained a summary at the Bikes level of the Internet Order Quantity for the calendar years 2005 through 2008, because we affixed the .Parent function to the Mountain Bikes level.  As we have stated before, 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.

• Save the query by selecting File --> Save MDXQuery2.mdx As …, and naming the file SMDX006-002.

Now that we’ve seen how to use the .Parent function, let’s move to the next “family” member function, .Children.

The .Children Function

Much like the .Parent function, the .Children function works within a “vertical” scope, moving down between hierarchical levels from the “source” member to which the function is applied.  As its name implies, the .Children function returns the children of the source member.  Again resembling the .Parent function (except for the “direction” of its “travel”), the .Children function is especially useful in calculated members, among other scenarios, most of which we will explore later in the Stairway to MDX series.

Syntactically, the .Children function is appended to the right of the member:

  <member>.Children

A simple illustration of the .Children function, using an example somewhat similar to the example I cited earlier to illustrate the syntax for the .Parent function, is as follows:

SELECT
  NON EMPTY {[Date].[Calendar].[Calendar  Year].MEMBERS} ON COLUMNS,
  {[Product].[Product  Categories].[Category].[Bikes].CHILDREN} ON ROWS
FROM
  [Adventure Works]
WHERE
  ([Measures].[Internet Sales Amount])

The results dataset returned when executing the above would appear as shown:

Illustration 8:  Example Results Dataset Generated Using the .Children Function

Referring again to the hierarchical structure within which our source member lies, we can see that the .Children function has caused a shift in the opposite direction to that we saw for the .Parent function above.

Illustration 9:  Downward Movement along the Hierarchy under Consideration

We can begin the reinforcement of our understanding of how the .Children function operates by working within another simple illustration, and extending that illustration to other basic examples that actually add the .Children function to a given member. To get started, let’s assume that we have, as before, been given a business requirement by a hypothetical client:  We have been, once again, asked by an analyst in the Sales and Marketing department of the Adventure Works organization to provide the total Internet Order Quantity for all “children” (the individual bike products) of the specific subcategory of products, Mountain Bikes, sold by the organization, over the Calendar Years whose data is contained in the Adventure Works cube.

Once again, we are asked to provide the information in a two-dimensional grid, with the Calendar Years in the column axis and the specified product members of interest, the various individual Mountain Bikes, in the row axis.

• Select File --> New from the top menu.
• 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:

--  SMDX006-003:  Using .CHILDREN Function in  Row Specification
SELECT
  NON EMPTY {[Date].[Calendar].[Calendar  Year].MEMBERS} ON AXIS(0),
  {[Product].[Product  Categories].[Subcategory].[Mountain Bikes].CHILDREN}
  ON AXIS(1)
FROM 
  [Adventure Works]
WHERE
([Measures].[Internet Order Quantity])

The query appears within the Query pane.

Illustration 10:  The Query in the Query Pane

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

The Results pane is populated as partially depicted here.

Illustration 11:  The Query Results in the Results Pane (Partial View)

The results dataset displays the totals at the level of the individual bikes, the Children of the Mountain Bikes member (the source member upon which the .Children function is enacted in our query above).

We have now obtained a summary of Internet Order Quantity for each constituent member of Mountain Bikes for the calendar years 2005 through 2008, because we affixed the .Children function to the Mountain Bikes level.  As we have stated to be the case with other functions we have examined in this article, we will find the .Children function far more powerful at a later juncture in our series, when we are using a relative member, such as .CurrentMember, so that the calculation within which .Children is placed determines its context.

• Save the query by selecting File --> Save MDXQuery6.mdx As …, and naming the file SMDX006-001.mdx.

Now, to reinforce understanding, let’s illustrate the operation of the .Children function once again, with another example.

• Select File --> New from the top menu.
• 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:

--  SMDX006-004:  Using .CHILDREN Function in  Row Specification,
  --   Second Example
SELECT  
  NON EMPTY {[Date].[Calendar].[Calendar  Year].MEMBERS} ON AXIS(0),
  NON EMPTY {
  [Product].[Product Model Lines].[Product  Line].[Accessory].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 (see above for a discussion) to expand, in turn, the slicer dimension to specify not only the Internet Order Quantity measure, but 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 appears within the Query pane.

Illustration 12:  The Query in the Query Pane

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

Analysis Services populates the Results pane, once again.

Illustration 13:  The Query Results in the Results Pane (Partial View)

The results dataset displays the Internet Order Quantity totals for the children of the Product Accessory level, for the calendar years 2007 and 2008, as purchased by customers in the United Kingdom.

• Save the query by selecting File --> Save MDXQuery7.mdx As …, and naming the file SMDX006-001.mdx.
• Exit SQL Server Management Studio as desired.

As we move into subsequent levels of Stairway to MDX, and into the use of many of the functions available in MDX, as well as into progressively more advanced stages of query building, we will call upon these and other “family” functions frequently. As we’ll see, they are often quite powerful when used in tandem with other functions.  As we have noted throughout the series so far, a grasp of the basic operators and functions will be vital to success in our taking advantage of the more complex MDX concepts that we will uncover as we proceed.  Practice with these components will assure that their use comes as second nature to us, and will create a foundation from which the power and elegance of MDX can be fully exploited.

Summary

In this level, we continued the introduction to the general MDX member functions I began in Level 5: Members, and an Introduction to the MDX Members Functions, where I also introduced the pervasive and powerful .Members function. In gaining our first exposure to the member functions, we began an examination that will span several levels of this series, with each level focusing upon a couple of member functions at a time.  In this level, we got some hands-on familiarity with the .Parent and .Children member functions, discussing the syntax within which they are used, as well as the information they return and other details.  We again 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.