[Mondrian] Aggregating a set and using it in the where clause == 1h running query

[Pedro Alves]

I know the subject hardly makes sense, but here it goes:


I have a set of dashboards where I can dynamically drill down in some
dimensions and exclude some members. Works as a charm; the problem is when
I have high cardinality; 


Take this examples in the steelwheels dataset:

select 
[Time].[Months].Members
 ON COLUMNS,
  [Customers].Children ON ROWS
from [SteelWheelsSales]


This works ok; Now I want this same grid but excluding NYC; What I do is:

with set [a] as 'Except([Markets].[City].Members,{[Markets].[City].[NYC]})'
member [Markets].[Filter] as Aggregate(a)
select 
[Time].[Months].Members
 ON COLUMNS,
  [Customers].Children ON ROWS
from [SteelWheelsSales]
where [Markets].[Filter]

I need to aggregate the [a] to use it in the where clause; but takes AGES
to complete; by ages I mean about 20 seconds in this set and 1 hour in my
real data, just eating cpu. I stays iterating in FunUtil.evaluateSet, and
seems to be doing that 
numbers of months * number of clients * (number of cities -1 ) times. 


Can I do this any other way? This seems to be highly inefficient, and the
problem seems to be on the Aggregate(); if I print that on the sets with:

with set [a] as 'Except([Markets].[City].Members,{[Markets].[City].[NYC]})'
member [Markets].[Filter] as Aggregate(a)
select 
[Time].[Months].Members
 ON COLUMNS,
  [Customers].Children * [a] ON ROWS
from [SteelWheelsSales]
--where [Markets].[Filter]

then its much faster - 2 seconds (though, of course, it's a visualization
nightmare and not what I want)


I remeber Julian having done some work on allowing sets to be on the where
clause; Would that help here? Is there any other trick besides the
Aggregate() to make a set into a member so that we can filter?


Thanks





-- 
Pedro Alves
pmgalves-at-gmail.com