The Intersect() Function
The Intersect() function, according
to the Analysis Services Books Online, "Returns the intersection of two input sets, optionally retaining
duplicates." We will examine the manner of accomplishing intersects, both
with and without duplicates, in the sections that follow.
We will
examine the syntax for the Intersect() function in general, building to
the intersection of two sets after creating an individual query for each, to
show that the Intersect() function does, in fact, generate the results
we might expect. Next, our practice example will be modified to introduce the
possibility of duplication within an Intersect() function, and
the means that MDX affords us for managing duplication. Our objective is to
gain a richer understanding of the options we have within the Intersect()
function, together with a feel for the similarities that it has with the Union()
function that we discussed in Lesson
13.
Discussion
Intersect() allows us to intersect sets; that
is, it allows us to return a set consisting of the members that the two
original sets have in common. The syntax with which we make use of Intersect()
affords us an optional flag, as was the case with the Union()
function, to force the presentation of duplicates that might occur within the
sets we are subjecting to the Intersect() function. The default for the
function (without the flag), again like the Union() functions, is
the elimination of duplicates.
Let's look at some syntax illustrations to further clarify
the operation of Intersect().
Syntax
Syntactically, the sets to be "crossed"
in the Intersect() function are placed within the parentheses to the
right of Intersect, and separated by a comma. The optional ALL flag
overrides the default behavior of the function with regard to duplicates, and
allows duplicates to remain in the set that is produced. The syntax is shown in
the following string:
Intersect(Set1, Set2[, ALL])
The Intersect() function returns data that two sets
have in common. While the default behavior of the function dictates that duplicates
are eliminated from both sets prior to their intersection, 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.
The following example expression illustrates a use of the Intersect()
function. We will intersect a set of three cities' employee population
information with a subset of the first three cities' population
information, to see that only the members "in common" between sets 1
and 2 are returned; in short, the employee population data for the two-city subset
is returned.
INTERSECT(
{([Store].[All Stores].[USA].[OR]),([Store].[All Stores].[USA].[CA]), ([Store].[All Stores].[USA].[WA])} ,
{([Store].[All Stores].[USA].[OR]), ([Store].[All Stores].[USA].[WA])} )
This simple expression in a proper query,
for the measure Number of Employees, would result in the return of a set
similar to that depicted in Table 1.
|
|
Number of Employees
|
|
OR
|
136
|
|
WA
|
287
|
Table 1: Results of a Simple Intersect, Selecting Number
of Employees as the Measure
We will practice the use of the Intersect() function
in the section that follows. Moreover, we will explore the use of the ALL
flag we have discussed, to activate the concept of its use in retaining
duplicates.
» 
Comment and Contribute
