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.
» 
Comment and Contribute
