[Mondrian] Aggregate Tables and Supremely Dodgy SQL

Tom Barber tom at wamonline.org.uk
Wed Feb 25 06:32:58 EST 2009

Hi Guys,

I have been playing with PAD and mondrian for the last few days on a lucid DB, and I've created the attached schema. Ignoring the general ugliness of the tables I've created I have a specific query that I've been trying to run on it and the sql generated isn't all that nice...

select NON EMPTY {([Date.Date].[All Dates].[2008].[Q1].[January] : [Date.Date].[All Dates].[2008].[Q4].[December])} ON COLUMNS,
TopCount({[Customer.Customers].[All Customers].Children}, 20.0, ([Measures].[Quantity], [Date.Date].[All Dates].[2008].[Q4].[December])) ON rows

from [Sales]
where [Movement Type.Movement Type].[All Movement Types].[002]

When I've got Agg tables switched off the result takes about 30 seconds to generate and works as expected, on the other hand when I switch on the agg tables, the sql generated and sent to lucid is the following...

select "d_customers"."entity_code" as "c0" from "MFourSchem_Sales_13" as "MFourSchem_Sales_13", "d_customers" as "d_customers" where "MFourSchem_Sales_13"."d_movement_type_Movement_Type" = '002' group by "
d_customers"."entity_code" order by "d_customers"."entity_code" ASC

And the result set on my last run took ~5000 seconds to generate which aint great, that being said,

 select "d_customers"."entity_code" as "c0" from "MFourSchem_Sales_13", "d_customers" where "MFourSchem_Sales_13"."d_movement_type_Movement_Type" = '002' 
AND "d_customers"."entity_code" = "MFourSchem_Sales_13"."d_customers_Customer_Code"
group by "d_customers"."entity_code" order by "d_customers"."entity_code" ASC

Takes about .5 of a second which is nice, but I had to manually add the `AND "d_customers"."entity_code" = "MFourSchem_Sales_13"."d_customers_Customer_Code"`

Is this a bug, my crappy schema design(its not real its just my tests), or something else?


-------------- next part --------------
A non-text attachment was scrubbed...
Name: MFour.mondrian.xml
Type: text/xml
Size: 26721 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20090225/853386b4/attachment.xml 

More information about the Mondrian mailing list