Thanks Kurt and Julian,<br> Julian as you pointed out rightly, end-user does not want to see such a huge data. The idea here is prepopulate the cube for all combination of dimensions, so that any MDX query is answered from the cache hit rather than hitting the DB. I can make this prepopulation a nightly job. I ve almost 10 dimensions, on an average each dimension has 15 rows and 750,000 rows in a fact table. I Use 128 GB RAM and mondrian is connected to Oracle database.<br>
<br> I am attempting to cache all the data in RAM, please advise if this approach is valid or not ?? and also i would like to understand the way non empty cross join works. from what i observed from the log, data structures for storing the results are prepared before issuing the SQL.Does the below step prepare/create the data structure for the combination 6 dimensions mentioned below?<br>
<br><b>DEBUG http-8080-4
mondrian.rolap.FastBatchingCellReader - FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000000011011110000<br>
Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"<br> Product (5):
"PRODUCT_DIMENSION"."PRODUCT_NAME"<br> Year (6):
"MONTH_DIMENSION"."CALENDER_YEAR"<br> Quarter (7):
"MONTH_DIMENSION"."CALENDER_QUARTER"<br> Month (Key) (9):
"MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"<br> TOD (10):
"TIME_OF_DAY_DIMENSION"."TOD_NAME" </b><br><br> There is a sharp increase in RAM usage, when (nonempty)crossjoin is attempted for more than 6 dimensions ( I tried up to ten) and no SQL queries are issued at this stage yet. What is mondrian doing at this moment ?? I have also enable native execution for non empty cross join , still I face this issue. <br>
<br>Does mondrian attempt to perform a cartesian join in memory if a plain cross join is attempted? If i use NonEmptyCrossJoin how are the missing combinations handled? if i request for a combination of dimensions which is not available in the cache ( and DB as well ), does it fire a SQL to check the availability in the DB or based on the NonEmptyCrossJoin attempted earlier, it returns an empty value?<br>
How does NonEmptyCrossJoin and CrossJoin differ? is data structure preparation is required in both the cases? <br><br>I am not sure if I am making sense, please respond incase i am not clear. Thanks a lot.<br><br>Thanks,<br>
Venki<br><br><br><div class="gmail_quote">On Wed, Oct 20, 2010 at 9:24 PM, Julian Hyde <span dir="ltr"><<a href="mailto:jhyde@pentaho.com">jhyde@pentaho.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div>
<div><span><font color="#000080" face="Lucida Sans" size="2">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.</font></span></div>
<div><span><font color="#000080" face="Lucida Sans" size="2"></font></span> </div>
<div><span><font color="#000080" face="Lucida Sans" size="2">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.</font></span></div>
<div><span><font color="#000080" face="Lucida Sans" size="2"></font></span> </div>
<div><span><font color="#000080" face="Lucida Sans" size="2">Learn MDX, and find a way to express the query that the
end-user actually wants to see.</font></span></div>
<div><span><font color="#000080" face="Lucida Sans" size="2"></font></span> </div>
<div><span><font color="#000080" face="Lucida Sans" size="2">Julian</font></span></div><br>
<blockquote style="border-left: 2px solid rgb(0, 0, 128); padding-left: 5px; margin-left: 5px; margin-right: 0px;">
<div dir="ltr" align="left" lang="en-us">
<hr><div class="im">
<font face="Tahoma" size="2"><b>From:</b> <a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a>
[mailto:<a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a>] <b>On Behalf Of </b>Venkatesh
U<br><b>Sent:</b> Wednesday, October 20, 2010 4:07 AM<br><b>To:</b>
<a href="mailto:mondrian@pentaho.org" target="_blank">mondrian@pentaho.org</a><br><b>Subject:</b> [Mondrian] Issue with Cross Join 10
dimensions<br></font><br></div></div><div><div></div><div class="h5">
<div></div>Hi ,<br><br> 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 <br><br>select
{<br>CrossJoin([Service].Members,CrossJoin([Product].Members,CrossJoin([Month].Members,CrossJoin([TOD].Members,CrossJoin([IO].Members,[ForeignNetwork].Members)))))<br>}
on rows,<br>{[Measures].Members}<br>on columns<br>from
[ServiceFactsByMonthWC]<br><br>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,<br><br><br>DEBUG http-8080-4 mondrian.rolap.FastBatchingCellReader -
FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000001101011110000<br>
Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"<br> Product (5):
"PRODUCT_DIMENSION"."PRODUCT_NAME"<br> Year (6):
"MONTH_DIMENSION"."CALENDER_YEAR"<br> Quarter (7):
"MONTH_DIMENSION"."CALENDER_QUARTER"<br> Month (Key) (9):
"MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"<br> IO (11):
"INCOMING_OUTGOING_DIMENSION"."DIRECTION"<br> ForeignNetworkName (12):
"FOREIGN_NETWORK_DIMENSION"."FND_NAME"<br><br>DEBUG http-8080-4
mondrian.rolap.FastBatchingCellReader - FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000000011011110000<br>
Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"<br> Product (5):
"PRODUCT_DIMENSION"."PRODUCT_NAME"<br> Year (6):
"MONTH_DIMENSION"."CALENDER_YEAR"<br> Quarter (7):
"MONTH_DIMENSION"."CALENDER_QUARTER"<br> Month (Key) (9):
"MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"<br> TOD (10):
"TIME_OF_DAY_DIMENSION"."TOD_NAME"<br><br>DEBUG http-8080-4
mondrian.rolap.FastBatchingCellReader - FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000001011011110000<br>
Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"<br> Product (5):
"PRODUCT_DIMENSION"."PRODUCT_NAME"<br> Year (6):
"MONTH_DIMENSION"."CALENDER_YEAR"<br> Quarter (7):
"MONTH_DIMENSION"."CALENDER_QUARTER"<br> Month (Key) (9):
"MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"<br> TOD (10):
"TIME_OF_DAY_DIMENSION"."TOD_NAME"<br> ForeignNetworkName (12):
"FOREIGN_NETWORK_DIMENSION"."FND_NAME"<br><br>DEBUG http-8080-4
mondrian.rolap.FastBatchingCellReader - FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000000111011110000<br>
Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"<br> Product (5):
"PRODUCT_DIMENSION"."PRODUCT_NAME"<br> Year (6):
"MONTH_DIMENSION"."CALENDER_YEAR"<br> Quarter (7):
"MONTH_DIMENSION"."CALENDER_QUARTER"<br> Month (Key) (9):
"MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"<br> TOD (10):
"TIME_OF_DAY_DIMENSION"."TOD_NAME"<br> IO (11):
"INCOMING_OUTGOING_DIMENSION"."DIRECTION"<br><br>DEBUG http-8080-4
mondrian.rolap.FastBatchingCellReader - FastBatchingCellReader:
bitkey=0x0000000000000000000000000000000000000000000000000001111011110000<br>
Service (4): "SERVICE_DIMENSION"."SERVICE_NAME"<br> Product (5):
"PRODUCT_DIMENSION"."PRODUCT_NAME"<br> Year (6):
"MONTH_DIMENSION"."CALENDER_YEAR"<br> Quarter (7):
"MONTH_DIMENSION"."CALENDER_QUARTER"<br> Month (Key) (9):
"MONTH_DIMENSION"."CALENDER_MONTH_NUMBER_IN_YEAR"<br> TOD (10):
"TIME_OF_DAY_DIMENSION"."TOD_NAME"<br> IO (11):
"INCOMING_OUTGOING_DIMENSION"."DIRECTION"<br> ForeignNetworkName (12):
"FOREIGN_NETWORK_DIMENSION"."FND_NAME"<br><br><br><br><br><br></div></div></blockquote></div>
<br>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div><br><div style="visibility: hidden; display: inline;" id="avg_ls_inline_popup"></div><style type="text/css">#avg_ls_inline_popup { position:absolute; z-index:9999; padding: 0px 0px; margin-left: 0px; margin-top: 0px; width: 240px; overflow: hidden; word-wrap: break-word; color: black; font-size: 10px; text-align: left; line-height: 13px;}</style>