MDX equivalent of a filtered GROUP BY in SQL

Does that title make sense? I don't know if it does, but I can't think of another description for this problem. If anyone can think of a better title I would love to hear it. It's hard to explain in words so let's jump into some code examples.

Consider the following SQL statement against the AdventureWorksDW relational database. The requirement is to select a list of 4 cities and then want to see the order quantity grouped at the country level.

SELECT 
    g.EnglishCountryRegionName Country
    ,sum(OrderQuantity) as OrderQuantity
FROM dbo.FactResellerSales rs
INNER JOIN dimReseller r
    ON r.ResellerKey = rs.ResellerKey
INNER JOIN dimGeography g
    ON g.GeographyKey = r.GeographyKey
WHERE g.City IN ('Melbourne','Sydney','Seattle','New York')
GROUP BY g.EnglishCountryRegionName

This query returns the following result:

image

How can we do an equivalent query in MDX?

if you start with something like the following it gets you the correct raw figures, but it breaks the amounts out for each city and we want to see the sub-totals by country.

SELECT 
  [Measures].[Reseller Order Quantity] on 0
  ,{[Geography].[City].[Melbourne]
   ,[Geography].[City].[Sydney]
   ,[Geography].[City].[Seattle]
   ,[Geography].[City].[New York]} on 1 
FROM [Adventure Works] 

This is the result that you get:

image

So then you might figure that putting the country members on the axis and the cities in the where clause should do the trick.

SELECT 
  {Measures.[Reseller Order Quantity]} on 0
  ,[Geography].[Country].[Country].Members on 1 
FROM [Adventure Works] 
WHERE (
    {[Geography].[City].[Melbourne]
    ,[Geography].[City].[Sydney]
    ,[Geography].[City].[Seattle]
    ,[Geography].[City].[New York]})

But then we get the following...

image

So what is going on here? The query has returned the total amounts for the entire countries, not just the sub totals for the cities in the WHERE clause. Because of the attribute relationship between Countries and Cities, SSAS has put the country members on the rows that are related to the cities in the WHERE clause. In effect what this query is roughly saying to SSAS is "show me the Reseller Order Quantity for the Countries that contain one or more of the following cities. There is a more in depth explanation of this behavior here: Attribute Relationships Explained

So what we want to do is to get a measure that is filtered to figures for the selected cities. One way of doing this would be to created a calculated measure like the following:

WITH MEMBER Measures.FilteredSales as 
    SUM(
        EXISTING {[Geography].[City].[Melbourne]
                ,[Geography].[City].[Sydney]
                ,[Geography].[City].[Seattle]
                ,[Geography].[City].[New York]}
   , [Measures].[Reseller Order Quantity])
SELECT 
  {Measures.[FilteredSales]} on 0
  ,[Geography].[Country].[Country].Members on 1 
FROM [Adventure Works] 
WHERE (
    {[Geography].[City].[Melbourne]
    ,[Geography].[City].[Sydney]
    ,[Geography].[City].[Seattle]
    ,[Geography].[City].[New York]})

This gets the result that we are after, but it was a rather convoluted solution.

image

However, there is an easier way. We could use a sub-select...

SELECT 
  [Measures].[Reseller Order Quantity] on 0,
  [Geography].[Country].[Country].Members on 1
FROM ( 
SELECT {[Geography].[City].[Melbourne] ,[Geography].[City].[Sydney] ,[Geography].[City].[Seattle] ,[Geography].[City].[New York]} on 0 FROM [Adventure Works] )

 

Which returns the following:

image

Which is exactly what we were after. I usually tend to avoid using sub-selects when I can as I don't like how they can affect calculated measures because the sub-select is not visible to functions like .CurrentMember. But there are situations like this, where they are perfectly suited and are much simpler than an alternative solution.

Print | posted on Wednesday, January 28, 2009 6:54 AM

Comments on this post

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Great tip!
Left by Russ on Jan 28, 2009 9:32 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
This is a really important blog post.

This MDX above:
WITH MEMBER Measures.FilteredSales as
SUM(
EXISTING {[Geography].[City].[Melbourne]
,[Geography].[City].[Sydney]
,[Geography].[City].[Seattle]
,[Geography].[City].[New York]}
, [Measures].[Reseller Order Quantity])
SELECT
{Measures.[FilteredSales]} on 0
,[Geography].[Country].[Country].Members on 1
FROM [Adventure Works]
WHERE (
{[Geography].[City].[Melbourne]
,[Geography].[City].[Sydney]
,[Geography].[City].[Seattle]
,[Geography].[City].[New York]})

Returns the same result on my SSAS 2008 cube as:

WITH MEMBER Measures.FilteredSales as
SUM(
EXISTING {[Geography].[City].[Melbourne]
,[Geography].[City].[Sydney]
,[Geography].[City].[Seattle]
,[Geography].[City].[New York]}
, [Measures].[Reseller Order Quantity])
SELECT
{Measures.[FilteredSales]} on 0
,[Geography].[Country].[Country].Members on 1
FROM [Adventure Works]

The slicer will not add anything that the calculated members will not do.

I hope that I have not missed anything and is only a small part of what you are discussing.
Left by Thomas Ivarsson on Jan 29, 2009 7:22 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Hi Thomas,

I get the same figures with both queries (on both 2005 & 2008), the only difference with the WHERE clause is that it excludes unrelated countries, which you could also do by adding NON EMPTY to the row axis.

Without the WHERE clause I get extra empty countries on the rows.
Left by Darren Gosbell on Jan 29, 2009 10:24 PM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
You are correct. I also got the empty rows.
Left by Thomas Ivarsson on Jan 30, 2009 12:06 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
This was very helpful but I have a slightly different problem.

How can I force it to show all the sales for Australia or United if the same criteria is used.
Left by Colleen on Mar 17, 2009 5:28 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Colleen, I'm not sure what you mean. Can you explain your issue in a bit more detail? What sort of criteria are you talking about? Is it possible to develop an example query against Adventure Works that illustrates your problem?
Left by Darren Gosbell on Mar 17, 2009 7:02 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
I need to be able to roll-up a parent based on the selection criteria of a child. In SQL, it would be something like

SELECT
g.EnglishCountryRegionName Country
,sum(OrderQuantity) as OrderQuantity
FROM dbo.FactResellerSales rs
INNER JOIN dimReseller r
ON r.ResellerKey = rs.ResellerKey
INNER JOIN dimGeography g
ON g.GeographyKey = r.GeographyKey
LEFT JOIN (SELECT EnglishCountryRegionName from dimGeography where EnglishCountryRegionName ('Melbourne','Sydney','Seattle','New York') Group by EnglishCountryRegionName) g2
on g.EnglishCountryRegionName = g2.EnglishCountryRegionName
GROUP BY g.EnglishCountryRegionName
Left by Colleen on Mar 17, 2009 7:57 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Colleen, If I understand correctly, this need would be satisfied by the second MDX query that I posted.

SELECT
{Measures.[Reseller Order Quantity]} on 0
,[Geography].[Country].[Country].Members on 1
FROM [Adventure Works]
WHERE (
{[Geography].[City].[Melbourne]
,[Geography].[City].[Sydney]
,[Geography].[City].[Seattle]
,[Geography].[City].[New York]})

This gets the totals for the countries that contain the cities in the WHERE clause. This works because the attributes in the WHERE are related to those in the rows.
Left by Darren Gosbell on Mar 17, 2009 10:58 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
The issue I had was that it was only the subtotal for the cities listed in the WHERE clause. I need the totals for all of Australia and all of the US.
Left by Colleen on Mar 18, 2009 12:16 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
The query I posted above does just what you want, but it relies on auto-exists, which only happens if the attribute in the axis and the where clause are related.

Another approach would be to do an exists between the two attributes, you can use the third parameter of the exists function to specify a given measure group to use.

SELECT
{Measures.[Reseller Order Quantity]} on 0
,EXISTS([Geography].[Country].[Country].Members
,{[Geography].[City].[Melbourne]
,[Geography].[City].[Sydney]
,[Geography].[City].[Seattle]
,[Geography].[City].[New York]}
,"Reseller Orders") on 1
FROM [Adventure Works]
Left by Darren Gosbell on Mar 19, 2009 6:49 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
good stuff!
Left by Jesse Orosz on Apr 02, 2009 7:28 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Good point Jason, in fact for an SSRS report I think I would do it this way and use SSRS to do the sum at the country level. It's not always possibly, but I think that SSRS feels more natural if I can feed it "flatter" results without aggreate levels.

The only change I would make would have been to crossjoin at the ,{[Geography].[Country].[Country] level to exclude the [All Geography] member.

SELECT
[Measures].[Reseller Order Quantity] on 0
,{[Geography].[Country].[Country].members * {[Geography].[City].[Melbourne]
,[Geography].[City].[Sydney]
,[Geography].[City].[Seattle]
,[Geography].[City].[New York]}} on 1
FROM [Adventure Works]
Left by Darren Gosbell on Mar 21, 2010 7:47 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Good post, almost what I was looking for, but not quite. Is there a way to group some items from a dimension using a calculated set? I need to do this for a client tool that I can use calculated sets or members, but I can't write the query.

I need to take a date hierarchy by month and show the most current three months and one more member that groups all prior months, then sorts descending, like this:

2010-May
2010-Apr
2010-Feb
2010-Jan and Before

The set would have to be dynamic so it always shows the most current month first. Is this possible with a calculated set, or only within the query itself?
Left by Kory on May 04, 2010 4:35 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
@Kory This should be possible in a named set. The basic technique would be to create a calculated member in your date dimension for the "... and before" months. You would use the Aggregate() function so that this member would work correctly with all the measures.

It would be easier if this had a fixed generic name as dynamically changing the name would involve updating the MDX script each month.

Once you had that you would then create a set with the recent 3 months plus the calcuated member.

eg.

create member currentcube.measures.date.calendar.[Earlier] as
aggreagate(null:[Date].[Calendar].<current month>.lag(3));

create set [Recent Months] as
{[Date].[Calendar].<current month>.lag(2):[Date].[Calendar].<current month>
,[Date].[Calendar].[Earlier])
Left by Darren Gosbell on May 04, 2010 7:10 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Thank you Darren! This worked for me, but I can't seem to get the reverse ordering to work. I tried to use ORDER([Recent Months],[Recent Months].Item(0).Properties("YYYYMM"),DESC)
but it sorts them in the default order. If I create a member of this member property to view, it does show up as 201001, 201002, etc. so it should be reading it right...
Left by Kory on May 04, 2010 7:59 AM

# MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Hi, i am newbie on mdx query. so i need some help to make a simple mdx query.
suppose that I have the following dimension:

Dimension name: [DimCustomer]
Dimension fields : [Code] and [Age]
[Measures].[Sales]

and the following data:

Code | Age | Sales
1 | 20 | 2
2 | 35 | 0
3 | 20 | 2
4 | 30 | 1

what i want?

Is counting the number of customer by age with Sales greater then or equal 0. The result:

Age | Count
20 | 2
30 | 1
35 | 1


Can you please, help me?
Thanks in advance,
cafc
Left by cafc on Jul 21, 2010 9:17 PM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Hello Darren,

I haven't found how to solve a problem that is similar to the one you solve here, but with a parent child hierarchy and a unary operator. On this scenario the sub select doesn't work. Please follow the link below for a more detailed description of the problem and a test case:

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/b913ad16-d5ee-4b0b-aac4-73ea31990381

Do you have a solution for this scenario?

Thank you,
Daniel.
Left by Da on Aug 25, 2011 6:31 AM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
@Daniel - I added a reply directly to that thread.
Left by Darren Gosbell on Aug 25, 2011 2:25 PM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
NICE NICE KEEP POSTING
Left by NISH on Nov 09, 2012 9:19 PM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
Nice post and I've found it while looking into some strange MDX behavior for a query similar to the ones you describe here - the query seems to behave differently depending on the number of elements in the WHERE set. This behavior is a bug to me, but if there is an explanation for that, it would be good to know.

Sales per country:

SELECT NON EMPTY
{Measures.[Reseller Sales Amount]} on COLUMNS
,[Geography].[Country].[Country].ALLMEMBERS on ROWS
FROM [Adventure Works]
WHERE (
{[Geography].[City].[London]
,[Geography].[City].[Seattle]})

Reseller Sales Amount
United Kingdom $4,279,008.83
United States $53,607,801.21


Now, remove one of the two cities from the set in the Where clause, e.g., London, and the query returns only sales for Seattle and not US, although it should do the same aggregation as in the previous query :

SELECT NON EMPTY
{Measures.[Reseller Sales Amount]} on COLUMNS
,[Geography].[Country].[Country].ALLMEMBERS on ROWS
FROM [Adventure Works]
WHERE (
{[Geography].[City].[Seattle]})

Reseller Sales Amount
United States $1,494,860.53
Left by MariusM on Aug 28, 2013 6:53 PM

# re: MDX equivalent of a filtered GROUP BY in SQL

Requesting Gravatar...
@MariusM - Yeah that looks like a bug to me. I think that would be worth logging an issue at http://connect.microsoft.com/sql.

As a work around you could use the EXISTS() function on the row axis.

eg.

SELECT NON EMPTY
{Measures.[Reseller Sales Amount]} on COLUMNS
,EXISTS([Geography].[Country].[Country].ALLMEMBERS , {[Geography].[City].[Seattle]}) on ROWS
FROM [Adventure Works]
Left by Darren Gosbell on Aug 29, 2013 7:00 AM

Your comment:

 (will show your gravatar)