In this level, we introduce Head(), a function of the set variety. As we have seen elsewhere in this Stairway, set functions construct sets in several ways, returning data by traversing locations (both absolute and relative) among dimensions, hierarchies and levels. Like their kindred member (and other) functions, set functions are crucial to the navigation of the multidimensional structures that make their home in Analysis Services. Moreover, set functions are essential to the very capability of MDX queries to deliver the data we request, because set expressions, as most of us are aware, define the axes involved.

As we'll see in this article, the general purpose of the Head() function is to return the first specified number of elements in a set. We will introduce Head() with comments upon its operation, and then, as in the other levels of this Stairway, we will examine the syntax involved in using the function. We'll then be in a position to undertake some hands-on examples of uses of the function in practice examples, discussing the results we obtain with each as a part of the exercise.

The Head() Function

According to the Microsoft Developer Network (MSDN), the Head() function "returns the specified number of tuples from the beginning of the specified set." As we'll learn, the order of the set elements is kept intact within the operation of the function as it returns the first << Count >> elements. We control the range of the function by providing a numeric expression, in a similar manner to the way we control the "reach," or "span," we obtain in other MDX functions.

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_Expression >> [ ,Count ] ) 

We follow the set specification with a comma, which is, in turn, followed by the count, a numeric expression. The omission of a numeric expression means that the function simply does its job with a default of 1. Moreover, when numeric expressions that are greater than the number of tuples in the set are specified, the original set itself is returned. Finally, 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 Adventure Works organization wish to see the total Reseller Sales Amount by Reseller for the first ten resellers in the cube.

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

  Head ([Reseller].[Reseller].CHILDREN, 10)

Assuming that we placed the Reseller Sales Amount measure within the column axis definition, and the Head() function within the row axis definition, of a query, our returned dataset would resemble this:

Illustration 1: Results Dataset with Head() Defining Rows

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

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 confirm our overall understanding of the Head() function.

The Basics

To reinforce our understanding of the basics we have covered so far, let's 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 how Head() works in a straightforward manner.

Using Head() in a Basic Query

As is usually the case within the Levels of the Stairway to MDX, , we'll rely upon the MDX Query Editor in SQL Server Management Services ("SSMS"), as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain. We will assume, for purposes of our practice example, that we have received a call from corporate Logistics at a hypothetical client, the Adventure Works Cycles organization, needing some shipping metrics. These information consumers wish to know total Reseller Order Quantity shipped over the first three quarters of calendar year 2007, by Reseller Business Type.

Let's set up the query to return the Reseller Order Quantity for all quarters first, and then apply the Head() function to deliver the measure for the specified three quarters.

1. Type (or copy and paste) the following query into a blank Query pane:

-- SMDX014-001 Prep for use of the Head() Function in a Basic Query
SELECT
   
   {DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
      [Date].[Calendar].[Calendar Quarter], SELF)} ON AXIS(0),
   {[Reseller].[Business Type].CHILDREN} ON AXIS(1)
FROM
   [Adventure Works]
WHERE 
   ([Measures].[Reseller Order Quantity])

The preparatory query appears within the Query pane.

Illustration 2: The Preparatory Query in the Query Pane

2. Select Query --> Execute from the Main Menu, as shown.

Illustration 3: Select Query - Execute from the Main Menu

The Results pane is populated with the dataset.

Illustration 4: The Query Results – Preliminary "Core" Query Results Dataset

We see that we have obtained a summary, by Quarter for Calendar Year 2007. This will serve as the "starting point" for asking for "the first three quarters," at least in this simple scenario. We have applied the Descendants() function as one approach to working around the existing hierarchy of Year – Half Year (Semester) – Quarter within the preparatory step. This will enable us to meet the client need in a straightforward manner with the Head() function in the next steps, where we will make a couple of small adjustments.

3. Within the SELECT clause, modify the existing Columns / Axis(0) specification from the existing syntax:

{DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
      [Date].[Calendar].[Calendar Quarter], SELF)} ON AXIS(0),

… to the following syntax:

{
    HEAD(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        3) 
 }  ON AXIS(0),

Note that we are merely enclosing the pre-existing selection of calendar quarters in the Head() function on the Columns axis. The complete query should appear, with modifications, as follows:

-- SMDX014-001-1 Use of Head() Function in a Basic Query
SELECT
   
   {
    HEAD(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        3) 
 }  ON AXIS(0),
   {[Reseller].[Business Type].CHILDREN} ON AXIS(1)
FROM
   [Adventure Works]
WHERE 
   ([Measures].[Reseller Order Quantity])

We have simply changed the Column specification to return "the first three quarters in calendar year 2007."

The Query pane appears, with our modifications in place.

Illustration 5:  Modified Query in the Query Pane: Applying Head() Function

4. Click the Execute (!) button in the toolbar, once again.

Analysis Services populates the Results pane, and the smaller dataset appears, as shown.

Illustration 6: Final Result Dataset: "Leading Three" (Chronologically) Quarters for Reseller Types

We obtain confirmation from our client colleague that the dataset retrieved meets the business need he has communicated: a summary of the total Reseller Order Quantity shipped over the first three quarters of calendar year 2007, by Reseller Business Type.

5. Save the query by selecting File Save MDXQuery1.mdx As …, naming the file SMDX014-001-1.mdx, and placing it in a convenient location.

Having gained some basic exposure to the Head() function, let's explore additional considerations surrounding its use.

Handling of the Numeric Value

Next, let's explore the handling of omitted numeric values, among 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 SMDX014-001-1, replace the top comment line of the query with the following:

  -- SMDX014-002, Use of Head() Function – Omitted Numeric Expression

2. Save the query as SMDX014-002, to prevent damaging SMDX014-001-1.
3. Remove the comma (",") and numeral 3 from the fourth and fifth line, respectively, of the Column axis specification of the query:

{
    HEAD(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        3) 
 }  ON AXIS(0),

… so that the modified Column axis specification looks like this: 
{
    HEAD(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF)
        ) 
 }  ON AXIS(0),

The affected characters in the query – those to be removed – appear circled in the illustration below:

Illustration 7:  Eliminate the Comma (",") and "3" from the Head() Function

The Query pane appears, after the specified deletions, as depicted.

Illustration 8: The Query with Omitted Numeric Expression

4. Click the Execute (!) button in the toolbar, once again.

Analysis Services repopulates the Results pane, and an even smaller dataset appears, as expected.

Illustration 9: Result Dataset – Head() Function with Omitted Numeric Expression

5. Re-save the file as SMDX014-002.
6. Leave the query open for the next step.

We see, in the foregoing result dataset that the "default" value assumed by the Head() function, in the absence of a specified numeric expression, is 1. Q1 only, therefore, is returned, on the column axis, as a result.

Let's extend our work with the numeric function to the use of a negative number.

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

-- SMDX014-003, Use of Head() Function – Numeric Expression < 1

8. Save the query as SMDX014-003, to prevent unintentional modification to SMDX014-002.
9. With the Column axis specification of the query, which currently appears as shown here:

{
    HEAD(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF)
        ) 
 }  ON AXIS(0),

…Add a comma (",") at the end of the fourth line of the Column axis specification of the query (after the syntax "SELF)", and add a "-1" on the fifth line of the Column axis specification, just before the right parenthesis ( ")" ) that currently occupies line five alone, so that the modified Column axis specification looks like this:

{
    HEAD(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        -1) 
 }  ON AXIS(0),

The affected characters in the query – those we have added – appear circled in the illustration below:

Illustration 10:  Adding a "Less-than-1" Numeric Expression to the Head() Function …

The full Query pane, after the specified modifications, appears as shown:

Illustration 11: The Query with Modified Numeric Expression

8. Right-click any point within the Query pane.
9. Select Execute from the context menu that appears, as depicted.

Illustration 12: Executing the Query via the Context Menu …

Analysis Services attempts to populate the Results pane, but this time we are confronted with an empty cellset.

Illustration 13: Empty Cellset – As Expressed in the SSMS MDX Query Editor

10. Re-save the file as SMDX014-003.
11. Leave the query open for the next step.

And so we see, in the foregoing result dataset (or, more significantly, in the absence of same) that a numeric expression "less than 1" within the Head() function elicits the return of an empty cellset. This matches the expected behavior we noted in our general syntax discussion earlier.

Now, let's examine one last provision for "unconventional" numeric input: What happens when we input a number that is higher than the total number of tuples in the specified set?

12. Within the query we have saved as SMDX014-003, replace the top comment line of the query with the following:

-- SMDX014-004, Use of Head() Function – Numeric Expression > Total No. Tuples in Set

13. Save the query as SMDX014-004, to prevent unintentional modification to SMDX014-003.
14. Replace the numeral -1 with the numeral 6, in the following line of the existing query:

{
    HEAD(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        -1) 
 }  ON AXIS(0),

… so that the modified Column axis specification looks like this:

{
    HEAD(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        6) 
 }  ON AXIS(0),

The affected character in the query – the one we have modified – appears circled in the illustration below:

Illustration 14:  Intentionally Exceeding the Known Number of Tuples in a Set within the Numeric Expression of the Head() Function…

The full Query pane, after our latest modification, appears as shown:

Illustration 15: The Query with Modified Numeric Expression

15. Press the F5 button.

Analysis Services populate the Results pane, generating the following dataset:

Illustration 16: Results of the Modified Numeric Expression in the Head() Function

16. Re-save the file as SMDX014-004.

We note immediately that (as we might think rather intuitive), even though the numeric 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, and nothing more.

Having explored examples of the behavior of the Head() function under various scenarios of numeric expression input for a specified set, let's construct a query to meet another hypothetical business need, and to activate what we have learned further.

Another Example of Head() at Work

Let's say that the Logistics Department, very pleased with the illustrations that we have provided in the foregoing examples, rewards us fully - in the way that information consumers so often do: they contact us with a more elaborate requirement.

It seems that Logistics would like to be able to present, within a single report, an "adjusted" Internet Sales gross profit. The calculation is simple: They want to present the gross profit value as it is currently calculated (Internet Sales Amount minus Internet Total Product Cost), but with Internet Freight Cost also subtracted, as the organization began offering free shipping to all customers in calendar year 2007.

Our client colleagues want to generate Internet Gross Profit minus Internet Freight Cost for the first two quarters of calendar year 2007, preferring that we label this derived metric "Adjusted Internet Gross Profit." They tell us that they wish to see all three measures totaled side-by-side, for easy verification of the new calculation amount, by customer country, at least in this request. (The ultimate objective with this client is to perform parameterization in Reporting Services reports they generate based upon test examples such as this one.)

Let's return to the MDX Query Editor to craft a new query to handle this request.

1. Select File --> New from the main menu.
2. Select Query with Current Connection from the cascading menu that appears, as depicted.

Illustration 17: Creating a New Query with Current Connection …

3. Type (or copy and paste) the following into the new blank Query pane that appears:

-- SMDX014-005, Use of Head() Function – Bonus Example
WITH 
MEMBER
   [Measures].[Adjusted Internet Gross Profit] 
AS
   [Measures].[Internet Gross Profit] - [Measures].[Internet Freight Cost]
SELECT
   CROSSJOIN 
      (
        { HEAD(
            DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
               [Date].[Calendar].[Calendar Quarter], SELF), 
2) },  
 
 {[Measures].[Internet Gross Profit], [Measures].[Internet Freight Cost], 
    [Measures].[Adjusted Internet Gross Profit]}
      )  ON AXIS(0),
   {[Customer].[Customer Geography].[Country].MEMBERS} ON AXIS(1)
FROM
   [Adventure Works]

Our input appears within the Query pane, as shown.

Illustration 18: The Query in the Query Pane

In the query above, we employ the WITH keyword to create a calculated measure - our new Adjusted Internet Gross Profit measure. We apply the Descendants() function, once again, as an approach to working around the existing hierarchy of Year – Half Year (Semester) – Quarter, to enable us to meet the client need to present "the first two quarters of calendar year 2007" in a straightforward manner, via the Head() function, once again, for our column axis. Moreover, we exploit the CrossJoin() function to order our three measures (two base and one calculated) under each Quarter we present.

4. Select Query --> Execute from the Main Menu, as we did in earlier steps.

The Results pane is populated with the dataset.

Illustration 19: The Query Results Dataset

We see that we have obtained a summary, by Quarter, for Calendar Year 2007, of "the first two quarters," courtesy of the Head() function. The base and calculated measures are presented, as requested, by customer country. Our client colleagues express their satisfaction with our handiwork.

5. Save the query as SMDX014-005.mdx.
6. Exit the Query Editor when ready.

We will gain practical exposure to additional "subset" functions in subsequent levels of Stairway to MDX.

Summary

In this article, 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 Head() function, within which we acted to meet illustrative business requirements. Moreover, we demonstrated, in detail, the manner in which Head() handles various numeric expression input scenarios. Throughout the practice examples, we briefly discussed the results datasets we obtained via our use of the Head() function, together with other surrounding considerations.