[Mondrian] CM performance

Pedro Alves pmgalves at gmail.com
Fri Jun 15 20:40:30 EDT 2012


Hey - let us know how you did that! Udf in js sounds great!


On Friday, June 15, 2012, Paul Stoellberger wrote:

> Ok that really does work like a charm! (did it in JavaScript)
>
> I think its even a good usecase for such an UDF.
>
> Query time is down to 2.5s from previously at least 15-20s
>
> I hope I'll still find some time to create a real test case for that!
>
> Thanks!
>
> -Paul
>
> On Jun 15, 2012, at 7:48 PM, Julian Hyde wrote:
>
> > Unfortunately CASE syntax can't do that.
> >
> > You might try writing a UDF
> >
> > String bucket(double value) {
> >  return value < 0.3 ? "< 0.3"
> >    : value < 0.5 < ?"0.3-0.49"
> >   : value < 0.8 ? "0-5.-0.79"
> >   : null;
> > }
> >
> > (For extra credit write it in JavaScript and embed it in your schema.)
> Then you can write
> >
> > with member Measures.P as Measures.Sales / Measures.Quantity
> > member Measures.[Price Range] as bucket(Measures.P)
> >
> > and who knows, it might even run faster.
> >
> > Julian
> >
> > On Jun 15, 2012, at 10:41 AM, Paul Stoellberger wrote:
> >
> >> I dont know if i can simplify it even further.
> >> Yes it was always using the same measure. Which was part of the query
> as well (and is computed already anyhow, and that very quickly, so i
> assumed the iif wouldnt change much of that)
> >>
> >> I was trying the same with CASE .. WHEN and had the same issue.
> >>
> >> Additionally i thought I could do something like:
> >>
> >> CASE Measures.P
> >> WHEN < 0.3 THEN  " < 30 "
> >> ELSE null
> >> END
> >>
> >> but that only seems to work with string fields (e.g. i used it with
> Customer.CurrentMember.Name)
> >>
> >> I'll try and reproduce this using foodmart
> >>
> >> -Paul
> >>
> >>
> >> On Jun 15, 2012, at 7:37 PM, Julian Hyde wrote:
> >>
> >>> Please log a bug and attach the SQL that is generated at each pass.
> >>>
> >>> Simplify as much as you can (as long as you still see the behavior,
> obviously).
> >>>
> >>> If the query was using different measures, e.g.
> >>>
> >>> Iif(Measures.P1 < 0.3, Measures.R,
> >>> Iif(Measures.P2 < 0.5, Measures.S,
> >>>  Iif(Measures.P3 < 0.8, Measures.T, Measures.U)))
> >>>
> >>> then Mondrian might take multiple passes because it doesn't have P1 in
> cache in the first pass, so it assumes that Measures.P1 < 0.3 evaluates to
> false; and it doesn't have P2 in cache in the second pass, and so forth.
> >>>
> >>> But if your query really uses only the measure P, I don't see why it's
> making multiple passes.
> >>>
> >>> Julian
> >>>
> >>>
> >>> On Jun 15, 2012, at 10:22 AM, Paul Stoellberger wrote:
> >>>
> >>>> Hi,
> >>>>
> >>>> I have a query that looks like that:
> >>>>
> >>>> with
> >>>> member Measures.P as Measures.Sales / Measures.Quantity
> >>>>
> >>>> member Measures.[Price Range] as
> >>>> IIF( Measures.P is null, null,
> >>>> IIF( Measures.P < 0.3 AND Measures.P < 0.5, '< 0.30',
> >>>> IIF( Measures.P >= 0.3 AND Measures.P < 0.5, '0.30-0.49',
> >>>> IIF( Measures.P >= 0.5 AND Measures.P < 0.8, '0.50-0.79',
> >>>> null
> >>>> ))))
> >>>>
> >>>> select { Measurse.Sales, Measures. Quantity, Measures.[Price Range] }
> ON COLUMNS,
> >>>> { Skus.Skuname.Members } ON ROWS
> >>>> from [Sales]
> >>>>
> >>>>
> >>>> now my problem is that mondrian executes a query for each IF
> statement (or at least execution time increases along with the number of if
> statements)
> >>>>
> >>>> is there any better way to do that? why isn't mondrian calculating P
> and then just running through the if?
> >>>>
> >>>> -Paul
> >>>>
> >>>> _______________________________________________
> >>>> Mondrian mailing list
> >>>> Mondrian at pentaho.org
> >>>> http://lists.pentaho.org/mailman/listinfo/mondrian
> >>>
> >>> _______________________________________________
> >>> Mondrian mailing list
> >>> Mondrian at pentaho.org
> >>> http://lists.pentaho.org/mailman/listinfo/mondrian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20120616/919625c1/attachment.html 


More information about the Mondrian mailing list