<div dir="ltr">On debugging mondrian source code, it looks like an issue with the <a href="https://github.com/OSBI/mondrian/blob/sparksupport/src/main/java/mondrian/spi/impl/PhoenixDialect.java">PhoenixDialect</a>. Since the dialect doesn't return any statistics provider implementations, the code isn't able to get the row count and hence the fact table gets queried. I tried fixing the issue by calling super.getStatisticsProviders(). I didn't follow a couple of things behind this logic<div><br></div><div>1. Why does mondrian rely on the rowCount to make a decision on whether to use aggregate tables or not?</div><div>2. Why does the code check if the aggregate table has more number of rows than the fact table - I am referring to the code <a href="https://github.com/OSBI/mondrian/blob/sparksupport/src/main/java/mondrian/rolap/RolapGalaxy.java#L199">here</a>?</div><div><br></div><div>I will appreciate to get some inputs from the team.</div><div><br></div><div>Thanks,</div><div>Amit.</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Sat, Apr 2, 2016 at 3:40 PM, Amit Shah <span dir="ltr"><<a href="mailto:amits.84@gmail.com" target="_blank">amits.84@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Any inputs here? I'm sure having pre-aggregated tables must be a pretty common practice. <div>I'm kind of blocked with this not working.</div><div><br></div><div>Thanks,</div><div>Amit.</div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Apr 1, 2016 at 5:34 PM, Tom Barber <span dir="ltr"><<a href="mailto:tom@analytical-labs.com" target="_blank">tom@analytical-labs.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">I'll extend this post slightly to add:<div><br></div><div>We've enabled aggregates in mondrian.properties, and we added a Aggregate only measure and using that one forced the use of the aggregate table, so the aggregates are being parsed. </div></div><div class="gmail_extra"><br clear="all"><div><div><div dir="ltr"><div><div dir="ltr">--------------<div><br></div><div><div style="font-size:small"><font color="#999999">Director Meteorite.bi - Saiku Analytics Founder</font></div><div style="font-size:small"><font color="#999999">Tel: +44(0)5603641316 </font></div><div style="font-size:small"><font color="#999999"><br></font></div><div style="font-size:small"><font color="#999999">(Thanks to the Saiku community we reached our <a href="http://kickstarter.com/projects/2117053714/saiku-reporting-interactive-report-designer/" target="_blank">Kickstart</a> goal, but you can always help by <a href="http://www.meteorite.bi/products/saiku/sponsorship" target="_blank">sponsoring the project</a>)</font></div></div></div></div></div></div></div>
<br><div class="gmail_quote"><div><div>On 1 April 2016 at 12:59, Amit Shah <span dir="ltr"><<a href="mailto:amits.84@gmail.com" target="_blank">amits.84@gmail.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div dir="ltr"><span style="font-family:Arial,Helvetica,sans-serif;font-size:13px">Hello,</span><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px"><br></div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px">I have been using Mondrian 4 with Saiku as an OLAP engine. Mondrian is integrated with apache phoenix (version 4.6) as the data source that used apache hbase as the data store. In order to speed up the queries I am trying to make pre-aggregated tables work but for some reason the actual fact table is getting queried. Here's how the mondrian schema definition looks like - <a href="http://pastebin.com/xquutKYX" rel="nofollow" style="margin:0px;padding:0px;border:0px;text-decoration:none;color:rgb(102,17,204)" target="_blank">http://pastebin.com/xquutKYX</a>.</div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px"><br></div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px">The database schema for the dimension, facts and the pre-aggregated table is shared here - <a href="http://pastebin.com/EWhyNLTJ" rel="nofollow" style="margin:0px;padding:0px;border:0px;text-decoration:none;color:rgb(102,17,204)" target="_blank">http://pastebin.com/EWhyNLTJ</a></div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px"><br></div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px">Selecting the total sales measure and the agent id as a row in the saiku builds up a query and tries to execute it on the facts table instead of the pre-aggregated table. The query from the sql logs is</div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px"><br></div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px">DEBUG [mondrian.sql] 850: Segment.load: executing sql [select "AGENTS"."A_ID" as "c0", sum("TRANSACTIONS"."TOTAL_SALES") as "m0" from "TRANSACTIONS" as "TRANSACTIONS", "AGENTS" as "AGENTS" where "TRANSACTIONS"."AGENT_ID" = "AGENTS"."A_ID" group by "AGENTS"."A_ID"]<br></div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px"><br></div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px">What could be missing in order to make the OLAP engine to use the AG_TER_PRO (pre-aggregated table)?</div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px"><br></div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px">Thanks,</div><div style="margin:0px;padding:0px;border:0px;font-family:Arial,Helvetica,sans-serif;font-size:13px">Amit.</div></div>
<br></div></div>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div><br></div>
<br>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div><br></div>
</div></div></blockquote></div><br></div>