MDX: Filtering and slicing on different members of the same dimension

Question

I am using icCube's Sales cube to learn MDX. I would like to write an MDX query to show the revenue from sales in 2009 for those countries where the revenue from sales in 2010 was over $80,000. I tried the following, but it returned an empty table with only one column ([Amount]):

WITH
 SET [myset] AS Filter([Country].members, ([Measures].[Amount], [2010])>80000)
select [Country].members on 0, [Amount] on 1
from (select [myset] on 0, [Amount] on 1 from [sales])
where [2009]

FrankPl · Answer 1 · 2013-12-17T08:21:30.443

3

Just use

WITH
 SET [myset] AS Filter([Country].members, ([Measures].[Amount], [2010])>80000)
select [myset] on 0, [Amount] on 1
from [sales]
where [2009]

In MDX, this type of query does not need a subselect or WHERE. And you even could omit the set myset, writing

select Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
       [Amount] on 1
from [sales]
where [2009]

I tried a similar query on Adventure works, and it shows All Customers, Australia, Canada, and US, but not France, Germany, and UK:

SELECT Filter([Customer].[Country].Members,
              ([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2006]) > 600000
             )
       ON 0,
       {[Measures].[Internet Sales Amount]}
       ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2008]

edited Dec 17 '13 at 08:21

answered Dec 16 '13 at 18:04

FrankPl

13,205
2
14
40

This does not work. It returns an empty table. I understand that slicing on [2009] gets rid of all the data for [2010]... – AlwaysLearning Dec 16 '13 at 18:30
@MeirGoldenberg I verified my query - adapted as necessary - with MS Adventure Works, see my edited answer. I assume there is something else wrong with your query or your data. – FrankPl Dec 17 '13 at 08:23
there is a bug in icCube :-( , that's why is not working – ic3 Dec 17 '13 at 09:35

ic3 · Answer 2 · 2013-12-30T08:55:54.493

UPDATE : The tuple evaluation is a case we missed, the bug has been fixed in icCube 4.2. Pay attention that using the same hierarchy on a where clause and an axis it's tricky. Any sets is filtered out : "{[2010],[2011]} on 0 .. where [2010] will return [2010] but a tuple is evaluted without filtering. Different hierarchies of the same dimension will apply the autoexists filter.

As this is a bit tricky we decided to extend SETS to allow to declare a set in a query that is evaluated with the global context - without taking into account where clause and subqueries :

WITH
-- always the same regardless of the where clause and subquery
  STATIC SET [Global] Filter([Country].members, ([Measures].[Amount], [2010])>80000)
..

The easy and most efficient solution is to go as Frank is proposing, adding directly the filtered countries in the axis.

SELECT 
   // this will return the countries with sales amount bigger than 80'000 for [2010]
   // e.g. { [USA], [UK] } ... [2010] is is just used for filterting
   Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
   // When evaluated we're going to use [2009] as it's the slicer default value
   [Amount] on 1
FROM [sales]
WHERE [2009]

Regarding your query it should work, I agree, it's a bug in icCube that we will fix asap (PTS). In the meantime you have to avoid using the same hierarchy in the slicer and in the Filter function used in a slicer, something like :

WITH 
 MEMBER [Amount 2009] AS ([Amount],[2009])
SELECT 
  Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
  [Amount 2009] on 1
FROM [sales]

Or you can change to a version that works in icCube (less efficient than Frank's one) :

SELECT [Country].members on 0, [Amount] on 1
FROM (select  Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0 from [sales])
WHERE [2009]

If you're playing with grouping based on different critierias you can use a new feature icCube introduced a couple of months ago : Categories

They allow to create a hierarchy as member of other hierarchies. You could define those countries as [80000+] to afterwards perform calculations based on this new clasification.

Please point out the place where I am going wrong, but it seems to me that a solution without a subquery is plain wrong, because the order of evaluation of the query is: FROM, WHERE, SELECT (sets, etc.). Once the slicing in the WHERE clause is done (and so only the data for [2009] remained in the cube), I cannot use a filter on [2010] in the SELECT clause. Please see this post as well: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cd980958-e443-444e-a176-9285d5080059/mdx-query-execution-and-context?forum=sqlanalysisservices — AlwaysLearning, Dec 17 '13 at 09:55
WHERE clause in MDX is not like a SQL where but more like a way setting default values. So if in the axis you put [2010] you're going to use [2010] overwriting [2009]. It's when you're using a subquery that you get the behaviour like in a SQL WHERE clause. It's strange but it's how MDX works — ic3, Dec 17 '13 at 10:03
Till we release a new version in a couple of days, you've to use either static sets or the query with the subselect (the second one of my post) — ic3, Dec 17 '13 at 10:13

MDX: Filtering and slicing on different members of the same dimension

2 Answers2