MDX Essentials: Basic Set Functions: The EXCEPT() Function - Page 5

January 12, 2004

Were the ALL flag not in place to override default behavior, elimination would be imposed within both sets prior to the evaluation and return of the difference. We will see an instance of the default behavior later.

The Results pane is populated, and the dataset shown in Illustration 4 appears.

The behavior of the EXCEPT() function, with the ALL flag in place, can be explained in this way. Because it eliminates matching duplicates in first set first (that is, duplicates in the first set that match a member in the second set), Oregon (the only member in the second set that matches a pair of duplicates in the first set, is eliminated from the outset.

The non-matching duplicates (that is, duplicates in the first set that have no match in the second set) are retained. In the present case, that means that Washington and California, which exist as duplicates in the first set (they are included in [Store].[All Stores].[USA].Children, as well as appearing as [Store].[WA] and [Store].[CA], respectively), and are unmatched in the second set (where only [Store].[OR] appears), are retained as duplicates. Hence we see two of each of [Store].[WA] and [Store].[CA] appearing in the result dataset.

More succinctly, the rule for retaining duplicates is that matching duplicates in the first set are eliminated, and nonmatching duplicates are retained.

We can prove this to ourselves easily by making changes to the duplicates and re-executing the query, until we grasp the results that we obtain in each case. In addition, the remaining steps, which show the default operation of the EXCEPT() function, will demonstrate that duplicates are, indeed, eliminated when the default behavior is not overridden by the optional override flag.

{ EXCEPT

  ({[Store].[All Stores].[USA].Children, [Store].[OR], [Store].[CA], 

    [Store].[WA]} , {[Store].[OR]}, ALL )} ON ROWS

{ EXCEPT

  ({[Store].[All Stores].[USA].Children, [Store].[OR], [Store].[CA], 

    [Store].[WA]} , {[Store].[OR]} )} ON ROWS

-- MDX15-5:  Tutorial Query Step 5

The Query pane appears as shown in Illustration 5.

The Results pane is populated, and the dataset shown in Illustration 6 appears. We see clearly that the removal of the overriding flag results in the elimination of duplicates that were retained in our last step.

We have thus explored the use of the ALL flag within the EXCEPT() function to allow us to retain duplicates in a simple example. We have also exposed clearly the default behavior of the function, which is to eliminate the duplicates.

In conclusion, we can see that the EXCEPT() function provides us the significant capability of presenting the difference between two sets. Our use of the optional ALL flag within the syntax, can also afford us flexibility in the handling of duplicates in queries we construct that use this function - flexibility that can be paramount in meeting the needs of organizational information consumers.

Summary ...

In this lesson, we explored the useful EXCEPT() function, whose purpose is to return the difference between two sets. We have noted that, like the INTERSECT() function and other functions that we have explored in previous lessons, EXCEPT() allows us flexibility in duplicate handling, which is managed through the use of a flag which can be used to override the default elimination of duplicates.

The EXCEPT() function provides important capabilities within MDX, and is yet another means that MDX provides us to more efficiently access multidimensional data, as well as to write more concise code. In addition to discussing the purpose and operation of the EXCEPT() function, we focused on the treatment of duplicates by the function. We practiced the use of the function in general, through a multi-step practice example, then explored an additional example where we practiced the addition of the ALL flag, to override the EXCEPT() function's default duplicate handling. Throughout the multiple-step practice exercise, we discussed the results we obtained with each step's execution, remarking on the distinguishing characteristics of each.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.