[Mondrian] CM performance

[Paul Stoellberger]

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