Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 25, 2003

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

By William Pearson

Introduction to the "Family" Member Functions

In the first lesson of the Member Functions segment of our series, we will take some time to "get to know the family." 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.

We will discuss some of the concepts that underlie the "family" functions, and discuss the appropriate syntax involved in using each effectively. Next, we will provide hands-on exposure to the use of each in simple expressions / queries that we will subsequently run to view the output and reinforce our understanding of the function / component from the perspective of the result dataset that it returns.

This lesson will include:

  • Further general discussion surrounding members and member functions and their roles in MDX, when relevant to the component under consideration.
  • An introduction to the .Parent, .Children and Ancestor() functions, with
    • an examination of the syntax that surrounds each function's use;
    • an illustrative example of its use in a practice exercise;
    • a brief discussion of the MDX results we obtain in the practice example.
  • An introduction to the WHERE clause, and the specification of slicer dimensions.
  • A look ahead to the additional member "family" functions that we will explore in forthcoming articles.

As we mentioned in Lesson 4, member functions, particularly those of the "family" group, allow us to perform operations based upon a member's relative position, either up ("above" the member upon which the function is being performed) or down ("below" the member upon which the function is being performed) a dimensional hierarchy. A zero is returned if we attempt to go beyond the boundaries of a defined hierarchy an example of this might be trying to apply the .Children function to a member at the bottom / leaf level of the hierarchy (which is childless, in this context). Like most member functions, the "family" functions can be quite valuable in helping us to simplify our MDX queries and make them more compact.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM