[Mondrian] Help with sort of large dimension and calculated members

Joshua Diamond diamond.joshh at gmail.com
Tue May 5 17:11:57 EDT 2015


Hi Roland,

Thanks for your response. I can't send you the data but I attached the
create table statements. I've indexed all of my primary keys and foreign
keys (I think). I indexed most of the columns being used as level members
in the schema. None of the tables are partitioned but I also don't think
that my database is large enough to really warrant it (I could be wrong).
Here are the table row sizes:
er_fact573971er_dim_candidates49739er_dim_geo12128er_dim_party64
er_dim_office36

Any thoughts? Anything else I can send you that isn't the actual data let
me know.

Thank you so much,

Josh


Date: Tue, 5 May 2015 22:31:02 +0200
From: Roland Bouman <roland.bouman at gmail.com>
Subject: Re: [Mondrian] Help with sort of large dimension and
        calculated      members
To: Mondrian developer mailing list <mondrian at pentaho.org>
Message-ID:
        <CAO44d8Fr7fds-cx4WuaRtfFakm15dCN9Q5mATb77WTDbk91big at mail.gmail.com>
Content-Type: text/plain; charset="utf-8"

Just curious...can you share your MySQL schema + data? I'm intrigued by
your query and would like to run it myself. Perhaps some optimization on
the MySQL end of things is possible as well...

On Tue, May 5, 2015 at 10:26 PM, Joshua Diamond <diamond.joshh at gmail.com>
wrote:

>
> Hi,
>
> Thanks for letting me join this list. I joined because I'm having a
> problem that no amount of research has been able to solve.
> Here's my specs:
> Analyzer: Saiku 2.6 CE
> mondrian-3.8.0.0-209.jar
> database: MySql
> Attached is my schema.
> Attached are my mondrian properties.
>
> my fact table is roughly 500,000 rows.
> my largest dimension table is my CandidatesDim dimension is 50,000 rows.
>
> When ever I try to run a query like the following, mondrian generates
> hundreds of sql querries and even if I set my query timeout property to
> something ridiculous, it never finishes.
> SELECT
> NON EMPTY Hierarchize(Union(CrossJoin({[OfficeDim.OfficeHier].[U.S.
> President]}, CrossJoin([PartyDim.PartyHier].[Party].Members,
> {[Measures].[VotesDisplay]})), CrossJoin({[OfficeDim.OfficeHier].[U.S.
> President]}, CrossJoin([PartyDim.PartyHier].[Party].Members,
> {[Measures].[Percentage]})))) ON COLUMNS,
> NON EMPTY CrossJoin([GeoDim.GeoHier].[State].Members,
> [CandidatesDim.CandidatesHier].[Candidate].Members) ON ROWS
> FROM [ElectionResults]
> WHERE {[CycleDim.CycleHier].[General].[2012 General]}
>
> The problem seems to happen whenever I have more than one dimension on an
> axis and more than one measure.
>
> If I run this query it finishes in 3 seconds:
> SELECT
> NON EMPTY CrossJoin({[OfficeDim.OfficeHier].[U.S. President]},
> CrossJoin([PartyDim.PartyHier].[Party].Members,
{[Measures].[Percentage]}))
> ON COLUMNS,
> NON EMPTY CrossJoin([GeoDim.GeoHier].[State].Members,
> [CandidatesDim.CandidatesHier].[Candidate].Members) ON ROWS
> FROM [ElectionResults]
> WHERE {[CycleDim.CycleHier].[General].[2012 General]}
>
> I've tried almost every combination of mondrian properties. I've tried
> setting saiku.olap.convert.query = true. I've created aggregate tables
that
> never got used.
>
> What am I doing wrong? Is this just a mondrian 3.x issue that I'm forced
> to live with? I'm open to any thoughts you all might have.
>
> Thank you so much for your help.
>
> Josh
>
> --
> Joshua Diamond
> e. Diamond.JoshH at gmail.com
> c.: (561)676-3275
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>


--
Roland Bouman
blog: http://rpbouman.blogspot.com/
twitter: @rolandbouman
linkedin: http://www.linkedin.com/profile/view?id=5142800&trk=tab_pro

Author of "Pentaho Solutions" (Wiley, ISBN: 978-0-470-48432-6
http://tinyurl.com/lvxa88) and "Pentaho Kettle Solutions" (Wiley, ISBN:
978-0-470-63517-9 http://tinyurl.com/33r7a8m)
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.pentaho.org/pipermail/mondrian/attachments/20150505/d92212cc/attachment-0001.html

-- 
Joshua Diamond
e. Diamond.JoshH at gmail.com
c.: (561)676-3275
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20150505/ae0b4ca7/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: create_table_statements.sql
Type: application/octet-stream
Size: 5152 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20150505/ae0b4ca7/attachment-0001.obj 


More information about the Mondrian mailing list