[Mondrian] performance impact of having several cross joins ofdimensions at the All level

[Julian Hyde]

What happens if you replace the All members with another member from the
same dimension?
 
I hypothesize that the slow running time is due to the sheer number of
nested invocations - O(2^N) - not a problem specific to All.
 
A variant of the NonEmptyCrossJoin function which took an arbitrary number
of arguments might be a solution. Then at least the arguments to the
function would only need to be evaluated once. The set constructor operator
'{ ... }' may look trivial, but it adds up.
 
Also try wrapping the arguments to each NonEmptyCrossJoin in the Cache
operator. (This would require a code change to
NonEmptyCrossJoinFunDef.java.) Would have the same effect.
 
Julian


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Robin Tharappel
Sent: Wednesday, August 29, 2007 6:25 PM
To: mondrian at pentaho.org
Subject: [Mondrian] performance impact of having several cross joins
ofdimensions at the All level



Hello, 

 

We have observed a significant slow down in performance when the there are
several nested NonEmptyCrossJoin calls in an MDX query. Below is an example
query that shows the non empty cross join of about 40 dimensions, however
only 3 dimensions are not at the All level ( Dim18, Dim20, and Dim28). We
see a substantial improvement in performance when the dimensions at the All
level are removed. The performance impact appears to be in lower CPU
utilization for Mondrian. Since the other dimensions are at the All level
the expectation is that not much processing would not be required for them.
What is the overhead incurred for a dimension at the All level in this case
? 

 

WITH SET [#DataSet#] as
'NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(Non
EmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmpt
yCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCro
ssjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjo
in(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(N
onEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEm
ptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyC
rossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCross
join(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin
(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin(NonEmptyCrossjoin({[D
im1].[All]}, {[Dim2].[All]}), {[Dim3].[All]}), {[Dim4].[All]}),
{[Dim5].[All]}), {[Dim6].[All]}), {[Dim7].[All]}), {[Dim8].[All]}),
{[Dim9].[All]}), {[Dim10].[All]}), {[Dim11].[All]}), {[Dim12].[All]}),
{[Dim13].[All]}), {[Dim14].[All]}), {[Dim15].[All]}), {[Dim16].[All]}),
{[Dim17].[All]}), {Descendants([Dim18].[All], 3)}), {[Dim19].[All]}),
{[Dim20].[All].Children}), {[Dim21].[All]}), {[Dim22].[All]}),
{[Dim23].[All]}), {[Dim24].[All]}), {[Dim25].[All]}), {[Dim26].[All]}),
{[Dim27].[All]}), {Descendants([Dim28].[All], 3)}), {[Dim29].[All]}),
{[Dim30].[All]}), {[Dim31].[All]}), {[Dim32].[All]}), {[Dim33].[All]}),
{[Dim34].[All]}), {[Dim35].[All]}), {[Dim36].[All]}), {[Dim37].[All]}),
{[Dim38].[All]}), {[Dim39].[All]}), {[Dim40].[All]}), {[Dim41].[All]}),
{[Dim42].[All]}), {[Dim43].[All]})' SELECT {[Measures].[Measure1],
[Measures].[Measure2], [Measures].[Measure3]} on columns, NON EMPTY
Hierarchize({[#DataSet#]}) on rows FROM [Transaction]

 

Thanks,

 

Robin

 



  _____  

The information contained in this email may be confidential and/or legally
privileged. It has been sent for the sole use of the intended recipient(s).
If the reader of this message is not an intended recipient, you are hereby
notified that any unauthorized review, use, disclosure, dissemination,
distribution, or copying of this communication, or any of its contents, is
strictly prohibited. If you have received this communication in error,
please contact the sender by reply email and destroy all copies of the
original message. Thank you





-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20070830/414ea3a3/attachment.html