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 Apr 21, 2003

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

By William Pearson

Working with the Member "Family" Functions

We discussed two general groups (from the perspective of the result datasets they return) to which the members of the "family" functions belong in our last lesson. One group works within a "vertical" scope, traveling up or down between hierarchical levels, as we will see in the Practice section for each respective function. The functions in this article (like the Ancestor(), .Children and .Parent functions of our last lesson) that act "vertically" include:

  • .FirstChild, and
  • .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, within the context of this article, include:

  • .FirstSibling, and
  • .LastSibling.

As we have already determined, the capability to perform operations within the vertical and horizontal scopes of the hierarchy can mean more efficient, simpler MDX queries. We'll take a look at our current selection of "family" functions individually, to obtain an appreciation for their capabilities, in the sections devoted to each that follow.

The .FirstChild Function

Discussion:

As we will observe, the .FirstChild function returns the first child of a specified member, using the syntax we describe below. An illustration of this concept might be helpful in getting an understanding of just what we mean by a "first child," as well as to provide a visual platform upon which to understand the other "family" functions that we will explore in this lesson.

I chose the Time dimension for this illustration because we all understand the time hierarchy, perhaps making a grasp of the core concepts less subject to being fettered by a need to study the hierarchical setup of a sample database. The illustration to which I refer appears below.


Illustration 1: Time Dimension Hierarchy, Showing Year 1997 with Expanded Q4

 

Syntax

The .FirstChild function is appended to the right of the member, as shown in the following illustration:

<member>.FirstChild

A simple illustration of the .FirstChild function in action follows:

SELECT
{[Time].[Year].[1997].FirstChild} ON COLUMNS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

The result dataset returned would appear as shown in Illustration 2 below:


Illustration 2: Example Result Dataset from Using the .FirstChild Function

A look at the Time hierarchy for the source member (1997) within the .FirstChild function above will make the illustration more meaningful. In Illustration 3 below, we see that Q1 is a member of the Quarters sublevel of the Year level, within the Time dimension. The order of the members within the database determines the status of Q1 as "first;" Q1 is the child of the Year level 1997, and as it is "first in line," it is returned.

Illustration 3 below displays the foregoing graphically.


Illustration 3: [1997].FirstChild is Q1

In the simple example above, we can easily see that the parent member is 1997.

We introduced the WHERE clause in our last lesson, in a discussion of the .Parent function. As we discussed at that point, the WHERE clause is optional; it specifies the Slicer Dimension, and limits the data returned to specific dimension(s) or member(s). The WHERE clause in our illustration above restricts the data extracted for the axis dimensions to a specific member of the Measures dimension, Warehouse Sales.



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