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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 5, 2006

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

By William Pearson

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.

Illustration 13: "Confirmation Draft" of Intended Hierarchical Results Dataset

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

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

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

Illustration 14: 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.

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

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

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

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

17.  Execute the query by clicking the Execute button in the toolbar.

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

Illustration 16: Results Dataset –Duplicate in Evidence (Circled)

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

18.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX044-002-1.mdx, and place it in the same location used to store the earlier queries.

19.  Leave the query open for the next step.

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.

20.  Replace the comment line in query MDX044-002-1 with the following:

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

21.  Select File -> Save MDX044-002-1.mdx As..., name the file MDX044-002-2.mdx, and save it with the other queries we have constructed.

22.  Click to the immediate right of HIERARCHIZE( - on the fourth row from the top in the existing query - to place the cursor there.

23.  Press the Enter key on the PC twice, to create a space between the row and the row underneath it.

24.  Type the following syntax into the new row:


25.  On what is now the seventh row, place the cursor to the immediate right of the following:

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

26.  Add another right parenthesis ( ")" ) to the right of the right-most parenthesis.

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.

Illustration 17: "Adjusted" Query in the Query Pane (Modifications Circled)

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

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

Illustration 18: Results Dataset – Distinct() in Action

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.

28.  Select File -> Save MDX044-002-2.mdx to ensure that that the file is saved.

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


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.

MS SQL Archives

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