[Mondrian] [MONDRIAN-797] expand members of a level based on partition column

Sergey Mazin sergey.mazin at skype.net
Sun Nov 10 17:22:46 EST 2013


Similar issue has been discussed here:
http://jira.pentaho.com/browse/MONDRIAN-797  - Force SQL to use members of a level

Most of the relational databases (Teradata, Greenplum, Postgre, MS SQL Server, etc) which have partition mechanism implemented are not able to prune/skip partitions when filter is applied not on partition column in fact table directly or column in Time dimension which is used in join. If filter is applied on Month and table is partitioned using column with daily granularity then full table is scanned.

When working with big datasets this leads to significant performance degradation. I agree, that partition pruning/elimination should be fixed on DB layer, but so far I haven't seen any databases, which are able to do this kind of stuff.

Can one of this solution be an option?

1)      Add partitionColumn attribute to <Table> part in schema definition and in SQL filter part expand members of Dimensions using this column as foreignKey to the lowest level.
                                <Table name="sales_fact_1997" schema="public" partitionColumn=" time_id">

2)      Add some kind of a flag for dimension level that it is used to prune partitions

3)      Add "level.includemembers = [Calendar].[Calendar Day]" as proposed in the ticket.

Best regards,
Sergey Mazin

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20131110/7bd8d912/attachment.html 

More information about the Mondrian mailing list