[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 13:26:35 EDT 2009



Pasted the wrong query, meant to use this:

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


On Fri, Jun 12, 2009 at 05:27:59PM +0100, Pedro Alves wrote:
> 
> 
> 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
> 
> _______________________________________________
> 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