Practice
The Basics
As we have done in previous articles of this series, and to reinforce
our understanding of the basics we have covered so far, we will use the EXCEPT()
function in a manner that illustrates its operation through a multi-step
example. We will begin with the construction of a pair of simple SELECT
queries, then build to a combination of the two within an EXCEPT() function,
which will itself be housed within a query that we construct to address an
illustrative business need. We will then explore the use of the ALL
flag to force retainage of duplicates for presentation purposes.
We will call upon our old friend, the MDX Sample
Application, once again, to construct and execute the MDX we examine, and
to view the results datasets we obtain.
1.
Start the MDX
Sample Application.
2.
Clear the top
area (the Query pane) of any queries or remnants that might appear.
3.
Ensure that FoodMart
2000 is selected as the database name in the DB box of the toolbar.
4.
Select the Budget
cube in the Cube drop-down list box.
Let's assume for our practice example that we have been
asked, as MSAS architects for our organization, to present the change, or
delta, in the total G & A expense, a measure that is stored in the FoodMart
2000 Budget cube, between years 1997 and 1998, for all stores
within two states, California and Washington. We are to
present the change as "Annual Delta," the term used by
management when reporting from the Budget cube for such year-to-year
differences in values, in our ultimate results dataset.
We are aware that our organization operates in three states,
California, Washington and Oregon, and, especially since
we are excluding only one of the state stores groups (comprising the Oregonian
stores) from the final dataset, we see an opportunity to use the EXCEPT()
function to achieve our ends. We will first construct a SELECT query
that returns the Annual Delta for all three states' stores, and then
construct a second SELECT query to present the same data for the Oregonian
stores alone.
At this stage, we should easily be able to understand the
difference between total G & A expense for all three states, and the
corresponding total for just the Oregonian stores. The next step in our
practice example will be the creation of a query that performs the retrieval of
this difference with an EXCEPT() function. Finally, we will modify the
query to demonstrate the use of the optional duplicates flag to see the effect
that can be induced via that approach.
As our first objective is to present the total Annual
Delta of General & Administration expense, between years 1997
and 1998, for the stores of all three states, we will compose a simple
query to select the total value for this set, by taking the following steps.
5.
Type the
following query into the Query pane:
-- MDX15-1: Tutorial Query Step 1
WITH MEMBER
[Time].[Annual Delta]
AS
'[Time].[1998] - [Time].[1997]'
SELECT
{Time.[1998] , Time.[1997] , [Time].[Annual Delta] } ON COLUMNS,
{[Store].[All Stores].[USA].Children} ON ROWS
FROM
Budget
WHERE
([Account].[All Account].[Net Income].[Total Expense].
[General & Administration], Measures.[Amount])
Again,
the purpose of this query is to simply generate the first set of the pair of
sets upon which we intend to enact the EXCEPT() function, within a
final, "consolidated" query.
6.
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 1 appears.
Illustration 1: Result Dataset - Initial Half of Set Pair Whose
"Difference" is Required
We see the total G & A expense
returned for each of years 1997 and 1998, as well as the Annual
Delta between the years (created through the use of a calculated member
within the WITH MEMBER clause - see Lessons 7, 8,
and 9 of my MDX in Analysis Services series, here at DatabaseJournal, for more information), for each state. We
obtain confirmation, since we use the .Children function (for more
information, see Article Five in this series, MDX
Member Functions: The Family Functions) in constructing our rows, that the three states we
see are the complete set for the information presented.
7.
Select File
-> Save As, name the file MDX15-1,
and place it in a meaningful location.
Now let's construct a second SELECT query that
retrieves the identical dataset as that above, but this time for the Oregonian
stores alone.
8.
Select File
--> New from the main menu.
A new,
blank query pane appears.
9.
Type the
following query into the Query pane:
-- MDX15-2: Tutorial Query Step 2
WITH MEMBER
[Time].[Annual Delta]
AS
'[Time].[1998] - [Time].[1997]'
SELECT
{Time.[1998] , Time.[1997] , [Time].[Annual Delta] } ON COLUMNS,
{[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 simply generate the "second half" of the
pair of sets between which we intend to obtain a "difference" using
the EXCEPT() function in yet another query, which we will construct
next.
» 
Comment and Contribute
