Featured Database Articles

The Tail() Function

According to the Analysis Services
Books Online, the Tail()function "returns
the last << Numeric Expression >> elements in a set."
As we shall see, the order of the set elements remains intact within the
operation of the function. We control the range of the function by providing a
numeric expression, similar to the way we control the "reach"
we obtain in other MDX functions - and identical to the way we use the numeric
expression in the Head() function that we explored in our previous
article.

We will examine the syntax for the Tail()
function, then look at its behavior based upon different numeric expressions
we might provide. Next, we will undertake practice examples built around
hypothetical business needs that illustrate logical uses for the function. We
will also experience hands-on the manner in which Tail() handles the
numeric expression scenarios we have explored in the Discussion
and Syntax sections.

Discussion

To expand our explanation of its general operation, the Tail()
function starts at the end of a set we designate. Its
straightforward operation is to return the n elements from the
specified set, where n is the number that we specify, counting backward
from the end of the set, and preserving set order.

In a manner similar to Head(), as we saw in our
previous article, Tail() manages the absence of a specified numeric
expression by "defaulting" to the return of a single element.
In addition to substituting 1 in cases where we do not specify a numeric
expression, it again replicates the action of Head(), and returns an empty
set if the numerical expression provided is less than the number 1.
Further, Tail() returns the full set of elements, in a way again identical
to Head(),if the number we supply it is greater than the
number of the elements of the specified set.

Let's look at some syntax illustrations to further clarify
the operation of Tail().

Syntax

Syntactically, the set upon
which we seek to perform the Tail operation is specified within the
parentheses to the right of Tail, just as we saw with the Head()
function in our previous article. The syntax is shown in the following string.

Tail(<< Set >> [,<< Numeric Expression >>])

We follow the set
specification with a comma, which is, in turn, followed by the numeric
expression. As we have discovered, the omission of the numeric
expression means that the function simply does its job with a default of 1.
For a scenario where the numeric expression is greater than the
number of tuples in the set we specify, the original set itself is
returned. Moreover, the input of a number less than 1 as the numeric
expression results in a "default answer" of empty set. These "defaults"
for "numeric expression management" are identical to those we found
in the Head() function.

The following example
expression illustrates a use of the Tail() function, within the context
of an expression we used in discussing the syntax of the Head() function
in our last article. This will illustrate the similarities in the construction
of the functions, while exposing the differences in the datasets that they
return.

Let's say that a group of corporate-levelinformation consumers within the FoodMart organization wish to see the total
Profits by U.S.Warehouse-Country for the last three Quarters
of 1998.

The basic Tail()
function, which would specify the "last three Quarters"
portion of the required result dataset, would be constructed as follows:

Tail([1998].Children, 3)

Assuming that we placed the Tail() function within
the column axis definition of a query, and the Warehouse-Country
information defined the row axis, our returned dataset would resemble that
shown in Table 1.

Q2

Q3

Q4

Canada

4,949.88

4,196.32

3,645.54

Mexico

19,625.45

16,477.01

14,509.69

USA

26,093.90

24,912.75

29,348.79

Table 1: Results Dataset, with Tail() Defining Columns

Just as we saw with the Head() function in our
previous session, Tail() has the effect of compactly expressing that we
wish to display the Quarters as shown, from "the end" of the
set (the Quarters of 1998), in their natural order, for three
elements "distance."

As is probably obvious, Tail() can be particularly
useful in working with the Time dimension, as can other "subset"
functions, including Head(), as we saw in our last article (using the
very same example as above, for comparative purposes). In addition, obvious
efficiencies can be obtained when it is used in conjunction with "family"
functions, as with the .Children function above. More compact, reusable
coding is often the result.

We will practice the use of the Tail() function in
the section that follows. Moreover, we will take a look at the way in which
the function manages omitted numeric expressions, as well as values that
exceed the number of tuples in the specified set, just as we did in the
previous article with the first of the subset functions, Head(). In each
scenario we expose, we will discuss briefly the results we obtain to reinforce
our overall understanding of the Tail() function.