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

Julian Hyde jhyde at pentaho.com
Mon Jun 22 13:24:57 EDT 2009


Pedro, what's the difference between your first query (20 seconds) versus
the second (700 ms)? Mondrian ought to be smart enough to do it the best way
however you formulate your query.

Julian

> -----Original Message-----
> From: mondrian-bounces at pentaho.org 
> [mailto:mondrian-bounces at pentaho.org] On Behalf Of Pedro Alves
> Sent: Friday, June 12, 2009 9:28 AM
> To: Mondrian Mailing List
> Subject: Re: [Mondrian] Aggregating a set and using it in the 
> where clause ==1h running query
> 
> 
> 
> 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
> 
> 
> 





More information about the Mondrian mailing list