[Mondrian] CM performance

Julian Hyde jhyde at pentaho.com
Fri Jun 15 13:48:08 EDT 2012


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
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian



More information about the Mondrian mailing list