MDX Clauses and Keywords: Use HAVING to Filter an Axis - Page 3

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.

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.

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.

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

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.

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

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.)

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.

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

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

FILTER( 

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

ON AXIS (1)

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

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

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.

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

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.

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

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

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.

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

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.

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.