[Mondrian] Issue with Cross Join 10 dimensions

Julian Hyde jhyde at pentaho.com
Wed Oct 20 11:54:01 EDT 2010


It isn't really surprising that big crossjoins take longer: they produce
more tuples. The number of tuples is exponential in the number of sets being
combined.
 
I'm guessing that your query produces many thousands, possibly millions of
rows. I doubt that any end-user wants to see that many rows.
 
Learn MDX, and find a way to express the query that the end-user actually
wants to see.
 
Julian


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Venkatesh U
Sent: Wednesday, October 20, 2010 4:07 AM
To: mondrian at pentaho.org
Subject: [Mondrian] Issue with Cross Join 10 dimensions


Hi ,

  What I observed generally is it takes very longer when i try to do a
crossjoin with more than 7 dimensions. I am trying to run the below query it
runs for a very longer time 

select {
CrossJoin([Service].Members,CrossJoin([Product].Members,CrossJoin([Month].Me
mbers,CrossJoin([TOD].Members,CrossJoin([IO].Members,[ForeignNetwork].Member
s)))))
} on rows,
{[Measures].Members}
on columns
from [ServiceFactsByMonthWC]

Below is the tail of log file where mondrian is spending lot of time. What
is it doing? Is there any way to optimize cross joins? . I am trying to use
cross join as an option to prepopulate the Cube with a single MDX query, so
that the subsequent executions will be faster, is there any better way to
prepopulate the Cube, please let me know if there are any,


DEBUG http-8080-4 mondrian.rolap.FastBatchingCellReader -
FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000001101011110000
  Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"
  Product (5): "PRODUCT_DIMENSION"."PRODUCT_NAME"
  Year (6): "MONTH_DIMENSION"."CALENDER_YEAR"
  Quarter (7): "MONTH_DIMENSION"."CALENDER_QUARTER"
  Month (Key) (9): "MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"
  IO (11): "INCOMING_OUTGOING_DIMENSION"."DIRECTION"
  ForeignNetworkName (12): "FOREIGN_NETWORK_DIMENSION"."FND_NAME"

DEBUG http-8080-4 mondrian.rolap.FastBatchingCellReader -
FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000000011011110000
  Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"
  Product (5): "PRODUCT_DIMENSION"."PRODUCT_NAME"
  Year (6): "MONTH_DIMENSION"."CALENDER_YEAR"
  Quarter (7): "MONTH_DIMENSION"."CALENDER_QUARTER"
  Month (Key) (9): "MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"
  TOD (10): "TIME_OF_DAY_DIMENSION"."TOD_NAME"

DEBUG http-8080-4 mondrian.rolap.FastBatchingCellReader -
FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000001011011110000
  Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"
  Product (5): "PRODUCT_DIMENSION"."PRODUCT_NAME"
  Year (6): "MONTH_DIMENSION"."CALENDER_YEAR"
  Quarter (7): "MONTH_DIMENSION"."CALENDER_QUARTER"
  Month (Key) (9): "MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"
  TOD (10): "TIME_OF_DAY_DIMENSION"."TOD_NAME"
  ForeignNetworkName (12): "FOREIGN_NETWORK_DIMENSION"."FND_NAME"

DEBUG http-8080-4 mondrian.rolap.FastBatchingCellReader -
FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000000111011110000
  Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"
  Product (5): "PRODUCT_DIMENSION"."PRODUCT_NAME"
  Year (6): "MONTH_DIMENSION"."CALENDER_YEAR"
  Quarter (7): "MONTH_DIMENSION"."CALENDER_QUARTER"
  Month (Key) (9): "MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"
  TOD (10): "TIME_OF_DAY_DIMENSION"."TOD_NAME"
  IO (11): "INCOMING_OUTGOING_DIMENSION"."DIRECTION"

DEBUG http-8080-4 mondrian.rolap.FastBatchingCellReader -
FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000001111011110000
  Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"
  Product (5): "PRODUCT_DIMENSION"."PRODUCT_NAME"
  Year (6): "MONTH_DIMENSION"."CALENDER_YEAR"
  Quarter (7): "MONTH_DIMENSION"."CALENDER_QUARTER"
  Month (Key) (9): "MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"
  TOD (10): "TIME_OF_DAY_DIMENSION"."TOD_NAME"
  IO (11): "INCOMING_OUTGOING_DIMENSION"."DIRECTION"
  ForeignNetworkName (12): "FOREIGN_NETWORK_DIMENSION"."FND_NAME"







-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20101020/62a3ce14/attachment.html 


More information about the Mondrian mailing list