MDX Set Functions: The Distinct() Function - Page 4

June 5, 2006

The report authors / developers have a new appreciation for the fact that, given the current core query, the capability to perform ad hoc hierarchy generation, based upon the individual selected at runtime, becomes a matter of parameterizing a key component of the rows-axis specification of the query, the unique name for the employee. Because we have demonstrated to the developers, 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 core query is deemed valuable to the team.

While we will not get into the parameterization aspects of query design to make this happen, we will concentrate upon one undesirable characteristic of the query as it is currently written; the developers tell us that all seems to be functioning properly, except for duplication within the results dataset that the query produces. We will use this scenario as a vehicle to once again illustrate the usefulness of the Distinct() function. Moreover, showing the unintended duplication in the initial scenario, and then applying Distinct() to the query to eliminate the duplicates, will provide a hands-on, "before and after" look at how Distinct() behaves in another example.

To outline the requirement further, the developers present a draft of the intended objective within an MS Excel spreadsheet (with an additional column showing Employee level, for illustration purposes only). The draft is intended to serve as our "confirmation of understanding draft" of the desired dataset, and appears in MS Excel as depicted in Illustration 13.

Having obtained consensus on the proposed target dataset, we are ready to set about constructing the query.

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.

-- MDX044-002-1 Duplication Appears within the Use of Combined Functions
SELECT 
   {[Measures].[Reseller Order Count]} ON AXIS(0),
   HIERARCHIZE( 
      {DESCENDANTS([Employee].[Employees].[Stephen Y. Jiang]), 
   
          ASCENDANTS([Employee].[Employees].[Stephen Y. Jiang])}) 
              DIMENSION PROPERTIES [Employee Department].[Department Name] 
             
                  ON AXIS(1)
                    
FROM
    [Adventure Works]
WHERE
    ([Date].[Calendar Year].[CY 2004]) 

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

The Results pane is, once again, populated by Analysis Services. This time, the dataset shown in Illustration 16 appears.

In the returned dataset, we see that, except for the duplicate seen in the above illustration, the query appears to deliver the intended results.

As we can see, the initial query presents a scenario that will serve as a basis from which to employ Distinct() to eliminate duplication. The Distinct() function can be easily added to align the dataset completely with the "intended results" draft that the developers have provided, as we shall see next.

-- MDX044-001-2 Using DISTINCT() to Eliminate Duplication

 DISTINCT(

ASCENDANTS([Employee].[Employees].[Stephen Y. Jiang])})

The effect, once again, is to enclose the set involved within the Distinct() function. Once we have accomplished this simple modification, the Query pane appears as depicted in Illustration 17.

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

Once again, we witness that Distinct() has eliminated the duplicate tuple, leaving only a single occurrence of manager Stephen Y. Jiang, for whom two occurrences appeared in the earlier results dataset. Further, while it might be impossible to ascertain precisely which of the duplicate rows remains, the fact is that the function has removed "all except the first instance" of the once duplicated tuple, as we discussed earlier, and then saw in our first practice example.

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

Summary...

In this lesson, we continued our examination of MDX functions to concentrate upon the rudimentary, but useful, Distinct() function. We discussed the purpose of Distinct(), to return a set without duplicates from a set we specify within the function, as well as the manner in which the function manages to return a distinct set.

After introducing the Distinct() function, we examined the syntax with which we employ it. We next undertook illustrative examples whereby we put Distinct() to work, to gain some hands-on practice in its use. Throughout our practice session, 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.