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

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered, we will use the Min() function within queries that illustrate its operation. The intent is to demonstrate the use of Min() 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 Min() function. The idea is to generate a basic dataset that shows the concepts behind using the Min() 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 Min() Function to Generate a Simple “Minimum 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 bottom 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 lowest values among those Country totals (in a column called “Bottom Sales”), on each row of the returned data set.

The basic Min() function involved will be housed within a calculated member we will call Bottom 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.

-- MDX063-1: Basic Use of MIN() Function
WITH
MEMBER
   [Sales Territory].[Sales Territory].[Bottom Sales]  
AS 
   'MIN(  { [Sales Territory].[Sales Territory].[Country].MEMBERS}, 
       [Measures].[Internet Sales Amount])'
SELECT
   { [Sales Territory].[Sales Territory].[Country].MEMBERS, 
      [Sales Territory].[Bottom 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 lowest value within each row, within a column we label “Bottom 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 Min() function (inside the “Bottom 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 Min(), 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 row axis. We have retrieved, for each intersection, the non-null Internet Sales Amount values, together with the value of the calculated member Bottom Sales that corresponds with each Product Category row. This simple dataset provides a great “beginner” illustration of the output of Min(), as we can see that the function generates the correct minimum 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 Min() function. We will employ the function again in our next steps to extend our understanding of its basic use.