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

Roland Bouman roland.bouman at gmail.com
Tue May 5 16:31:02 EDT 2015


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.html 


More information about the Mondrian mailing list