MDX Clauses and Keywords: Use HAVING to Filter an Axis

Monday Mar 5th 2007 by William Pearson
Share:

BI Architect Bill Pearson overviews the new HAVING clause, and then leads hands-on practice with its use in filtering an axis.

About the Series ...

This article is a member 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 for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.

Overview

In this lesson, we will explore the HAVING clause, which makes its debut in the MDX language with Analysis Services 2005. As we shall see, HAVING does not endow us with any capabilities we might not have achieved in other ways, with approaches that were fully available before Analysis Services 2005. What HAVING offers us is a way to apply a filter that is, perhaps, more easily understandable for a reader. More importantly, it offers us another means of manipulating context from the perspectives of our queries. Such manipulation can mean more optimal processing, and the sort of finesse that we can apply easily to an existing query structure – often without a rewrite, and more as an addition to syntax. These are the kinds of scenarios within which one can easily envision parameterization constructs, among other extended concepts in the Analysis Services and the reporting layers within the integrated business intelligence solution.

Along with an introduction to the HAVING clause, this lesson will include:

  • an examination of the syntax and positioning involved in the use of the clause;
  • illustrative examples of uses of the clause within practice exercises;
  • comparative approaches in obtaining similar results with the MDX FILTER() function;
  • a brief discussion of the MDX results obtained within each of the practice examples.

The HAVING Clause

Introduction

As we put our growing MDX knowledge to work within business scenarios, we find that, beyond the knowledge of query basics and the utilization of MDX functions, the inevitable motivation emerges to optimize our expressions and queries. The concept of context comes into play, from various perspectives, within the queries we assemble. Webster’s Dictionary defines “context” as “the part or parts of something written or printed, as of Scripture, which precede or follow a text or quoted sentence, or are so intimately associated with it as to throw light upon its meaning.” More germane, perhaps, to the concept of context within a query and calculation language like MDX, is the definition as found within the Free On-line Dictionary of Computing: “That which surrounds, and gives meaning to, something else. In a grammar it refers to the symbols before and after the symbol under consideration.”

Nowhere is the concept of context more apropos than within the multidimensional world. From the perspective of MDX (where execution and session context are of primary importance) many things determine context - some implicit and some explicit; some based upon how invalid or missing data / members are interpreted; some surrounding which of multiple properties associated with a cell are considered within the query. Data types alone can be a significant consideration, requiring precise control within MDX calculations to ensure that the desired end result is generated.

Many factors come into play when we consider context in general, a great number of which we examine throughout the series from specific points of view. At the heart of defining what exists within individual cells amid the juxtaposition / intersection of members of axis / slicer specifications ( with each axis’ sets often being dependent upon the performance of calculations with cells whose existence is based upon regular or calculated members) lies the concept of resolution order. Understanding the way in which queries are resolved and calculations are carried out is key to optimization, as well as to the intended operation of our applications – particularly those created based upon context and other considerations within Analysis Services 2000, because of the changes that come along in Analysis Services 2005.

While the subject of context itself is both wide and deep, a good understanding of resolution order begins with an understanding of the primary execution stages of an MDX query. These ordered stages consist of the following resolutions:

  • the FROM clause;
  • the WHERE clause;
  • the WITH clause – Named Sets;
  • the tuples on each axis.

Once these four resolution stages are accomplished, a final execution stage – wherein calculation of the cells returned within the intersections specified among the axes transpires - takes place. Within this execution stage, a couple more resolutions take place, as dictated by the design of the query: the resolution of NON EMPTY intersections and the resolution of the HAVING clause.

This focus of this article begins, as it were, within this last execution stage, with the HAVING clause. As we shall see, HAVING affords us a means of applying a “post NON EMPTY” filter, as well as an alternate means of applying filtering in general. The advantages that accrue depend upon the backdrop within which we seek to employ the clause, and can include more optimal performance of the query involved, together with the more cosmetic effects of simpler maintenance and more intuitive coding. We will examine the syntax surrounding the HAVING clause after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a couple of scenarios, constructed to support simple, hypothetical business needs that illustrate a use for the clause. This will afford us an opportunity to explore some of the basic options that HAVING can offer the knowledgeable user. Hands-on practice with HAVING, where we will create queries that employ the clause, will help us to activate what we have learned in the Discussion and Syntax sections.

Discussion

As we have intimated, the HAVING clause is particularly useful in cases where we need to insert a means of filtering within our query so as to take advantage of the fact that NON EMPTY logic has already been applied. We will examine a couple of instances of this within the practice session that follows. While HAVING does not, as we have mentioned, enable us to do anything, from the standpoint of sheer filtering, that we might not have been able to do before the advent of the HAVING clause in Analysis Services 2005 (as we shall also see within our examples, it simply supplies an alternative means of accomplishing something that we can also accomplish via the FILTER() function), it does allow us to perform such filtering subsequent to the application of NON EMPTY in an easy manner. Moreover, as we shall see, the HAVING clause not only offers us another means of optimizing queries, but it can help us to make them easier to read, and to be maintained by, others.

Let’s look at syntax specifics to further clarify the operation of HAVING.

Syntax

We typically employ the HAVING clause at the axis level. The HAVING expression is applied against each of the axis’ tuples – and therefore operates within the scope of the axis. The following snippet shows an example of placement of the HAVING clause within a row axis:


NON EMPTY
   {[Sales Territory].[Sales Territory Group].CHILDREN}
    
       HAVING [Measures].[Reseller Sales Amount] > 0 
   ON AXIS(1)       

In this case, the “filter-effect” of the HAVING clause is enacted within the scope of the row axis, as is relatively intuitive by its placement. The expression in this case is applied to [Sales Territory].[Sales Territory Group].CHILDREN, combining [Sales Territory].[Sales Territory Group].CHILDREN with the context it enforces.

We see the above within a larger query in the example shown immediately below:


-- MDX053: Syntax Sample with HAVING Clause in Row Axis
SELECT 
CROSSJOIN(
   {[Date].[Calendar].[Calendar Quarter].[Q1 CY 2004].CHILDREN},
   
      {[Measures].[Reseller Sales Amount] } )
   
    ON AXIS(0),
NON EMPTY
   {[Sales Territory].[Sales Territory Group].CHILDREN}
    
       HAVING [Measures].[Reseller Sales Amount] > 0 
   ON AXIS(1)
       
FROM 
   [Adventure Works]
WHERE
   ([Reseller].[Reseller Type].[Business Type].[Value Added Reseller])

The above query would retrieve a result set similar to that shown in Illustration 1.


Illustration 1: Results Dataset Retrieved via Query Employing HAVING Clause

In effect, too, the placement of the HAVING clause would result in the same outcome as that of the following query, where the FILTER() expression is substituted for HAVING in the above:


-- MDX053: Syntax Sample Substituting FILTER() Function for HAVING
SELECT 
CROSSJOIN(
   {[Date].[Calendar].[Calendar Quarter].[Q1 CY 2004].CHILDREN},
   
      {[Measures].[Reseller Sales Amount] } )
   
    ON AXIS(0),
NON EMPTY
   FILTER(
   
      {[Sales Territory].[Sales Territory Group].CHILDREN},
    
         [Measures].[Reseller Sales Amount] > 0 
       
      )
   ON AXIS(1)
       
FROM 
   [Adventure Works]
WHERE
   ([Reseller].[Reseller Type].[Business Type].[Value Added Reseller])

Other placements of HAVING are, of course, possible, and the HAVING clause can leverage functions, in combination with the relevant tuples, as we shall see in a subsequent article.

NOTE: For more detail surrounding the FILTER() function, see Basic Set Functions: The Filter() Function, a member of my Database Journal MDX Essentials series.

We will get some hands-on practice with the HAVING function in the section that follows.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered, we will put the HAVING clause to work within a couple of queries that illustrate its operation, focusing, within this article, upon scenarios where we use HAVING to act as a filter within an axis context. We will undertake our practice exercises within scenarios that place HAVING within the context of meeting basic requirements similar to those we might encounter in our respective daily environments. The intent is to demonstrate the use of the function in a straightforward, memorable manner.

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:

Prepare MSSQL Server Management Studio to Query Analysis Services

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 assume, for purposes of our practice example, that we have received a request from representatives of our client, the Adventure Works organization. Having implemented the integrated Microsoft business intelligence solution, including MSSQL Server, Analysis Services, Integration Services, Reporting Services, and other components for the client earlier in the operating year, we have been called upon to assist in the continuing rollout of the various components throughout the organization. In the present case, a group of report authors in the Operations department has requested assistance in generating some simple values for a specific analysis task that has been discussed at a recent meeting with the organization’s controllers.

The Operations authors are aware that the particular need that they are currently expressing will manifest itself in recurring situations as they work to meet the daily requirements of the Adventure Works information consumers, as well as to support business requirements gathering efforts as Analysis Services is implemented throughout other operating entities of the organization. The authors have posed a couple of scenarios wherein they wish to perform filtering within the scope of an axis, and, while we have already imparted knowledge of the use of the FILTER() function in meeting previous business needs, we feel that this is an appropriate opportunity to introduce an alternative option.

In a brief discussion with our client colleagues, we provide introductory details (as we have in the sections above) surrounding the HAVING clause. We start by using a whiteboard to acquaint the group with the syntax involved in its use, in an example similar to the one we provided in the Syntax section above. Our next steps will be to provide practice with the use of HAVING within a couple of query scenarios wherein we parallel our efforts in obtaining the desired effect, using first the FILTER() function, and then the HAVING clause, in a manner similar to our syntax example above. Through the construction and execution of queries that illustrate the characteristics of HAVING, we hope to reinforce the understanding of the authors, so as to enable them to judiciously use the clause within MDX queries for various analysis and reporting needs.

The authors group tells us that the values under immediate consideration involve Reseller Order Quantity and Reseller Sales Amount, but, as is typically the case in our collaborative sessions, they want to develop an approach that will work equally well with other measures that have similar analysis potential. (They often derive parameterized queries in Reporting Services from the basic MDX syntax we assist them in assembling, and can thus create self-serve reports that allow information consumers to dictate what measure they wish to analyze, and myriad other options, at run time). The current desired end is to simply return the Reseller Order Quantity and Reseller Sales Amount values, as captured within the Adventure Works cube, for the Product Category Components, for the Calendar Year 2003.

Our client colleagues further tell us that they wish to see only Value Added Resellers on the row axis of the returned dataset, with empties suppressed. Moreover, they wish to see only Value Added Resellers whose Reseller Order Quantity value exceeds ten units. (It is this final part of the specification, the need to filter the members of the row axis, that serves as the basis for our reasoning that this represents a great opportunity for demonstrating the use of HAVING as a substitute for the FILTER() function.)

We offer to illustrate the use of HAVING to meet the immediate need, proposing to present a couple of examples, each subsequent to a parallel case where we use the FILTER() function to achieve the same end, to solidify the authors’ new understanding, as well as to assist in rounding their overall MDX “vocabularies.” We then set about the assembly of our examples to illustrate uses of HAVING.

Procedure: Use the HAVING Clause as a Substitute Means of Filtering a Rows Axis Specification

We will first construct a simple core query to support the business requirements in all except the specific focus of our lesson, the use of the HAVING clause to enforce filtering within the row axis. Once the core query is in place, we will apply filtering via the FILTER() function, so as to have a baseline dataset that meets all the requirements. We will then substitute the HAVING clause for the FILTER() function, re-execute the query, and verify that the retrieved data reflects the same desired, filtered result.

1. Type (or cut and paste) the following query into the Query pane:


-- MDX053-001-1 Core Query 
SELECT 
{[Measures].[Reseller Order Quantity], [Measures].[Reseller Sales Amount] } 
   ON AXIS(0),
NON EMPTY
{[Reseller].[Reseller Type].[Business Type].[Value Added Reseller].CHILDREN} 
    ON AXIS(1)
FROM 
   [Adventure Works]
WHERE
   ([Product].[Product Categories].[Category].[Components], 
      [Date].[Calendar].[Calendar Year].[CY 2003] )

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


Illustration 2: Our Initial Query in the Query Pane ...

The above query returns the Reseller Order Quantity and Reseller Sales Amount for the Components Product Category sold by each Value Added Reseller in Calendar Year 2003. As we noted earlier, this core query gets us most of the distance in meeting the expressed business requirement. (Once we have a core superset as a backdrop, we will enact a filter, through the two approaches we have discussed, to “fine tune” the query to produce the precise dataset our client colleagues have requested, restricting the data returned to Value Added Resellers within the requested time frame that had Reseller Order Quantities of greater than ten.)

2.  Execute the query by clicking the Execute button in the toolbar, as shown in Illustration 3.


Illustration 3: Click Execute to Run the Query...

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


Illustration 4: Results Dataset (Partial View) – Core Query Before Filtering

In the partial view of the returned dataset, we see that the core query we have constructed accomplishes the intended purpose - generating the Reseller Order Quantity and Reseller Sales Amount values, for the Product Category Components, for the Calendar Year 2003. Moreover, only Value Added Resellers appear on the row axis of the returned dataset, with empties suppressed.

3.  Select File -> Save MDXQuery1.mdx As ..., name the file MDX053-001-1, and place it in a meaningful location.

Our client colleagues express satisfaction with the contextual backdrop we have established for introducing contrasts in filtering to precisely tune the query to their specifications. First, we explain, we will filter the core dataset with the FILTER() function, to arrive at the exact dataset required. We will then replicate that filter effect with the HAVING clause, to illustrate its use in a manner that makes the results instantly verifiable as to accuracy and completeness.

4.  Replace the comment line in query MDX053-001-1 with the following:

-- MDX053-001-2 Using FILTER() to Narrow Returned Data Set

5.  Select File -> Save MDX053-001-1.mdx As ..., name the file MDX053-001-2, and place it in the same location as its predecessor, to protect the former query.

6.  Place the cursor to the right of the NON EMPTY keyword on the fifth row of the query.

7.  Press the Enter key twice to create a new line between the line of the query on which we have placed the cursor and the line that currently follows it, namely:

{[Reseller].[Reseller Type].[Business Type].[Value Added Reseller].CHILDREN} 

8.  Add the following syntax from the current cursor position (in the new space created by pressing Enter twice above):

FILTER( 

9.  On what is now the seventh row of the query (including the comment line at top), place the cursor to the immediate right of the following:

{[Reseller].[Reseller Type].[Business Type].[Value Added Reseller].CHILDREN} 

10.  Add a comma ( “,” ) to the right of the existing right curly brace, which immediately precedes “ON AXIS (1)” within the row.

11.  Press the Enter key twice to create a new line between the line of the query on which we have placed the comma and the line that currently follows it, namely:

ON AXIS (1)

12.  Type the following syntax into the new row:

[Measures].[Reseller Order Quantity] > 10

13.  Ensuring placement of the cursor to the right of the “10” in the line typed above, press the Enter key twice more, to create a new line between our newly added syntax and the line that currently follows it, namely:

ON AXIS (1)

14.  Type a right parenthesis ( “)” ) into the new row:

The effect, of course, is to enclose the specified set, {[Reseller].[Reseller Type].[Business Type].[Value Added Reseller].CHILDREN} within the newly added FILTER() function, predicated by the logical expression, [Measures].[Reseller Order Quantity] > 10, which FILTER() will evaluate against each member in the the specified set.

Once we have incorporated the FILTER() function via the steps above, the Query pane appears as shown in Illustration 5.


Illustration 5: “Adjusted” Query in the Query Pane (Modifications Circled)

15.  Execute the query by clicking the Execute button in the toolbar, as before.

The Results pane is populated by Analysis Services, and the dataset partially depicted in Illustration 6 appears.


Illustration 6: Results Dataset (Partial View) – FILTER() at Work

The client representatives agree that the FILTER() function has had the expected effect: only Value Added Resellers whose Reseller Order Quantity value exceeds ten units appear. We have thus filtered the members of the row axis successfully. Next we will demonstrate the use of HAVING as a substitute for the FILTER() function in obtaining the same results.

16.  Select File -> Save MDX053-001-2.mdx.

17.  Replace the comment line in query MDX053-001-2 with the following:

-- MDX053-001-3 HAVING Clause:  Simple Filter Alternative

18.  Select File -> Save MDX053-001-2.mdx As ..., name the file MDX053-001-3, and place it in the same location as its predecessor, to protect the former query.

19.  Remove the line of the query containing “FILTER(” (currently the sixth line of the query), which we added earlier.

20.  Remove the line of the query containing “)” (the eight line of the query, after removing “FILTER(” above).

21.  Remove the comma ( “,” ) to the right of the existing right curly brace of the string {[Reseller].[Reseller Type].[Business Type].[Value Added Reseller].CHILDREN}, which currently appears in the sixth row.

22.  Replace the expression in the row immediately below ( “{[Reseller].[Reseller Type].[Business Type].[Value Added Reseller].CHILDREN}” ) with the following clause:

HAVING [Measures].[Reseller Order Quantity] > 10

The modified query should look something like this:


-- MDX053-001-2 Using FILTER() to Narrow Returned Data Set
SELECT 
{[Measures].[Reseller Order Quantity], [Measures].[Reseller Sales Amount] } 
   ON AXIS(0),
NON EMPTY
      {[Reseller].[Reseller Type].[Business Type].[Value Added Reseller].CHILDREN}
        HAVING [Measures].[Reseller Order Quantity] > 10
   ON AXIS(1)
FROM 
   [Adventure Works]
WHERE
   ([Product].[Product Categories].[Category].[Components], 
      [Date].[Calendar].[Calendar Year].[CY 2003] )

The effect of our modifications is to substitute the HAVING clause for the FILTER() function, with the objective of filtering our row axis to deliver the same end results dataset as that delivered by the query via the FILTER() function. As before, the intent is to evaluate the logical expression, [Measures].[Reseller Order Quantity] > 10, against each member in the specified set.

Once we have substituted the HAVING clause via the preceding steps, the Query pane appears as shown in Illustration 7.


Illustration 7: Newly Adjusted Query in the Query Pane (Modifications Circled)

23.   Execute the query by clicking the Execute button in the toolbar, as before.

The Results pane is populated by Analysis Services, and the dataset partially depicted in Illustration 8 appears.


Illustration 8: Results Dataset (Partial View) – HAVING in Action

Our client colleagues concur that the HAVING clause has had the expected effect, and has replicated the action of the FILTER() function above. Several members of the group state that they prefer filtering via the HAVING clause, as they deem the coding easier to understand.

24.  Select File -> Save MDX053-001-3.mdx to ensure that the file is saved.

We now turn to the second example that we have discussed with the client representatives; it involves an additional case where they wish to contrast filtering methods, which they outline as follows. The authors group tells us that the value under consideration this time is the Reseller Average Sales Amount, but, again, that the mechanism we construct together will be extrapolated to other measures with similar analysis potential. The current desired end is to simply return the Reseller Average Sales Amount, as captured within the Adventure Works cube, for the Product Model Line named Components for each of the months of Calendar Year 2003, for those Customers of Geography Australia. This time they wish to cross join the respective Months with the measure within the column axis, presenting the total Reseller Average Sales Amount for each of the children of Product Model Line Components, which will inhabit the row axis.

Our client colleagues further tell us that they wish to see only those Product Model Line Components whose Reseller Average Sales Amount value for the Month of May 2003 exceeded zero units – this is initially a little confusing, but further discussion clarifies that we are indeed to present the value for all months of Calendar Year 2003, for only the Components with Reseller Average Sales Amounts for the Month of May 2003 greater than zero units. We note, once again, that the final part of the specification represents a need to filter the members of the row axis – this time supplying a filter condition that represents a subset of a part of a pre-existing axis specification. This example will therefore serve as another good opportunity for demonstrating the use of HAVING as a substitute for the FILTER() function.

Because we have pointed it out to them in passing, the client report authors group have a new appreciation for the fact that, given the current queries, the capability to support ad hoc runtime requirements, based upon, say, the “minimum Reseller Average Sales Amount,” the “Month of Minimum Average,” a combination of the two, and more (including extended variations such as an Average Sales Amount falling “between” a couple of values, a range of “minimum” months, etc.), becomes a matter of parameterizing the respective component of the rows specification of the query. Because we have demonstrated to our colleagues that parameterization of this sort becomes easily attainable within Reporting Services, assuming that sufficiently sophisticated queries are put in place to support it (within either the Reporting Services or Analysis Services layers), the queries are deemed even more valuable to the “extrapolation” focus of the team we have mentioned earlier.

NOTE: While we won’t get into the physical parameterization aspects of query design to make this happen, I present many scenarios that illustrate the concept within the articles of my Database Journal MSSQL Server Reporting Services series.

We will begin, as before, with the construction of a simple core query to support the business requirements in all except the row-axis filtering requirement we have outlined. Moreover (also after the manner of our first practice example), once the core query is in place, we will apply filtering via the FILTER() function, so as to have a baseline dataset that meets all the requirements, before again substituting the HAVING clause for the FILTER() function, to provide the same “instant verification” that the retrieved data reflects the same desired, filtered result.

25.  Select File --> New from the main menu.

26.  Select Query with Current Connection from the cascading menu that appears next, as shown in Illustration 9.


Illustration 9: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

27.  Type (or cut and paste) the following query into the Query pane:


-- MDX053-002-1 Core Query 
SELECT 
CROSSJOIN(
   {DESCENDANTS ([Date].[Calendar].[Calendar Year].[CY 2003], 
      [Date].[Calendar].[Month])},
   
         {[Measures].[Reseller Average Sales Amount] } )
   
      ON AXIS(0),
NON EMPTY
   {[Product].[Product Model Lines].[Components].CHILDREN}
      ON AXIS(1)
FROM 
   [Adventure Works]
WHERE
   ([Customer].[Customer Geography].[Country].[Australia])

The Query pane appears, with our input, as depicted in Illustration 10.


Illustration 10: Our Core Query in the Query Pane ...

The above core query is crafted to return the Reseller Average Sales Amount within the desired dataset, and is (as was the case in the earlier practice example) designed to retrieve a superset of the expressed business requirement. As we did in the first example, we will refine the core query via a filter, to produce the precise dataset our client colleagues have requested; in this case, we will restrict the data returned to Product Model Line Components whose Reseller Average Sales Amount value for the Month of May 2003 exceeded zero units.

28.  Execute the query by clicking the Execute button in the toolbar, as we did earlier.

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


Illustration 11: Results Dataset (Partial View) – Second Core Query before Filtering

In the partial view of the returned dataset, we see that the core query we have constructed accomplishes the intended purpose, and generates a superset of the expressed business requirement: the Reseller Average Sales Amount, as captured within the Adventure Works cube, for the Components Product Model Line, for each of the months of Calendar Year 2003, for those Customers of Geography Australia. As requested, the columns are formed by a crossjoin of each respective Month with the Reseller Average Sales Amount measure, while the row axis presents each of the children of Product Model Line Components. (We note, in passing, that the effect of the NON EMPTY keyword, too, is evident: While we do see nulls at the intersections of some of the Component / Months, no Component appears with nulls in every column.)

29.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX053-002-1, and place it in a meaningful location.

Our client colleagues once again express satisfaction with the contextual backdrop we have established, and state that they are ready to examine the contrasts in filtering that we have proposed to precisely tune the query to their specifications. As before, we will filter the core dataset with the FILTER() function, to arrive at the exact dataset required. We will then replicate the same filter with the HAVING clause, to illustrate its use in a manner that makes the results instantly verifiable as to accuracy and completeness.

30.  Replace the comment line in query MDX053-002-1 with the following:

-- MDX053-002-2 Using FILTER() to Narrow Returned Data Set

31.  Select File -> Save MDX053-001-2.mdx As ..., name the file MDX053-002-2, and place it in the same location as its predecessor, to protect the former query.

32.  Place the cursor to the right of the NON EMPTY keyword (on the eighth row of the query).

33.  Press the Enter key twice to create a new line between the line of the query on which we have placed the cursor and the line that currently follows it, namely:

{[Product].[Product Model Lines].[Components].CHILDREN}

34.  Add the following syntax from the currently cursor position (in the new space created by pressing Enter twice above):

FILTER( 

35.  On what is now the tenth row of the query (including the comment line at top), place the cursor to the immediate right of the following:

{[Product].[Product Model Lines].[Components].CHILDREN}

36.  Add a comma ( “,” ) to the right of the existing right curly brace.

37.  Press the Enter key twice to create a new line between the line of the query on which we have placed the comma and the line that currently follows it, namely:

ON AXIS (1)

38.  Type the following syntax into the new row:

[Measures].[Reseller Average Sales Amount] > 0 

39.  Press the Enter key twice more, to create a new line between the above newly inserted syntax and the line that currently follows it (ON AXIS (1), once again).

40.  Type the following syntax into the new row:

AND [Date].[Calendar].[Month].[May 2003]

Ensuring placement of the cursor to the right of the “[May 2003] in the line typed above, press the Enter key twice, once again, to create a new line between the line of the query on which we have placed the cursor and the line that currently follows it (ON AXIS (1), yet again).

41.  Type a right parenthesis ( “)” ) into the new row.

The effect is to enclose the specified set, {[Product].[Product Model Lines].[Components].CHILDREN}, within the newly added FILTER() function, together with the logical expression, [Measures].[Reseller Average Sales Amount] > 0 AND [Date].[Calendar].[Month].[May 2003], which FILTER() will evaluate against each member in the specified set.

Once we have incorporated the FILTER() function via the steps above, the Query pane appears as depicted in Illustration 12.


Illustration 12: “Adjusted” Query in the Query Pane (Modifications Circled)

42.   Execute the query by clicking the Execute button in the toolbar, once again.

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


Illustration 13: Results Dataset (Partial View) – FILTER() at Work

The client representatives agree, once again, that the FILTER() function has had the desired effect: only Components whose Reseller Average Sales Amount totals for the Month of May 2003 exceeded zero units have their Calendar Year 2003 Monthly Reseller Average Sales Amount values presented. Based upon their concurrence, we can conclude that we have filtered the members of the row axis successfully. Next we will demonstrate the use of HAVING as a substitute for the FILTER() function in obtaining the same results.

43.  Select File -> Save MDX053-002-2.mdx.

44.  Replace the comment line in query MDX053-001-2 with the following:

-- MDX053-002-3 HAVING Clause:  Simple Filter Alternative

45.  Select File -> Save MDX053-002-2.mdx As ..., name the file MDX053-002-3, and place it in the same location as its predecessor, to protect the former query.

46.  Remove the line of the query containing “FILTER(” (currently the ninth line of the query), which we added earlier.

47.  Remove the line of the query containing “)” (the twelfth line of the query, after removing “FILTER(” above).

48.  Remove the comma ( “,” ) to the right of the existing right curly brace of the string {[Product].[Product Model Lines].[Components].CHILDREN}, currently appearing in what is now the ninth row.

49.  Place the cursor to the immediate left of the syntax in the row immediately below ( “[Measures].[Reseller Average Sales Amount] > 0”).

50.  Type the keyword HAVING, followed by a space, so that it precedes the syntax like this:

HAVING [Measures].[Reseller Average Sales Amount] > 0 

51.  Ensure that the syntax in the line just below our replacement remains intact (that is, AND [Date].[Calendar].[Month].[May 2003]).

The modified query should be substantially the same as the following:


-- MDX053-002-3 HAVING Clause:  Simple Filter Alternative
SELECT 
CROSSJOIN(
   {DESCENDANTS ([Date].[Calendar].[Calendar Year].[CY 2003], 
      [Date].[Calendar].[Month])},
   
         {[Measures].[Reseller Average Sales Amount] } )
   
      ON AXIS(0),
NON EMPTY
   {[Product].[Product Model Lines].[Components].CHILDREN}
   
      HAVING [Measures].[Reseller Average Sales Amount] > 0
      
         AND [Date].[Calendar].[Month].[May 2003]
      ON AXIS(1)
FROM 
   [Adventure Works]
WHERE
   ([Customer].[Customer Geography].[Country].[Australia])

As was the case within the last query of our first practice example, our objective is to filter the row axis, and to deliver the same end results dataset as that delivered by the previous version of the query, via the FILTER() function. The effect of our modifications, again, is to substitute the HAVING clause for the FILTER() function. As before, the intent is to evaluate the logical expression, “[Measures].[Reseller Average Sales Amount] > 0 AND [Date].[Calendar].[Month].[May 2003]”, against each member in the specified set.

Once we have substituted the HAVING clause via the preceding steps, the Query pane appears as depicted in Illustration 14.


Illustration 14: Newly Adjusted Query in the Query Pane (Modifications Circled)

52.   Execute the query by clicking the Execute button in the toolbar, as before.

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


Illustration 15: Results Dataset (Partial View) – HAVING in Action

Our client colleagues concur that the HAVING clause has had the expected effect, as was the case in our first practice example: HAVING has replicated the action of the FILTER() function above.

53.  Select File -> Save MDX053-002-3.mdx to ensure that the file is saved.

54.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

The client representatives inform us that their immediate goals have been met, and that the examples we have shared have illustrated the principles of operation behind HAVING, while contrasting the “HAVING approach” to the “FILTER() approach,” as a means for filtering the row axis specification (and, indeed, for filtering an axis specification in general).

Summary ...

In this session, we continued our examination of MDX to concentrate upon the HAVING clause, which debuts with Analysis Services 2005. Our focus in this article was the use of HAVING as a substitute for the FILTER() function, primarily from the perspective of filtering an axis specification within an MDX query. After introducing HAVING, and discussing advantages that accrue in its use, we examined the syntax surrounding its employment within our queries.

We next undertook a couple of illustrative examples whereby we put HAVING to work, providing, in each example, an approach to achieving filtering within an axis specification, first via the FILTER() function, and then via the HAVING clause, both to compare the operation of the methods and to establish a “base dataset” result that could be easily compared. Throughout our practice session, where we employed all we had learned to meet hypothetical business requirements, we briefly discussed the results datasets we obtained from each of the queries we constructed or modified.

» 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