10.
Execute the
query by clicking the Run Query button in the toolbar.
The
Results pane is
populated by Analysis Services, 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 MDX15-2.
We
have now become familiar with the data populating the two sets upon which we
wish to perform our EXCEPT() operation. This should make the operation
of the EXCEPT() function clearer in our next step, as the result dataset
that we obtain through its use should equal our first dataset after we "subtract"
our second dataset.
12.
Select File
--> New from the main menu.
A new,
blank query pane appears.
13.
Type the
following query into the Query pane:
-- MDX15-3: Tutorial Query Step 3
WITH MEMBER
[Time].[Annual Delta]
AS
'[Time].[1998] - [Time].[1997]'
SELECT
{Time.[1998] , Time.[1997] , [Time].[Annual Delta] } ON COLUMNS,
{ EXCEPT
([Store].[All Stores].[USA].Children, {[Store].[OR]} )} ON ROWS
FROM
Budget
WHERE
([Account].[All Account].[Net Income].[Total Expense].
[General & Administration], Measures.[Amount])
The
purpose of this query is to return the selected data for the difference
between the pair of sets we have constructed and examined individually, using
the EXCEPT() 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: The Results of Our Query with the EXCEPT()
Function in Place
Finally,
the effects of the EXCEPT() function are made clear. We note that, in
this simple scenario, the resulting
dataset is identical to the results dataset of our first SELECT query, "minus"
the results of the second SELECT query. While the actions we have taken
might seem not to save us a great deal of effort (after all, we might have
simply selected the California and Washington store states as our
rows directly, and skipped the EXCEPT() function entirely), we can
easily see that in a much larger group of members (ours was a small set of
three states), much typing might be saved by simply using a .Children, .Members,
or other function, and then "backing out" the members that we choose
not to include in our presentation.
15.
Save the file
as MDX15-3.
Next,
we will examine the operation of the EXCEPT() function from the
perspective of how it handles duplicate members in the sets between which it is
being asked to return a difference.
Dealing with Duplication
Let's next
establish a scenario whereby we can explore the handling of duplicates by the EXCEPT()
function. We will accomplish this by creating a query whose objective is to
introduce sets into the function that we know to contain a duplicate member,
as we have in earlier lessons with other functions offering a similar
duplicates flag option.
While
our next step may be a bit of a departure from a completely "real world"
scenario, the idea is to make the operation of the function clear through the
use of an example that can be easily understood. As most of us are aware, we
can certainly rely upon the fact that there are many situations in the business
environment 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:
-- MDX15-4: Tutorial Query Step 4
WITH MEMBER
[Time].[Annual Delta]
AS
'[Time].[1998] - [Time].[1997]'
SELECT
{Time.[1998] , Time.[1997] , [Time].[Annual Delta] } ON COLUMNS,
{ EXCEPT
({[Store].[All Stores].[USA].Children, [Store].[OR], [Store].[CA],
[Store].[WA]} , {[Store].[OR]}, ALL )} ON ROWS
FROM
Budget
WHERE
([Account].[All Account].[Net Income].[Total Expense].
[General & Administration], Measures.[Amount])
The EXCEPT()
function is called into action this time to return a difference between two
sets where we know duplicate members exist. We are asking that the complete set
of the USA store-state children, which
includes the states of Oregon, Washington and California, be combined with the sets of the
single states of Oregon, Washington and California in the initial set specification.
Then we are asking that the resulting set be used in the EXCEPT()
function with the set of the single state of Oregon, and that a difference be returned.
Were
the ALL flag not in place to override default behavior, elimination
would be imposed within both sets prior to the evaluation and return of the difference.
We will see an instance of the default behavior later.
» 
Comment and Contribute
