[Mondrian] Mondrian Query Optimization

Tom Barber tom at wamonline.org.uk
Wed Jun 3 09:56:55 EDT 2009


Ok so after annoying Pedro for long enough he rewrote it for me:

with member [Customer].[Others] as 'Sum(Except({[Customer].[Customer].Members}, TopCount([Customer].[Customer].Members, 3.0, ([Measures].[Total Sales Value], [Branch].CurrentMember,[Date.Fiscal].[All Dates].[2009].[Q4].[10]))))'

  set [Row] as 'Generate(Descendants([Branch].[All Branches], [Branch].[Group], SELF_AND_AFTER), ([Branch].CurrentMember * {Union(TopCount([Customer].[Customer].Members, 3.0, ([Measures].[Total Sales Value], [Branch].CurrentMember,[Date.Fiscal].[All Dates].[2009].[Q4].[10])), {[Customer].[Others]})}))'
select ([Date.Fiscal].[All Dates].[2009].[Q1].[1]:[Date.Fiscal].[All Dates].[2009].[Q4].[12]) ON COLUMNS,
  NON EMPTY {[Row]} ON ROWS
from [Sales]

An interesting side note, I ran it on PSW locally and it took about 8 minutes which is fine, REX to my Pentaho Server on BI Server 3.0 took 18 minutes, which wasn't exactly great, so I upgraded Mondrian on the server to 3.1.1, an behold, 10 minutes, which is perfectly acceptable. Not a bad speedup.

Tom

----- Original Message -----
From: "Tom Barber" <tom at wamonline.org.uk>
To: "Mondrian developer mailing list" <mondrian at pentaho.org>
Sent: Wednesday, 3 June, 2009 11:49:52 AM GMT +00:00 GMT Britain, Ireland, Portugal
Subject: [Mondrian] Mondrian Query Optimization

Hi Guys,

Not technically a dev question, but a problem I can't seem to find anyone who can give me a definitive answer to:
Here's a query that generates a steel wheels top 2 with a sum:

with
member [Customers].[Top2Sum] as
'sum({[Markets].CurrentMember} *
TopCount({[Customers].[Customer].Members}
, 2.0,([Measures].[Sales],[Markets].CurrentMember))
, ([Measures].[Sales],[Markets].CurrentMember))'

set [top20] as 'Generate({Descendants([Markets].[All Markets], [Markets].[Territory], SELF_AND_AFTER)}
,{[Markets].CurrentMember} *
{TopCount([Customers].[Customer].Members, 2.0, [Measures].[Sales])
, [Customers].[Top2Sum]
})'

select {[Measures].[Sales]} ON COLUMNS,
non empty
{[top20]} ON ROWS
from [SteelWheelsSales]
where([Time].[All Years].[2004].[QTR3].[Jul])

I have problems implementing this in a real world scenario because the [Markets].CurrentMember crossjoined to the top count iterates through what appears to be(and would make sense) all customers for all separate markets, but when you do this over 70000 customers and 40 branches, the run time becomes unusable. I would like to move the [Markets].CurrentMember inside the TopCount, that way the customers are selected for one the branch in question and the run time drops back down to a few minutes, but then the Top2Sum Member doesn't work which would then go one to generate a pareto total, All Customers - Top2Sum.

So the question being, is that doable, and if so how, because no matter what I do after that I get a `Mondrian Error:All arguments to function '{}' must have same hierarchy. `

Why do I find it so hard to iterate through all my branches, and generate a Top20 customers and a pareto ? :)

Cheers

Tom


_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian





More information about the Mondrian mailing list