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

Remarks

The Union() function (using either primary or alternative syntax) returns the combination of two sets' data. Use of the ALL flag in the primary syntax allows duplicate members to remain within the newly produced set, as we shall see in a step in the practice example.

We can also combine sets via one of the alternate syntaxes shown above. We can accomplish the union by enclosing the sets in a list-like manner, separating them with a comma; additionally, we can simply place a plus ("+") operator between the sets we intend to join.

According to the SQL Server 2000 Books Online, the use of either alternative approach is equivalent to the primary approach with an ALL flag in place; that is, anytime one of the alternate syntaxes is chosen, duplicates within the newly created set are retained in the set. (My experience appears to differ: I find that, for one of the alternate syntaxes, duplicates are not retained. We shall see an example of this in the practice set of the next section.)

The following example expression illustrates a use of the primary Union() function. Suppose we are asked by a group of FoodMart information consumers to present total Warehouse Sales for the state of Washington, by child city, together with the total sales of one city in Oregon, Portland, for reasons known only to management. We might approach this need with an expression similar to this:

UNION(

{[Store].[All Stores].[USA].[WA].Children},

{[Store].[All Stores].[USA].[OR].[Portland]})

This expression in a proper query, for the measure Warehouse Sales, would result in the return of the set depicted in Table 1.

In the expression above, we use the Union() function, in combination with the .Children member function (See MDX Member Functions: The "Family" Functions for a tutorial on this and related family functions) to enumerate the Washington, and Oregon (in this case, one specific city, Portland) child cities.

We will practice the use of the Union() function in the section that follows. Moreover, we will explore the use of the alternate syntaxes we have discussed, after the steps of our practice with the primary syntax, to consolidate our focus and activate the concepts that parallel those of the primary syntax.

Practice

The Basics

Let's reinforce our understanding of the basics we have covered so far, and extend those concepts to the "alternate sisters" of the primary Union() syntax. We will use the Union() function in a manner that illustrates its operation in a multi-step example that builds from a pair of simple select queries, to a combination of the two in a Union() illustration. From this point, we will investigate how to obtain similar results with the alternate syntaxes. We will call upon the MDX Sample Application again, as our tool for constructing and executing the MDX we examine, and for viewing the result datasets we obtain.

Let's assume for our practice example that we have been asked to supply data for a similar need as that we instanced in the example used in the syntax section. To reiterate, we are asked to present total Warehouse Sales, a value that is captured monthly within the FoodMart organization and which is stored in the Warehouse cube, for the state of Washington, by child city, together with the total sales of one city in Oregon, Portland. We will begin by composing a simple query to select the sales figure for the Washington cites, followed by a query to select the same information for the single Oregon city. We will then combine the two with the Union() function and its alternates.

-- MDX13-1:  Tutorial Query Step 1
SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
{[Store].[All Stores].[USA].[WA].Children} ON ROWS
FROM Warehouse

The purpose of this query is to simply generate the "first half" of the pair of sets that we intend to join together in the Union() query that will follow.

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 1 appears.

We see the total Warehouse Sales for Washington returned (actually for all years in the cube, as we do not specify that we want further detail).

A new, blank Query pane appears.

-- MDX13-2:  Tutorial Query Step 2

SELECT

{[Measures].[Warehouse Sales]} ON COLUMNS,

{[Store].[All Stores].[USA].[OR].[Portland]} ON ROWS

FROM Warehouse

The purpose of this query is to simply generate the "second half" of the pair of sets that we intend to join together in the Union() query that we will construct next.