Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: [Mondrian] Mondrian Performance Issue with Large Dimensions

  1. #1
    Tom Barber Guest

    Default [Mondrian] Mondrian Performance Issue with Large Dimensions

    Hi guys,

    We're using Pentaho BI Server 3.6 and testing Mondrian against some largish
    dimensions (200m records for users for example) in Vectorwise

    One of the biggest issues we've seen is that Mondrian seems to be querying
    dimensions that are unused and we're unsure why, my client has come up with
    a example that you might be able to shed some light on.

    I've extrapolated his email and attached it to this as its easier than me
    rewriting the issue. If anyone has a clue please let me know

    Cheers

    Tom

    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  2. #2
    Tom Barber Guest

    Default Re: [Mondrian] Mondrian Performance Issue with Large Dimensions

    I've been informed that this only seems to occur when we create shared
    dimension and include it in a cube.

    On 17 October 2011 16:38, Tom Barber <tom (AT) analytical-labs (DOT) com> wrote:

    > Hi guys,
    >
    > We're using Pentaho BI Server 3.6 and testing Mondrian against some largish
    > dimensions (200m records for users for example) in Vectorwise
    >
    > One of the biggest issues we've seen is that Mondrian seems to be querying
    > dimensions that are unused and we're unsure why, my client has come up with
    > a example that you might be able to shed some light on.
    >
    > I've extrapolated his email and attached it to this as its easier than me
    > rewriting the issue. If anyone has a clue please let me know
    >
    > Cheers
    >
    > Tom
    >


    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  3. #3
    Julian Hyde Guest

    Default Re: [Mondrian] Mondrian Performance Issue with Large Dimensions

    Pedro asked me a similar question. See my reply to him (below).

    It turns out that option #1 is already implemented. Try setting mondrian.olap.elements.NeedDimensionPrefix=true and see whether mondrian generates fewer SQL queries. (You'll have to write more explicit MDX of course.)

    Julian

    -------

    On Saturday, September 24, 2011, Julian Hyde wrote:

    > Ideas to improve "large dimension" performance:
    >
    > 1. Add option that forces strict name resolution. You would not be able to write [M], you would have to write [Gender].[M]. Therefore if you wrote [Customers] mondrian would not look for a root member called "Customers" in every dimension.
    >
    > 2. Specify root members in the schema. E.g.
    >
    > <Attribute name='Gender' ... >
    > <Members>
    > <Member key='F'/>
    > <Member key='M'/>
    > </Members>
    > </Attribute>
    >
    > 3. Use aggregate tables to find level members. This proposal is flawed, because only 'non empty' members would come back. For example, if you asked for [Time].[Year].members, 2012 would not be returned from the agg table, because we have not yet made a sale in 2012. We would need a way to specify that this approximation is safe for a given level.

    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  4. #4
    Tom Barber Guest

    Default Re: [Mondrian] Mondrian Performance Issue with Large Dimensions

    Hi Julian,

    I got them to check the mondrian property in
    system/mondrian/mondrian.properties, and I'm told its already set to true. I
    should also point out this when using Analyzer.

    Cheers

    Tom

    On 18 October 2011 08:19, Julian Hyde <jhyde (AT) pentaho (DOT) com> wrote:

    > Pedro asked me a similar question. See my reply to him (below).
    >
    > It turns out that option #1 is already implemented. Try setting
    > mondrian.olap.elements.NeedDimensionPrefix=true and see whether mondrian
    > generates fewer SQL queries. (You'll have to write more explicit MDX of
    > course.)
    >
    > Julian
    >
    > -------
    >
    > On Saturday, September 24, 2011, Julian Hyde wrote:
    >
    > > Ideas to improve "large dimension" performance:
    > >
    > > 1. Add option that forces strict name resolution. You would not be able

    > to write [M], you would have to write [Gender].[M]. Therefore if you wrote
    > [Customers] mondrian would not look for a root member called "Customers" in
    > every dimension.
    > >
    > > 2. Specify root members in the schema. E.g.
    > >
    > > <Attribute name='Gender' ... >
    > > <Members>
    > > <Member key='F'/>
    > > <Member key='M'/>
    > > </Members>
    > > </Attribute>
    > >
    > > 3. Use aggregate tables to find level members. This proposal is flawed,

    > because only 'non empty' members would come back. For example, if you asked
    > for [Time].[Year].members, 2012 would not be returned from the agg table,
    > because we have not yet made a sale in 2012. We would need a way to specify
    > that this approximation is safe for a given level.
    > _______________________________________________
    > Mondrian mailing list
    > Mondrian (AT) pentaho (DOT) org
    > http://lists.pentaho.org/mailman/listinfo/mondrian
    >


    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  5. #5
    Joe Barnett Guest

    Default Re: [Mondrian] Mondrian Performance Issue with Large Dimensions

    Possible that this is http://jira.pentaho.com/browse/MONDRIAN-811 ?
    Sounds like it's related to the order of dimension definition, which
    would correspond to what we saw there (and work around by putting the
    large dimensions toward the end of our schema definition).

    -Joe

    On Tue, Oct 18, 2011 at 12:47 AM, Tom Barber <tom (AT) analytical-labs (DOT) com> wrote:[color=blue]
    > Hi Julian,
    >
    > I got them to check the mondrian property in
    > system/mondrian/mondrian.properties, and I'm told its already set to true.. I
    > should also point out this when using Analyzer.
    >
    > Cheers
    >
    > Tom
    >
    > On 18 October 2011 08:19, Julian Hyde <jhyde (AT) pentaho (DOT) com> wrote:[color=green]
    >>
    >> Pedro asked me a similar question. See my reply to him (below).
    >>
    >> It turns out that option #1 is already implemented. Try setting
    >> mondrian.olap.elements.NeedDimensionPrefix=true and see whether mondrian
    >> generates fewer SQL queries. (You'll have to write more explicit MDX of
    >> course.)
    >>
    >> Julian
    >>
    >> -------
    >>
    >> On Saturday, September 24, 2011, Julian Hyde wrote:
    >>[color=darkred]
    >> > Ideas to improve "large dimension" performance:
    >> >
    >> > 1. Add option that forces strict name resolution. You would not be able
    >> > to write [M], you would have to write [Gender].[M]. Therefore if you wrote
    >> > [Customers] mondrian would not look for a root member called "Customers" in
    >> > every dimension.
    >> >
    >> > 2. Specify root members in the schema. E.g.
    >> >
    >> > <Attribute name='Gender' ... >
    >> >

  6. #6
    Julian Hyde Guest

    Default Re: [Mondrian] Mondrian Performance Issue with Large Dimensions

    On Oct 18, 2011, at 7:47 AM, Joe Barnett wrote:

    > Possible that this is http://jira.pentaho.com/browse/MONDRIAN-811 ?
    > Sounds like it's related to the order of dimension definition, which
    > would correspond to what we saw there (and work around by putting the
    > large dimensions toward the end of our schema definition).


    Very likely that it's related. But I believe that that issue is fixed, provided that you use SsasCompatibleNaming and NeedDimensionPrefix. (Please re-open if it's not!)

    See http://jira.pentaho.com/browse/MONDRIAN-811 for details.

    Julian

    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.