Or... is there a restriction on having 2 dimensions on the same fact table with same primary key/foreign key?<br><br>specifically we have:<br><br><Dimension name="Period2" foreignKey="pillangoperiodid">
<br> <Hierarchy hasAll="true" allMemberName="All Periods" primaryKey="id"><br> <Table name="plan_periodgroups_olap_view"/><br> <Level name="Plan Period Group" column="itemgroupid" nameColumn="itemgroupname" uniqueMembers="true"/>
<br> <Level name="Period" column="periodname" ordinalColumn="startdate" uniqueMembers="false"/><br> </Hierarchy><br> </Dimension><br><br> <Dimension name="Period" foreignKey="pillangoperiodid">
<br> <Hierarchy hasAll="true" allMemberName="All Periods" primaryKey="id"><br> <Table name="period"/><br> <Level name="Year" column="yearname" uniqueMembers="true"/>
<br> <Level name="Period" column="name" ordinalColumn="startdate" uniqueMembers="true"/><br> </Hierarchy><br> </Dimension><br><br>where the "plan_periodgroups_olap_view" table is a view on the "period" table (and a grouping table) that allows us to create whatever selections of periods are desired (in a 'group').. the PK id in both case is the same id from the "period" table and the FK is the same column in the fact table.
<br><br>in each case, the AggGen outputs this as one of the aggregate tables:<br><br>CREATE TABLE agg_l_XXX_invoice_and_item (<br> ledgerid INT8,<br> pillangoperiodid INT8,<br> amount FLOAT8,<br> fact_count INTEGER NOT NULL
<br>);<br><br>20:40:50,930 INFO [STDOUT] insertIntoLost:<br>INSERT INTO agg_l_XXX_invoice_and_item (<br> ledgerid,<br> pillangoperiodid,<br> amount,<br> fact_count)<br>SELECT<br> "invoice_and_item"."ledgerid" AS "ledgerid",
<br> "invoice_and_item"."pillangoperiodid" AS "pillangoperiodid",<br> SUM("INVOICE_AND_ITEM"."AMOUNT") AS "amount", <--- this caps seems to be a bug for any case sensitive rdbms.. btw :)
<br> COUNT(*) AS "fact_count"<br>FROM<br> "invoice_and_item" "invoice_and_item"<br>GROUP BY<br> "invoice_and_item"."ledgerid",<br> "invoice_and_item"."pillangoperiodid";
<br><br><br>I'll try to do something similar with Foodmart... as long as you can confirm that we're not doing something that should not be allowed (the MDX runs fine, data ouput is correct etc.. just the agg tables don't work..)
<br><br>Cheers<br>Ati<br><br><br><div><span class="gmail_quote">On 10/18/07, <b class="gmail_sendername">Julian Hyde</b> <<a href="mailto:julianhyde@speakeasy.net">julianhyde@speakeasy.net</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<div dir="ltr" align="left"><span><font color="#000080" face="Verdana" size="2">Can you clarify? Period and Period2 are based on the same
dimension table, with the same primary key, and the same foreign key from the
fact table? </font></span></div>
<div dir="ltr" align="left"><span><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#000080" face="Verdana" size="2">At this point, it sounds like a bug in the aggregate table
recognizer algorithm. There may be a bug logged already. If you can convert into
a testcase on the foodmart schema that would help.</font></span></div>
<div dir="ltr" align="left"><span><font color="#000080" face="Verdana" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#000080" face="Verdana" 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>
<font face="Tahoma" size="2"><b>From:</b> <a href="mailto:mondrian-bounces@pentaho.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">mondrian-bounces@pentaho.org</a>
[mailto:<a href="mailto:mondrian-bounces@pentaho.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">mondrian-bounces@pentaho.org</a>] <b>On Behalf Of </b>Ati
Rosselet<br><b>Sent:</b> Thursday, October 18, 2007 2:58 AM<br><b>To:</b>
<a href="mailto:mondrian@pentaho.org" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">mondrian@pentaho.org</a><br><b>Subject:</b> [Mondrian] Problem with aggregate
tables..<br></font><br></div><div><span class="e" id="q_115b426283953e3c_1">
<div></div>Hi, I'll try to be clear here. <br>We are running mondrian
2.4 with the Aggregate query generation turned on at the moment and we have
the following, odd situation:<br><br>We have 2 dimensions (Period and
Period2), both of which reference the same set of id's <br>We have also
created the appropriate aggregate table for a certain level, and all works
fine.. kind of.<br><br>The problem is that when the query using "Period" runs,
the aggregate table we created is shown on the console, and the database logs
show that it used, and consequently everything is FAST!! However, when
using the "Period2" dimension, although the EXACT SAME table is shown on the
console as a suggested aggregate table.. it is never used. <br><br>I
could understand if it was finding a 'better' table and using that instead,
but rather it is not using any aggregate table at all.
??????<br><br>What situation could cause the processing of an MDX query to
list "suggested" tables, and then NOT use one of them if it exists? <br>We've
been trying, tweaking etc.. and no change.. at a complete loss
here... any ideas?<br><br>Cheers<br>Ati<br><br><br>(Julian... just found the
new mailing list - thnx... did you shut down the old spamful one?)
<br></span></div></blockquote></div>
<br>_______________________________________________<br>Mondrian mailing list<br><a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br><a onclick="return top.js.OpenExtLink(window,event,this)" href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">
http://lists.pentaho.org/mailman/listinfo/mondrian</a><br><br></blockquote></div><br>