Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 5, 2007

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date