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

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:

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.

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

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

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

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.

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.

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

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

FILTER( 

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

ON AXIS (1)

[Measures].[Reseller Order Quantity] > 10

ON AXIS (1)

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.

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

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.

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

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.

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

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.