Practice
The Basics
To reinforce our understanding of the basics we have covered
so far, we will use the Tail() function in a manner that illustrates its
operation. We will do so in simple scenarios that place Tail() in
contexts similar to those we used for Head() in our last article. The
intent of our replication here is, again, to provide contrast in the operation
of the results datasets returned by the functions while highlighting their
similarities in operation.
To begin, we will construct a SELECT query with a
clearly defined set (Quarters in the Year) within our function,
to demonstrate its operation in a straightforward manner.
Let's return to 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, as we did in our last article,
needing some shipping metrics. The Logistics information consumers wish to
know total Units Shipped balances for the last 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 last three Quarters of 1998,
broken out by U. S. Warehouse-State. While we could certainly present
this information by including each of the three Quarters individually in
our query, we will use the Tail() 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 last
three Quarters as columns and the U. S. Warehouse-States
as rows.
5.
Type the
following query into the Query pane:
-- MDX020-1, Use of Tail() Function in a Basic Query
SELECT
{TAIL([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 - Tail() Function in Basic
Query
We see Q2, Q3, and Q4
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 MDX020-1,
and place it in a meaningful location.
8.
Leave the
query open for the next section.
Handling of Numeric Value
Next, let's explore the handling of omitted numeric values,
among the other "default" treatments that the Tail() function dispenses.
We can see these treatments in action by simulating some quick scenarios.
1.
Within the
query we have saved as MDX020-1, replace the top comment line of the
query with the following:
-- MDX020-2, Use of Tail() Function - Omitted Numeric Expression
2.
Save the query
as MDX020-2, to prevent damaging MDX020-1.
3.
Remove the
comma (",") and numeral 3 from the following line of the query:
{TAIL([Time].[1998].Children, 3)} ON COLUMNS,The
Query pane
appears as shown in Illustration 2.
Illustration 2: The Query with Omitted Numerical Expression
» 
Comment and Contribute
