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 3

By William Pearson

Practice

Let's construct an expression that calls the .FirstChild function into action to reinforce our understanding of how it operates. As we have done in previous lessons, we will use similar core expressions throughout sections of this article, to explore the different results we obtain in a way that they can be contrasted against each other.

The MDX Sample Application (see the second article in our series, Structure of the MDX Data Model, for more information about the Sample Application) will be our tool for constructing and executing our expressions, and will allow us to view the result datasets we obtain.

1. Start the MDX Sample Application.

2. Clear the top area (the Query pane) of any queries or remnants that might appear.

3. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4. Select the Warehouse cube in the Cube drop-down list box.

We will begin with a simple illustration that involves the .FirstChild function: a basic query designed to extract total Warehouse Sales for each member of the Product Departments Level of the Product dimension for Q1 of 1997.

5. Type the following query into the Query pane:

-- MDX06-1:  Tutorial Query No. 1
SELECT
{[Time].[Year].[1997].FirstChild} ON COLUMNS,
{[Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

6. Click the Run button (the green "arrowhead" icon) on the toolbar atop the Sample Application, to execute the query.

We see the result dataset below, which appears in the Results pane as soon as Analysis Services fills the cells that the query specifies.


Illustration 4: The Query Result Dataset

The query delivers the results that we requested; we see the totals for the enumerated members of the Product Department hierarchy.

7. If it is desirable to save this or subsequent queries, we can do so by selecting File ` Save As, and give the file a meaningful name and location.

As to the use of the .Members operator, recall from Lesson 4 of this series that the .Members operator gives us the members of the dimension.

Now let's illustrate the operation of the .FirstChild function. A quick look at the hierarchy illustrates the relationships between the members and levels under consideration, as shown below:


Illustration 5: The Hierarchy under Consideration

As our straightforward example makes quite obvious, the first child member of level member 1997 is Q1.

Now let's try another query using .FirstChild. This time, we will write a basic query to return total Warehouse Sales by Product Department, by month for each of the children of the member Q1. Recall our discussion surrounding the .Children function in Lesson 5. From its use we will enumerate the months contained within Q1, which we saw earlier was the first child of member 1997 of the time dimension. Our objective will be to create a column for each month in Q1 and a row for each product department.

Type the following query into the Query pane:

-- MDX06-2:  Tutorial Query No. 2
SELECT
{[Time].[Year].[1997].FirstChild.Children} ON COLUMNS,
{[Product].[Product Department].Members} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Warehouse Sales])

8. Click Query on the top menu, and then select Run.

The Results pane appears as shown in Illustration 6.


Illustration 6: The Query Results

The query delivers the totals for the enumerated members of the Product Department level of the Product dimension, for each of the children (that is, 1, 2, and 3) of the first child (Q1)of the 1997 member of the year level of the Time dimension. The months are expressed as numbers, as the cube was designed in this way. We could easily convert the numbers to the string names, but we'll leave that for another lesson.

We see that we have obtained a summary for each of the Product Department member's Warehouse Sales, for each month of the first quarter of 1997, because we affixed the .FirstChild function to the 1997 year level of the Time dimension, and then appended the .Children function to the .FirstChild function. As we determined was the case in our exposure of the .Parent function of Lesson 5, as well as elsewhere, we will find the .FirstChild and other "family" functions even more powerful at a later juncture in our series, when we are using a relative member, such as .CurrentMember, where the calculation within which the respective family function is placed will determine its context.

9. Leave the Sample Application open throughout the next sections.



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