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 Jan 12, 2004

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

By William Pearson



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.



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