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

Pedro Alves pedro at neraka.no-ip.org
Fri Jun 12 12:27:59 EDT 2009



woot! 


I got it! Stupid me, all I had to do was to change the way the stuff is
done.

700ms:

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]




On Fri, Jun 12, 2009 at 04:58:54PM +0100, Pedro Alves wrote:
> 
> 
> 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
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

-- 
Pedro Alves
pmgalves-at-gmail.com




More information about the Mondrian mailing list