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

Monday May 10th 2004 by William Pearson
Share:

Learn how to return a specified number of elements in a set in order. MSAS Architect Bill Pearson introduces the Head() function in the first of a set of three articles surrounding Subset functions.

This is the nineteenth 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 system 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, Basic Set Functions: The CrossJoin() Function, we explored the highly popular CrossJoin() function. We discussed the general purpose of the function, to allow us to produce all possible combinations of two sets, and cited its common use to "collapse" two or more dimensions onto a single axis for purposes of presenting multidimensional data in a two-dimensional matrix.

We began by introducing CrossJoin(), commenting upon its operation and touching upon performance considerations at a general level. We examined the syntax surrounding the function, and then began practice exercises to illustrate the operation of the function, within hypothetical scenarios where we described a business need. We then set about solving the need with a straightforward MDX query that exploited the CrossJoin() function. Finally, we briefly discussed the results datasets we obtained in the practice examples.

Introduction

In this lesson, we will begin 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. We will introduce the Head() function in this article, then follow it with the Tail() function in the subsequent article. The last of the three subset functions that we will investigate will be, unsurprisingly, the Subset() function. These three functions have much in common with regard to usage and operation, and covering them in this fashion 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 Head() function is to return the first specified number of elements in a set. We will introduce the Head() 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 the results datasets we obtain in the practice examples.

According to the Analysis Services Books Online, the Head() function "returns the first << Numeric Expression >> elements in a set." As we shall see, the order of the set elements is kept 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.

We will examine the syntax for the Head() function, looking 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.

Discussion

To expand our explanation of its general operation, the Head() function starts at the beginning of a set we designate. Its straightforward operation is to return the first n elements from the specified set, where n is the number that we specify.

Head() 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 also returns an empty set if the numerical expression provided is less than the number 1, as well as returning the full set of elements if we supply it a number that 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 Head().

Syntax

Syntactically, the set upon which we seek to perform the Head operation is specified within the parentheses to the right of Head. The syntax is shown in the following string.

`Head(<< 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 a numeric expression means that the function simply does its job with a default of 1. Numeric expressions that are 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.

The following example expression illustrates a use of the Head() function. 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 first three quarters of 1998.

The basic Head() function, which would specify the "first three quarters" portion of the required result dataset, would be constructed as follows:

`HEAD([1998].Children, 3)`

Assuming that we placed the Head() 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.

 Q1 Q2 Q3 Canada 4,186.22 4,949.88 4,196.32 Mexico 16.925.52 19,625.45 16,477.01 USA 25,358.40 26,093.90 24,912.75

Table 1: Results Dataset with Head() Defining Columns

The function has the effect of compactly expressing that we wish to display the Quarters as shown, from "beginning" (in their natural order), for three elements. As is probably obvious, Head() can be particularly useful in working with the Time dimension, as can other "subset" functions. In addition, obvious efficiencies can be obtained when it is used in conjunction with "family" functions, as with the .Children function above.

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

We will practice the use of the Head() 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. In each scenario we expose, we will discuss briefly the results we obtain to reinforce our overall understanding of the Head() function.

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
{[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.

Handling of Numeric Value

Next, let's explore the handling of omitted numeric values, among the other "default" treatments that the Head() function doles out. We can see these treatments in action by simulating some quick scenarios.

1. Within the query we have saved as MDX19-1, replace the top comment line of the query with the following:

`-- MDX019-2, Use of Head() Function - Omitted Numeric Expression`

2. Save the query as MDX19-2, to prevent damaging MDX19-1.

3. Remove the comma (",") and numeral 3 from the following line of the query:

`              {Head([Time].[1998].Children, 3)} ON COLUMNS,`

The Query pane appears as shown in Illustration 2.

Click for larger image

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 - Head() Function with Omitted Numerical Expression

5. Re-save the file as MDX19-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. Q1 only is returned, on the column axis, as a result.

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

`-- MDX019-3, Use of Head() Function -Numeric Expression < 1`

8. Save the query as MDX19-3.

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

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

The Query pane appears as shown 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 MDX19-3.

And so we see that a numerical expression less than 1 within the Head() function elicits the return of an empty cellset. Now, let's examine one last provision for oddball numeric input: 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 MDX19-3, replace the top comment line of the query with the following:

`-- MDX019-4, Use of Head() Function - Numeric Expression > Total Tuples in Set`

14. Save the query as MDX19-4.

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

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

The Query pane appears as shown 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), Head() returns the Full Set only.

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

We have explored examples of the behavior of the Head() 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.

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, very pleased with the metrics that we provided in our initial example, rewards us fully in the way that only information consumers can: they contact us with a more elaborate requirement.

It seems that Logistics would like to be able to present, within a single report, the difference in total Units Shipped and Units Ordered for the first 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:

```
-- MDX019-5, Use of Head() Function - Bonus Example
WITH

MEMBER [Measures].[Volume Delta] AS

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

SELECT

CROSSJOIN (

{[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 new 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 Head() function, once again, to retrieve the first 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 Q1 and Q2 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 MDX19-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 Head() 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. We demonstrated 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.

We will continue our "triptych" surrounding subset functions in our next article, where we will examine the Tail() function. The last of these three articles will focus on the Subset() function. The objective of covering the three subset functions in close proximity is to emphasize their commonalities from the perspective of usage and operation; once we have dedicated our attention to each in turn, we will be able to more finely distinguish among them for the attributes we need to leverage to meet specific business needs.

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

Share:
Home
Mobile Site | Full Site