Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

MDX code snippet Existing

Posted by thomasivarssonmalmo on April 30, 2010

If you would like to see how many cities that have sold a certain bike model you can start trying to write something like this on the Adventure Works demo cube.
 
With Member Measures.ProdSold As
Count(Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0))
Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];
 
You will then get a result like this.
 
 
You can also use Count like this with the same result:
 
With Member Measures.ProdSold As
Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0).Count
 
Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];
 
The same value, 102 cities, will be repeated for each country.  What you would like to see is the number of cities for each country. The explantion for this behaviour can be found in the SQL Server 2008 MDX Step By Step book page 116. There is a link to this book up to the right in this blog. Also, the solution to this problem I found in the same book.
 
Before we proceed to the solution lets see how we can move the filter function from the calculated measure and actually use it on rows to see the actual cities that did sell this Bike. I have changed the query to this.
Select {[Measures].[Internet Order Quantity]} on 0,
(Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0)) On 1
From [Adventure Works];
 
You will get the result below.
 
 
 All the 102 cities will appear on the rows axis with it’s total internet order quantity.
 
Finally the solution is to add the Existing operator to the calculated measure like this:
 
With Member Measures.ProdSold As
Count( Existing Filter([Customer].[City].[City],
([Product].[Product Categories].[Product].&[575],[Measures].[Internet Order Quantity])> 0))
 
Select {Measures.ProdSold} On 0,
Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
From [Adventure Works];
 
Finally we will get what we were looking for. Each country will have the number of cities that sold one unit or more of the selected mountain bike.
 
Advertisements

2 Responses to “MDX code snippet Existing”

  1. […] : Existing, Count et Filter via Thomas Ivarsson. Il n’explique pas assez à mon gout, mais en jouant avec ses requêtes on comprend […]

  2. Naveen Das said

    I have been intrugued by the article and have been looking into improving the query as suggested by you. I have found that using EXISTS function gives better performance.

    With Member Measures.ProdSold As
    Count( Exists([Customer].[City].[City],
    {[Customer].[Customer Geography].CurrentMember}*{[Product].[Product Categories].[Product].&[575]},”Internet Sales”))
    Select {Measures.ProdSold} On 0,
    Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]) on 1
    From [Adventure Works];

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: