MDX Numeric Functions: The Max() Function - Page 2

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered, we will use the Max() function within queries that illustrate its operation. The intent is to demonstrate the use of Max() in a straightforward, memorable manner that efficiently illustrates its operation.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain. If you do not know how to access the SQL Server Management Studio in preparation for using it to query an Analysis Services cube (we will be using the sample Adventure Works cube in the Adventure Works DW Analysis Services database), please perform the steps of the following procedure, located in the References section of my articles index:

This procedure will take us through opening a new Query pane, upon which we will create our first query within the section that follows.

Procedure: Satisfy Business Requirements with MDX

Let’s construct a simple query to provide a conceptual “starting point” for illustrating the use of the Max() function. The idea is to generate a basic dataset that shows the concepts behind using the Max() function and to introduce some of the ways we can employ it effectively. Once we have accomplished our immediate goal in our first practice example, we will further evolve these concepts in meeting another business requirement in the procedure that follows it.

Procedure: Use the Max() Function to Generate a Simple “Maximum Value” in a Results Dataset

Let’s say that information consumers from the Adventure Works Logistics department, whose data is housed within the Adventure Works cube, come to us with a straightforward request: The consumers wish to see the top Internet Sales Amounts, based upon the organization’s Sales Territory Countries, for each of the Product Category groups offered by Adventure Works to its customers. They prefer to present the totals (leaving out nulls) for the member Product Categories sold by each Country, along with the highest values among those Country totals (in a column called “Top Sales”), on each row of the returned data set.

The basic Max() function involved will be housed within a calculated member we will call Top Sales. We will then employ the calculated member within a core query that presents the information in the manner requested. Our query will be constructed as described in the following procedure.

-- MDX062-1: Basic Use of MAX() Function

WITH

MEMBER

   [Sales Territory].[Sales Territory].[Top Sales]  

AS 

   'MAX(  { [Sales Territory].[Sales Territory].[Country].MEMBERS}, 

       [Measures].[Internet Sales Amount])'

SELECT

   { [Sales Territory].[Sales Territory].[Country].MEMBERS, 

      [Sales Territory].[Top Sales]  } ON AXIS(0),

   NON EMPTY   {[Product].[Product Categories].[Category].MEMBERS} ON AXIS(1)

FROM 

   [Adventure Works]

WHERE 

   ([Measures].[Internet Sales Amount])

Our query is simply expressing that we wish to retrieve the “total value of Internet Sales,” for each of our general Product Categories, by Sales Territory Country, alongside a repetition of the highest value within each row, within a column we label “Top Sales.” We are ignoring time – the consumers are aware that the cube contains data from several years, and they want the information to be based upon the cube as a whole for their present purposes. Moreover, they realize that, once they use the scenario we have assembled to verify effective operation of the Max() function (inside the “Top Sales” calculation we have built), they may jettison the individual Sales Territory Country columns, at least in reports they create later, based upon our example. (Parameterization, in an application like Reporting Services, becomes an easy objective, of course.)

The Query pane appears, with our input, as shown in Illustration 2.

The above query demonstrates the basic use of Max(), and accomplishes the objective of illustrating, in the simplest manner, how it works. The idea is to generate a dataset to activate the concepts in the minds of our client colleagues.

The Results pane is populated by Analysis Services, and the dataset, shown in Illustration 4, appears.

In the returned dataset, we see the members of the Country level of the Sales Territory dimension (Sales Territory hierarchy) appear upon the column axis, and the members of the Category level of the Product dimension (Product Categories hierarchy) appear upon the rows axis. We have retrieved, for each intersection, the non-null Internet Sales Amount values, together with the value of the calculated member Top Sales that corresponds with each Product Category row. This simple dataset provides a great “beginner” illustration of the output of Max(), as we can see that the function generates the correct maximum value that appears in the respective Country column to its left.

Our client colleagues express satisfaction with the contextual backdrop we have established for introducing the Max() function. We will employ the function again in our next steps to expand upon its use in the first example.