[Mondrian] The perils of the crossjoin optimizer

Matt Campbell mcampbell at pentaho.com
Fri Nov 6 09:18:46 EST 2015

Mondrian's crossjoin optimizer acts as a fall back to native crossjoin, applying an alternative optimization strategy in cases where native evaluation was not possible or disabled.  It works by loading cell data for crossjoined tuples to eliminate empty intersections.  Loading these cells can be an added cost, but often that's okay since the cells may have been needed anyway, if not by this query then potentially by similar queries.

There are scenarios where the cost can be excessive, however.  The MDX below loads detail rows with a grand total.  In this case, the second crossjoin (of two "Total" calculated members) cannot be natively evaluated, so the crossjoin optimizer gets a shot.  Since the WHERE slicer is also a calculated member, however, this results in an unconstrained SQL query against the fact table.  That a big expense to reduce a tuple set that's already of size 1.

The default crossjoin optimizer threshold has a value of 0 tuples, meaning it always kicks in.  I'm not sure what the ideal default is, but it seems to me that in most cases with tiny sets the risk of expensive SQL outweighs the benefit.  A setting somewhere in the 10-100 region would eliminate many SQL queries with total/subtotal MDX like the one below.


member Store.[Store Total] as
    'Aggregate([Store].[Store State].[WA].children)'
member Product.[Product Total] as
    'Aggregate({[Product].[All Products].[Drink], [Product].[All Products].[Food]})'
member [Education Level].[Education Filter] as
    'Aggregate({[Education Level].[All Education Level].[Bachelors Degree],
       [Education Level].[All Education Level].[Graduate Degree]})'

SELECT Measures.[Unit Sales] on 0,
       CrossJoin([Store].[Store State].[WA].children,
                  {[Product].[All Products].[Drink], [Product].[All Products].[Food]}),
       CrossJoin(Store.[Store Total], Product.[Product Total])) on 1
FROM Sales
   [Education Level].[Education Filter]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20151106/03ca6445/attachment.html 

More information about the Mondrian mailing list