[Mondrian] Mondrian generating SQL that cannot be indexed

Ricardo Fradinho ricardo.fradinho at webdetails.pt
Mon Jul 21 09:50:59 EDT 2014


Hi,

I have a performance problem caused by the queries that Mondrian generates.
These queries always force full table scans because there's no indexing 
that can be done do help these queries.

The problem happens when I select my time range as (2014-06-01 : 
2014-07-08) and Mondrian rewrites this as (2014-06) + (2014-07-01 : 
2014-07-08) which is translated into this nasty SQL:

select
   `dim_site`.`SITE` as `c0`,
   count(distinct `fact_tetrapak_500k`.`dim_function_key`) as `m0`
from
   `dim_site` as `dim_site`,
   `fact_tetrapak_500k` as `fact_tetrapak_500k`,
   `dim_date` as `dim_date`,
   `dim_rul_band` as `dim_rul_band`
where `fact_tetrapak_500k`.`dim_site_key` = `dim_site`.`dim_site_key`
  and `fact_tetrapak_500k`.`dateToProcess` = `dim_date`.`DATE_SK`
  and `fact_tetrapak_500k`.`dim_rul_band_key` = 
`dim_rul_band`.`DIM_RUL_BAND_KEY`
  and
  (
    (
       `dim_date`.`MONTH_NAME` = 'June'
       and `dim_date`.`YEAR_NUMBER` = 2014
       and `dim_rul_band`.`band` = 'RUL 39-30'
    )
    or
    (
       (
          (
             `dim_date`.`DAY_DATE`, `dim_rul_band`.`band`
          )
          in
          (
             (DATE '2014-07-01', 'RUL 39-30'),
             (DATE '2014-07-02', 'RUL 39-30'),
             (DATE '2014-07-03', 'RUL 39-30'),
             (DATE '2014-07-04', 'RUL 39-30'),
             (DATE '2014-07-05', 'RUL 39-30'),
             (DATE '2014-07-06', 'RUL 39-30'),
             (DATE '2014-07-07', 'RUL 39-30'),
             (DATE '2014-07-08', 'RUL 39-30')
          )
       )
    )
)
group by `dim_site`.`SITE`

There is no indexing (on MySQL) that can prevent a full table scan.
I implemented a workaround by changing my [Date] dimension to have only 
the Day level, which is fine for the timerange selection we use. But 
this voids the use of aggregation tables.

I tried mondrian­.­rolap­.­aggregates­.­optimizePredicates=false 
(Boolean property that determines whether Mondrian optimizes predicates­)
but as the docs say, "[...] If false­,­ Mondrian still optimizes queries 
that involve all members of a dimension­".

Also tried setting mondrian­.­rolap­.­EnableInMemoryRollup=false but 
didn't help either.

Do you know if there's any setting that can prevent this optimization ?

Thanks,
Ricardo Fradinho.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20140721/91d0672a/attachment.html 


More information about the Mondrian mailing list