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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 24, 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

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date