MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function

Monday Jun 14th 2004 by William Pearson
Share:

Learn how to return a subset from the end of a set. MSAS Architect Bill Pearson introduces the Tail() function, in the second of a three articles surrounding Subset functions.

About the Series ...

This is the twentieth article of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements needed for getting the most out of the lessons included, please see the first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

What We Accomplished in our Last Article

In the last article of the series, Subset Functions: The Head() Function, we introduced the Head() function, whose general purpose is to return a specified number of elements from the beginning of a set, preserving natural order. We commented upon the operation of the function, and then examined its syntax. Next, we undertook practice examples with the function, based upon hypothetical business requirements. Our practice included the creation of queries to meet illustrative business needs, as well as demonstrations of the manner in which the Head() function handles various numeric expression input scenarios. Throughout the practice examples, we briefly discussed the results datasets we obtained with regard to the Head() function, together with other surrounding considerations.

Introduction

In this lesson, we continue a "triptych" of articles that expose set functions that deal specifically with subsets - that is, each function returns a subset of a larger set as part of its operation. Having covered the Head() function in the previous article, we will introduce the Tail() function in this article, then follow it with the Subset() function in the next article. As we mentioned in our last session, these three functions have much in common with regard to usage and operation; covering them in close proximity will allow us to more finely distinguish among them, to exploit the attributes we can leverage to meet specific business needs.

The general purpose of the Tail() function is to return the last specified number of elements in a set. We will introduce the Tail() function, commenting upon its operation, and then we will:

  • Examine the syntax surrounding the function;
  • Undertake illustrative examples of the uses of the function in practice exercises;
  • Briefly discuss of the results datasets we obtain in the practice examples.

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-level information 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.

NOTE: For information surrounding the .Children function, see MDX Member Functions: The "Family" Functions.

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.

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

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

The Results pane is populated, and the dataset shown in Illustration 3 appears.


Illustration 3: Result Dataset - Tail() Function with Omitted Numerical Expression

5. Re-save the file as MDX020-2.

6. Leave the query open for the next step.

We see that the "default" value assumed by the function, in the absence of a specified numeric expression, is 1. Q4 only is returned, on the column axis, as a result.

7. Within the query we have saved as MDX020-2, replace the top comment line of the query with the following:

-- MDX020-3, Use of Tail() Function 
  -Numeric Expression < 1

8. Save the query as MDX020-3.

9. Add a comma (",") after .Children, then a space, and then the numeral -1, into the following line of the query:

  {TAIL([Time].[1998].Children)} 
  ON COLUMNS,

The Query pane appears as depicted in Illustration 4, with the inserted coding circled in red.


Illustration 4: The Query with Numerical Expression Less Than 1

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

We receive a message box, as shown in Illustration 5, stating that the Sample Application is unable to display the opened cellset - essentially because the dataset is empty.


Illustration 5: Message - Empty Cellset

11.  Click OK.

The message box closes, and we are confronted with the empty cellset (perhaps only briefly), as expressed by the Sample Application (see Illustration 6.)


Illustration 6: Empty Cellset as Expressed by the Sample Application

12.  Re-save the file as MDX020-3.

And so we see that a numerical expression less than 1 within the Tail() function results in the return of an empty cellset. Now, let's examine one last provision for possible numeric input, as we did for the Head() function in our previous article: what happens when we input a number that is higher than the total number of tuples in the specified set?

13.  Within the query we have saved as MDX020-3, replace the top comment line of the query with the following:

-- MDX020-4, Use of Tail() Function - Numeric Expression > Total Tuples in Set

14.  Save the query as MDX020-4.

15.  Replace the numeral -1 with the numeral 6, in the following line of the existing query:

              {TAIL([Time].[1998].Children, -1)} ON COLUMNS,

The Query pane appears as depicted in Illustration 7, with the inserted coding circled in red.


Illustration 7: The Query with Numerical Expression Larger than Set

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

The Results pane is populated, and the dataset shown in Illustration 8 appears.


Illustration 8: Results of the Modified Numeric Expression

We see that, even though the numerical expression that we input exceeds the number of tuples in the specified set (only four Quarters exist in any single Year), Tail() returns the Full Set only, exactly as we saw with the Head() function in the previous article.

17.  Re-save the file as MDX020-4.

We have explored examples of the behavior of the Tail() function under various scenarios of numeric expression input for a specified set. We saw that the function manages potential input by providing a "default" numeric expression to drive its behavior, with regard to the results it produces. We also pointed out similarities in operation (with regard to numeric expressions) between the Head() and Tail() functions.



Another Example



Let's construct a query to meet another hypothetical business need, and to practice what we have learned further. Let's say that Logistics Department, returns with the more elaborate requirement we saw in our examination of the Head() function.



To reiterate with the focus on the Tail() function, Logistics would like to be able to present, within a single report, the difference in total Units Shipped and Units Ordered for the last two Quarters of 1998, preferring that we label this derived metric Volume Delta. They want to see the measures for the Washington Warehouse-Cities only, at least in this request. Further, they want to see this information presented in such a way that the Units Shipped, Units Ordered and Volume Delta values are side-by-side, for easy verification of the new variance amount.



We will return to the MDX Sample Application, creating a new query to handle this request.



1. Select File --> New to create a new MDX query.

A blank Query pane appears.

2. Type the following query into the Query pane:


-- MDX020-5, Use of Tail() Function - Bonus Example
WITH 

   MEMBER [Measures].[Volume Delta] AS

     '[Measures].[Units Ordered] -  [Measures].[Units Shipped]'

SELECT

   CROSSJOIN (

       {TAIL([Time].[Year].[1998].Children, 2)},

           {[Measures].[Units Ordered], [Measures].[Units Shipped], 

                [Measures].[Volume Delta]} ) ON COLUMNS,

       {[WAREHOUSE].[Country].[USA].[WA].Children} ON ROWS

FROM

    [Warehouse]

In the query above, we use the WITH keyword to create a calculated measure, to act as our newly derived Volume Delta measure. In addition, we exploit the CrossJoin() function to order our three measures under each Quarter we present. Finally, we use the Tail() function, once again, to retrieve the last two Quarters of 1998 for our column axis.

NOTE: For details concerning calculated measures, see my articles index at Database Journal.com. For information about the CrossJoin() function, see my article Basic Set Functions: The CrossJoin() Function.

3. 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 9 appears.


Illustration 9: Result Dataset

We see Q3 and Q4 populating the columns across, together with the Units Shipped, Units Ordered and Volume Delta measures aligned under each. In addition, the Washington Warehouse-Cities appear in the row axis, as required by the information consumers in Corporate Logistics.

4. Select File -> Save As, name the file MDX020-5, and place it in a meaningful location.

5. Close the Sample Application when ready.

Summary ...

This article served as the beginning of a set of three articles surrounding subset functions. We introduced the Tail() function, whose general purpose is to return a specified number of elements in a set, preserving natural order. We commented upon the operation of the function, and then examined its syntax.

We undertook practice examples with the function, within which we acted to meet illustrative business requirements. In our example set, we intentionally replicated, to a large extent, the requirements we simulated in working with the Head() function in our last article, so as to compare the Tail() and Head() functions, and to note their similarities in operation, as well as to contrast the results datasets they returned.

We demonstrated the manner in which the Tail() function handles various numeric expression input scenarios, again, in contrast to the Head() function. Throughout the practice examples, we briefly discussed the results datasets we obtained with regard to the Tail() function, together with other surrounding considerations.

We will continue our "triptych" surrounding subset functions in our next article, where we will examine the Subset() function. We will again emphasize the commonalities between SubSet() and the Head() and Tail() functions, from the perspective of usage and operation. We will thus conclude our examination of the group of three subset functions, having dedicated our attention to each in turn, then comparing / contrasting them in order to finely distinguish among them for the particular characteristics we need to meet specific business needs.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved