MDX Essentials: Basic Set Functions: The Intersect() Function - Page 2December 8, 2003 The Intersect() FunctionThe 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. DiscussionIntersect() 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(). SyntaxSyntactically, 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.
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. |