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

The EXCEPT() Function

The EXCEPT() function, according to the Analysis Services Books Online, "finds the difference between two sets, optionally retaining duplicates." We will examine the use of EXCEPT() to manage this, both with and without duplicates, in the sections that follow.

In keeping with the approach we have used in other lessons within the MDX Essentials series, we will examine the syntax for the EXCEPT() function in general, building to a determination of the difference between two sets after creating and running an individual query for each; this will show that the EXCEPT() function does, in fact, generate the results we might expect. Next, we will explore an example that introduces duplication within an EXCEPT() function, and the means that MDX affords us for managing duplication. Consistent with the design of our series, our objective is to gain a richer understanding of the options we have within the EXCEPT() function, as well as an overall perspective of how the function can be used to meet our business needs.

Discussion

The EXCEPT() function allows us to return a set consisting of the difference between a pair of sets upon which it is enacted. The syntax with which we employ EXCEPT() allows us an optional flag, as was the case with the INTERSECT() and UNION() functions, among others; the flag allows us the capability of presenting duplicates that might occur within the sets we are subjecting to the EXCEPT() function. The default for the function (without the flag), again like the INTERSECT() and UNION() functions, is the elimination of duplicates, which would likely be the typical requirement.

Let's look at some syntax illustrations to further clarify the operation of EXCEPT().

Syntax

Syntactically, the sets between which a difference is to be determined by the EXCEPT() function are placed within the parentheses to the right of EXCEPT, and separated by a comma. The optional ALL flag overrides the default behavior of the function, and allows duplicates to remain prior to the determination of the difference. The syntax is shown in the following string:

EXCEPT(Set1, Set2[, ALL])

The EXCEPT() function returns, in effect, whatever appears in the first set, and that does not appear in the second set, upon which the function is being enacted. This might also be expressed as the "data that is not in common between the two sets." Importantly, the two sets must be composed of the same dimension, and must exist at the same level within the dimension, for the EXCEPT() function to work.

While the default behavior of the function dictates that duplicates are eliminated prior to the determination of the difference, use of the optional ALL flag allows duplicate members to remain within the newly produced set, as we shall see in a step in the practice example. Duplicates matching in the first set are discarded, while non-matching duplicates are retained.

The following query contains an example EXCEPT() function. We are selecting the total annual General and Administrative ("G & A") expense for 1998 for the set of all store countries, minus the subset of the Canadian store countries.

SELECT

{Measures.[Amount] } ON COLUMNS,

   { EXCEPT ([Store].[Store Country].Members, {[Store Country].[Canada]} )} 
   
      ON ROWS

FROM 

Budget

WHERE 

   ([Account].[All Account].[Net Income].[Total Expense].[General & Administration],

      [Time].[1998])

This simple query would result in the return of a dataset similar to that depicted in Table 1.

We will practice the use of the EXCEPT() function in the section that follows. Moreover, we will explore the use of the ALL flag we have discussed, to activate and reinforce the concept of its use in retaining duplicates.