[Mondrian] query model 2.0 / named set performance?

Nicholas Goodman ngoodman at bayontechnologies.com
Fri Mar 23 17:41:26 EDT 2012


Paul,

I think I was the one that might have planted the bug in your ear on the performance aspects.  If I recall (and others on this list might have more to add) the performance impact is only if you are doing the NonEmptyCrossJoin in the named sets outside the context of a query axis.  NECJ often needs to evaluate with the fact table in place (slower queries) to come up with just the member set and then the query is evaluated.  If I recall (and I don't know if this is still the case) NECJ had a separate cache as well so when you went to the main fact table you ended up doing 2 queries against the fact table.

All that to say, I think you're generally safe as long as you do the CJ (or NECJ) on the axis.  Beware however dimensions with HUGE numbers of members.  Mondrian has optimizations to limit in memory members and push down the NECJ semantics to SQL but if you create them as named sets I believe the entire set of members is loaded into memory (10s of millions?).  Maybe you can add in a safeguard (Head() the sets to limit to the first 1000 or so since that's all that could practically be displayed on a report anyhow?

Again, I'm speaking from dusty memories of 2-3 year old conversations on this.  Luc/Julian may have more up to date info.

Nick
> A query could e.g. look like this:
> 
> with 
> set [ProductFamily] as '{[Product].[Food]}'
> set [ProductDepartment] as 'Filter({[Product].[Food].[Dairy], [Product].[Food].[Meat]}, Ancestor(Product.CurrentMember,[Product Family]) in [ProductFamily])'
> set [ProductCategory] as 'Filter({[Product].[Product Category].Members}, Ancestor(Product.CurrentMember,[Product
> Department]) in [ProductDepartment])'
> set [ProductDimension] as 'Hierarchize({[ProductFamily], [ProductDepartment],[ProductCategory]})'
> SELECT
> NON EMPTY {[Customers].[All Customers]} ON COLUMNS,
> NON EMPTY [ProductDimension] ON ROWS
> FROM [Sales]
> 
> on an axis i would then just do something like:
> SELECT [Dimension1] * [Dimension2] ON COLUMNS, .....
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20120323/72d23075/attachment.html 


More information about the Mondrian mailing list