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 May 10, 2004

MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function - Page 3

By William Pearson

Practice

The Basics

To reinforce our understanding of the basics we have covered so far, we will use the Head() function in a manner that illustrates its operation in the simplest of scenarios: We will construct a SELECT query with a clearly defined set within our function, to demonstrate its operation in a straightforward manner.

As we have in many of the preceding articles of the series, we will use the MDX Sample Application as a platform from which to construct and execute the MDX we examine, and to view the results 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.

Let's assume, for our practice example, that we have received a call from corporate Logistics needing some shipping metrics. The Logistics information consumers wish to know total Units Shipped balances for the first three Quarters of 1998, by U. S. States of Warehouse operation.

To rephrase, the objective will be to present a single measure, Units Shipped, for the first three Quarters of 1998, broken out by U. S. Warehouse-State. While we can present this information by including each of the three Quarters individually in our query, we will use the Head() function to compact our query, and to leverage the .Children function, similar to the scenario we described in the Syntax section.

Let's construct a simple query, therefore, to return the first three Quarters as columns and the Warehouse-States as rows.

5. Type the following query into the Query pane:


-- MDX019-1, Use of Head() Function in a Basic Query
SELECT
   {HEAD([Time].[1998].Children, 3)} ON COLUMNS,
   {[WAREHOUSE].[Country].[USA].Children} ON ROWS
FROM
 [Warehouse]
WHERE (Measures.[Units Shipped])

6. Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 1 appears.


Illustration 1: Result Dataset - Head() Function in Basic Query

We see Q1, Q2, and Q3 populating the columns across, and the U.S. Warehouse-States (from the Warehouse dimension) appearing on the row axis.

7. Select File -> Save As, name the file MDX19-1, and place it in a meaningful location.

8. Leave the query open for the next section.



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