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 6

By William Pearson

Practice

Let's confirm our understanding of the .FirstSibling function with a quick query. We will create a rudimentary query designed to return total Units Shipped for each member of the Beverages Product Department Level of the Product dimension for Q1 of 1997.

4. Type the following query into the Query pane:

-- MDX06-5:  Tutorial Query No. 5
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling} ON COLUMNS,
{[Product].[Product Department].[Beverages].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

5. Select the F5 key (an alternative way to run the query).

The query executes, and the following result dataset appears in the Results pane.


Illustration 14: The Query Result Dataset

The query delivers the results that we requested; we see the Q1 (the first sibling of specified member Q3) totals for the enumerated children of the Beverages level, within the Product Family level of the Product hierarchy.

Let's write another query to return total Units Shipped for the Canned Foods Product Department, by month, for Q1, using what we have learned. Employing the .Children function again, we will enumerate the months contained within Q1, which we have determined, in the query immediately above, to be the first sibling of member Q3 (the first of the peer members in its horizontal level) of the Time dimension. To restate, our intent will be to create a column for each month in Q1 and a row for each child member of the Canned Foods Product Department level of the Product dimension.

6. Type the following query into the Query pane:

-- MDX06-6:  Tutorial Query No. 6
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling.Children} ON COLUMNS,
{[Product].[Product Department].[Canned Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

7. Press F5 to run the query.

The Results pane appears as shown in Illustration 15.


Illustration 15: The Query Results

The query delivers the totals for the enumerated children of the Canned Foods product department by Q1 month (that is, 1, 2, and 3), the children of the Q3 member's first sibling Q1.

The results shown in Illustration 15 present an opportunity to examine another nuance we can use to make our presentation of data perhaps more user-friendly to its ultimate audience. Blank spaces often confuse information consumers, so let's remove them with a minor adjustment to Query No. 6 above.

8. Add the keywords NON EMPTY to Query No. 6 as shown below:

-- MDX06-6M:  Tutorial Query No. 6 [MODIFIED TO REMOVE EMPTIES]
SELECT
{[Time].[Year].[1997].[Q3].FirstSibling.Children} ON COLUMNS,
NON EMPTY {[Product].[Product Department].[Canned Foods].Children} ON ROWS
FROM [Warehouse]
WHERE ([Measures].[Units Shipped])

9. Press F5 to execute the newly modified query.

The Results pane appears as shown in Illustration 16 below.


Illustration 16: The Query Results

The query delivers the totals for the enumerated children of the Canned Foods product department by Q1 month, as before, but with the empty result intersects removed. Using the NON EMPTY query in this fashion allows us to suppress the empties; any tuples on the affected axis that are associated with empty intersects are efficiently removed before the results are displayed.



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