[Mondrian] Query performance issues

Tom Barber tom at analytical-labs.com
Fri Oct 2 05:49:27 EDT 2015


Sorry should probably also point out both tests were using the same
mondrian build 3.6.5

Tom

On 2 October 2015 at 10:43, Tom Barber <tom at analytical-labs.com> wrote:

> 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/40642fb3/attachment-0001.html 


More information about the Mondrian mailing list