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.
» 
Comment and Contribute
