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.


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


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:

 DISTINCT(

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

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers