dcsimg

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

January 12, 2004

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.








The Network for Technology Professionals

Search:

About Internet.com

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