Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 10, 2003

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

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date