[Mondrian] Making Pre-aggregated Tables Work

Amit Shah amits.84 at gmail.com
Mon Apr 4 10:22:47 EDT 2016


On debugging mondrian source code, it looks like an issue with the
PhoenixDialect
<https://github.com/OSBI/mondrian/blob/sparksupport/src/main/java/mondrian/spi/impl/PhoenixDialect.java>.
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

1. Why does mondrian rely on the rowCount to make a decision on whether to
use aggregate tables or not?
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 here
<https://github.com/OSBI/mondrian/blob/sparksupport/src/main/java/mondrian/rolap/RolapGalaxy.java#L199>
?

I will appreciate to get some inputs from the team.

Thanks,
Amit.

On Sat, Apr 2, 2016 at 3:40 PM, Amit Shah <amits.84 at gmail.com> wrote:

> Any inputs here? I'm sure having pre-aggregated tables must be a pretty
> common practice.
> I'm kind of blocked with this not working.
>
> Thanks,
> Amit.
>
> On Fri, Apr 1, 2016 at 5:34 PM, Tom Barber <tom at analytical-labs.com>
> wrote:
>
>> I'll extend this post slightly to add:
>>
>> 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.
>>
>> --------------
>>
>> Director Meteorite.bi - Saiku Analytics Founder
>> Tel: +44(0)5603641316
>>
>> (Thanks to the Saiku community we reached our Kickstart
>> <http://kickstarter.com/projects/2117053714/saiku-reporting-interactive-report-designer/>
>> goal, but you can always help by sponsoring the project
>> <http://www.meteorite.bi/products/saiku/sponsorship>)
>>
>> On 1 April 2016 at 12:59, Amit Shah <amits.84 at gmail.com> wrote:
>>
>>> Hello,
>>>
>>> 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 - http://pastebin.com/xquutKYX.
>>>
>>> The database schema for the dimension, facts and the pre-aggregated
>>> table is shared here - http://pastebin.com/EWhyNLTJ
>>>
>>> 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
>>>
>>> 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"]
>>>
>>> What could be missing in order to make the OLAP engine to use the
>>> AG_TER_PRO (pre-aggregated table)?
>>>
>>> Thanks,
>>> Amit.
>>>
>>> _______________________________________________
>>> Mondrian mailing list
>>> Mondrian at pentaho.org
>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>
>>>
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160404/c98d290b/attachment.html 


More information about the Mondrian mailing list