[Mondrian] Query performance issues

[Tom Barber]

Hello folks,

I had a query about MDX query performance between Saiku 2 and 3 and so we
did some testing and I'd like some expert analysis if anyone has a spare 10
minutes:

SELECT
NON EMPTY {Hierarchize({[Measures].[Unit Sales]})} ON COLUMNS,
NON EMPTY Hierarchize(Union(CrossJoin([Time].[Year].Members,
CrossJoin({[Store].[USA].[OR]}, CrossJoin([Customers].[Name].Members,
[Product].[Product Name].Members))), Union(CrossJoin([Time].[Year].Members,
CrossJoin(Filter({[Store].[USA].[OR].[Portland]},
(Exists(Ancestor([Store].CurrentMember, [Store].[Store State]),
{[Store].[USA].[OR]}).Count  > 0)), CrossJoin([Customers].[Name].Members,
[Product].[Product Name].Members))), Union(CrossJoin({[Time].[1997].[Q3]},
CrossJoin({[Store].[USA].[OR]}, CrossJoin([Customers].[Name].Members,
[Product].[Product Name].Members))), CrossJoin({[Time].[1997].[Q3]},
CrossJoin(Filter({[Store].[USA].[OR].[Portland]},
(Exists(Ancestor([Store].CurrentMember, [Store].[Store State]),
{[Store].[USA].[OR]}).Count  > 0)), CrossJoin([Customers].[Name].Members,
[Product].[Product Name].Members))))))) ON ROWS
FROM [Sales]

This is the MDX created by an old Saiku 2.6 server and the query executes
in about 20 seconds.

with set [~Time_Time_Year] as 'Exists({[Time].[Time].[Year].Members},
[~Time_Time_Quarter])'
  set [~Time_Time_Quarter] as '{[Time].[Time].[1997].[Q3]}'
  set [~ROWS_Time_Time] as 'Hierarchize({[~Time_Time_Year],
[~Time_Time_Quarter]})'
  set [~Store_Stores_Store State] as '{[Store].[Stores].[USA].[OR]}'
  set [~Store_Stores_Store City] as
'Exists({[Store].[Stores].[USA].[OR].[Portland]}, [~Store_Stores_Store
State])'
  set [~ROWS_Store_Stores] as 'Hierarchize({[~Store_Stores_Store State],
[~Store_Stores_Store City]})'
  set [~ROWS_Customer_Customers] as
'{[Customer].[Customers].[Name].Members}'
  set [~ROWS_Product_Products] as '{[Product].[Products].[Product
Name].Members}'
select NON EMPTY {[Measures].[Unit Sales]} ON COLUMNS,
  NON EMPTY (Crossjoin(Crossjoin(Crossjoin([~ROWS_Time_Time],
[~ROWS_Store_Stores]), [~ROWS_Customer_Customers]),
[~ROWS_Product_Products])) ON ROWS
from [Sales]

This is the much cleaner MDX from the 3.x server, but this query timesout
every time. Can someone shed any light on why the performance is so much
worse? I've tried cellbatch size and stuff but none of it makes a shred of
difference.

Thanks

Tom
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20151002/8fd3a485/attachment.html