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

November 10, 2003

Analysis Services populates the Results pane, and the dataset shown in Illustration 2 appears.


Illustration 2: Result Dataset - The "Second Half" of the Pair of Candidate Sets

11.  Save the file as MDX13-2, and place it in a meaningful location.

We have now seen the data populating the two sets that we will next combine via the Union() function. This should make the operation of the function clearer in our next step.

12.  Select File --> New from the main menu.

A new, blank query pane appears.

13.  Type the following query into the Query pane:

-- MDX13-3:  Tutorial Query Step 3

SELECT

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

UNION(

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

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

   ON ROWS

FROM Warehouse

The purpose of this query is to combine the pair of sets we have examined individually, using the Union() function.

14.  Execute the query by clicking the Run Query button in the toolbar.

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


Illustration 3: Our First Union() Function Results

At last we see the effects of the Union() function using the primary syntax. Note that the single Oregon city appears along with the Washington child cites, just as the information consumers have stated that they wanted. This simple example makes clear the operation of the Union() function in the context of its primary syntax.

15.  Save the file as MDX13-3.

Next, we will examine the operation of the function from the standpoint of how it handles duplicate members in the sets it is being asked to combine.

Dealing with Duplication

Let's establish a scenario whereby we can explore the handling of duplicates by the Union() function. We will accomplish this by creating a query whose objective is to combine two sets that we know to contain a duplicate member, and will thus perhaps step out of the realm of a credible request from our information consumers. This is to make the operation of the function clear, however, and we can certainly rely upon the fact that there are many real-world scenarios where dealing with duplicates is a fact of life.

1.  Select File --> New from the main menu.

The blank query pane appears.

2.  Type the following query into the Query pane:

-- MDX13-4:  Tutorial Query Step 4

SELECT

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

UNION(

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

{[Store].[All Stores].[USA].[WA].[Bellingham]})

ON ROWS

FROM Warehouse

The Union() function is called into action this time to perform a combination between two sets that we know to contain an identical member; we are asking that the complete set of the Washington city-children, which includes (see Step 1 of the practice exercise above) the city of Bellingham, be combined with the set of the single city of Bellingham. The purpose, again, is to illustrate clearly the default behavior of the Union() function, primary syntax, with regard to the handling of such duplicates.

3.  Execute the query by clicking the Run Query button in the toolbar.

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


Illustration 4: Union() Function Results - Duplicates Eliminated

4.  Save the file as MDX13-4.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers