[Mondrian] Mondrian Query Optimization

Tom Barber tom at wamonline.org.uk
Wed Jun 3 06:49:52 EDT 2009


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





More information about the Mondrian mailing list