Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: [Mondrian] slicer/filter..

  1. #1
    Ati Rosselet Guest

    Default [Mondrian] slicer/filter..

    Hi, I just ran into the problem MONDRIAN-791, and applied the patch..
    seems to work fine thankfully, but this was just a side problem.
    The main problem is with the values returned when the last filter
    element (when more than one member) have more than 1 child. E.g.
    When I select a filter such as:

    where Crossjoin({[Measures].[amount_inv_EUR]}, {[ProfitCenter].[BU2],
    [ProfitCenter].[BU3]})

    Where BU2 has 2 children and BU3 has 12, then the resulting cells are 12x value
    Reversing the order {[ProfitCenter].[BU3], [ProfitCenter].[BU2]}
    gives 2x values
    Testing with another (BU4) with 3 Children gives me values 3x expected.
    If I select just the children , even if many, the results are correct
    (I'm assuming since each is 1 element and not a group of elements).

    This seems to indicate to me something wrong with either the creation
    of the set in the filter? or application of the filter ....

    Any ideas, or even on where this might be happening?

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

  2. #2
    Ati Rosselet Guest

    Default [Mondrian] Re: slicer/filter..

    Using cmdRunner it turns out that the problem is somewhere else
    completely. If I remove the role I had assigned to the user
    (basically grant all. explicitly), and just use an 'allaccess' role,
    then the problem goes away, so it's got something to do with the role
    based permissions again... although how that could result in MULTIPLES
    being returned rather than less than expected... no idea yet... will
    write again if I figure something out...

    Cheers
    Ati

    On Mon, Apr 11, 2011 at 4:24 PM, Ati Rosselet <ati.rosselet (AT) gmail (DOT) com> wrote:
    > Hi, I just ran into the problem MONDRIAN-791, and applied the patch..
    > seems to work fine thankfully, but this was just a side problem.
    > The main problem is with the values returned when the last filter
    > element (when more than one member) have more than 1 child. E.g.
    > When I select a filter such as:
    >
    > where Crossjoin({[Measures].[amount_inv_EUR]}, {[ProfitCenter].[BU2],
    > [ProfitCenter].[BU3]})
    >
    > Where BU2 has 2 children and BU3 has 12, then the resulting cells are 12x value
    > Reversing the order {[ProfitCenter].[BU3], [ProfitCenter].[BU2]}
    > gives 2x values
    > Testing with another (BU4) with 3 Children gives me values 3x expected.
    > If I select just the children , even if many, the results are correct
    > (I'm assuming since each is 1 element and not a group of elements).
    >
    > This seems to indicate to me something wrong with either the creation
    > of the set in the filter? or application of the filter ....
    >
    > Any ideas, or even on where this might be happening?
    >
    > Cheers.
    >

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

  3. #3
    Ati Rosselet Guest

    Default [Mondrian] Re: slicer/filter..

    Narrowing it down. The problem is definitely with the role... am I
    using it wrong?
    This does not seem to be working logically I want no access to any BU
    without a specific grant to a child member, and
    then that [BUX] should be automatically set access="custom" with only
    the specified Children visible.

    <CubeGrant cube="FI" access="all">
    <HierarchyGrant hierarchy="[ProfitCenter]"
    access="custom" rollupPolicy="partial">
    <MemberGrant member="[ProfitCenter].[All
    ProfitCenters]" access="none"/>
    <MemberGrant
    member="[ProfitCenter].[BU2].[H2001]" access="all"/>
    <MemberGrant
    member="[ProfitCenter].[BU1].[H1004]" access="all"/>
    </HierarchyGrant>
    </CubeGrant>

    This, however works - setting the [BU] levels to all and then
    specifically revoking access to undesired children.

    <CubeGrant cube="FI" access="all">
    <HierarchyGrant hierarchy="[ProfitCenter]"
    access="custom" rollupPolicy="partial">
    <MemberGrant member="[ProfitCenter].[All
    ProfitCenters]" access="none"/>
    <MemberGrant member="[ProfitCenter].[BU2]"
    access="all"/>
    <MemberGrant
    member="[ProfitCenter].[BU2].[H2002]" access="none"/>
    <MemberGrant member="[ProfitCenter].[BU1]"
    access="all"/>
    <MemberGrant
    member="[ProfitCenter].[BU1].[H1001]" access="none"/>
    <MemberGrant
    member="[ProfitCenter].[BU1].[H1002]" access="none"/>
    <MemberGrant
    member="[ProfitCenter].[BU1].[H1003]" access="none"/>
    </HierarchyGrant>
    </CubeGrant>

    What is the difference between these 2 examples - they should
    logically have the same results right? I have many left nodes, so
    specifying each
    "no access" will give me a really large role I think there is a
    bug somewhere since the first role seems to work just fine, except if
    {[BU1],[BU2]} are on the slicer with an MDX such as:

    select {[Measures].[amount]} ON COLUMNS,
    {[Account].[Primary cost].[01_Base salary].[70000]} ON ROWS
    from [FI]
    where {[ProfitCenter].[BU1], [ProfitCenter].[BU2]}

    the resulting cell is the correct result X
    count([BU2].VisibleChildren) (yes.. it counts the visible children of
    the last element if it is custom access - I'm guessing the
    LimitedRolupMember's expression is somehow involved??)

    I'd be happy to try to duplicate the problem with the
    cmdRunner/Foodmart, but I can't see how I can specify a role there?
    Is there a way to do this??
    Cheers/Gnite

    On Mon, Apr 11, 2011 at 8:39 PM, Ati Rosselet <ati.rosselet (AT) gmail (DOT) com> wrote:[color=blue]
    > Using cmdRunner it turns out that the problem is somewhere else
    > completely.

  4. #4
    Ati Rosselet Guest

    Default [Mondrian] Re: slicer/filter..

    test case with foodmart (ok.. I figured out how to specify the role - RFTM
    right? )

    Role added to Foodmart.xml:

    <Role name="test">
    <SchemaGrant access="none">
    <CubeGrant cube="Sales" access="all">
    <HierarchyGrant hierarchy="[Store]" access="custom"
    topLevel="[Store].[Store Country]" rollupPolicy="partial">
    <MemberGrant member="[Store].[All Stores]" access="none"/>
    <MemberGrant member="[Store].[USA].[CA].[Los Angeles]" access="all"/>
    <MemberGrant member="[Store].[USA].[CA].[Alameda]" access="all"/>
    <MemberGrant member="[Store].[USA].[CA].[Beverly Hills]"
    access="all"/>
    <MemberGrant member="[Store].[USA].[CA].[San Francisco]"
    access="all"/>
    <MemberGrant member="[Store].[USA].[CA].[San Diego]" access="all"/>

    <MemberGrant member="[Store].[USA].[OR].[Portland]" access="all"/>
    <MemberGrant member="[Store].[USA].[OR].[Salem]" access="all"/>
    </HierarchyGrant>
    </CubeGrant>
    </SchemaGrant>
    </Role>

    execute the following with cmdRunner:

    > select {[Measures].[Unit Sales]} on columns,

    Crossjoin({[Product].[Food].[Baked
    Goods].[Bread]},{[Store].[USA].[OR],[Store].[USA].[CA]}) on rows
    from [Sales] ;
    Axis #0:
    {}
    Axis #1:
    {[Measures].[Unit Sales]}
    Axis #2:
    {[Product].[Food].[Baked Goods].[Bread], [Store].[USA].[OR]}
    {[Product].[Food].[Baked Goods].[Bread], [Store].[USA].[CA]}
    Row #0: 2,013
    Row #1: 2,150

    Then try:
    > select {[Measures].[Unit Sales]} on columns,

    {[Product].[Food].[Baked Goods].[Bread]} on rows
    from [Sales]
    where {[Store].[USA].[CA],[Store].[USA].[OR]} ;
    Axis #0:
    {[Store].[USA].[CA]}
    {[Store].[USA].[OR]}
    Axis #1:
    {[Measures].[Unit Sales]}
    Axis #2:
    {[Product].[Food].[Baked Goods].[Bread]}
    Row #0: 15,740


    now reverse the OR/CA and I get:

    > select {[Measures].[Unit Sales]} on columns,

    {[Product].[Food].[Baked Goods].[Bread]} on rows
    from [Sales]
    where {[Store].[USA].[OR],[Store].[USA].[CA]} ;
    Axis #0:
    {[Store].[USA].[OR]}
    {[Store].[USA].[CA]}
    Axis #1:
    {[Measures].[Unit Sales]}
    Axis #2:
    {[Product].[Food].[Baked Goods].[Bread]}
    Row #0: 39,350

    4163!=15740!=39350....
    Can someone please test this and see if they get the same results?


    Also :
    > select {[Measures].[Unit Sales]} on columns,

    {[Product].[Food].[Baked Goods].[Bread]} on rows
    from [Sales]
    where {[Store].[USA].[OR]}? ? ? ;
    Axis #0:
    {[Store].[USA].[OR]}
    Axis #1:
    {[Measures].[Unit Sales]}
    Axis #2:
    {[Product].[Food].[Baked Goods].[Bread]}
    Row #0: 2,013

    and the role seems to work since only these data are returned at USA
    level...
    > select {[Measures].[Unit Sales]} on columns,

    Crossjoin({[Product].[Food].[Baked
    Goods].[Bread]},{[Store].[USA]}) on rows
    from [Sales] ;
    Axis #0:
    {}
    Axis #1:
    {[Measures].[Unit Sales]}
    Axis #2:
    {[Product].[Food].[Baked Goods].[Bread], [Store].[USA]}
    Row #0: 4,163

    Thanks

    On Mon, Apr 11, 2011 at 11:11 PM, Ati Rosselet <ati.rosselet (AT) gmail (DOT) com>
    wrote:
    > Narrowing it down. The problem is definitely with the role... am I
    > using it wrong?
    > This does not seem to be working logically I want no access to any BU
    > without a specific grant to a child member, and
    > then that [BUX] should be automatically set access="custom" with only
    > the specified Children visible.
    >
    > <CubeGrant cube="FI" access="all">
    > <HierarchyGrant hierarchy="[ProfitCenter]"
    > access="custom" rollupPolicy="partial">
    > <MemberGrant member="[ProfitCenter].[All
    > ProfitCenters]" access="none"/>
    > <MemberGrant
    > member="[ProfitCenter].[BU2].[H2001]" access="all"/>
    > <MemberGrant
    > member="[ProfitCenter].[BU1].[H1004]" access="all"/>
    > </HierarchyGrant>
    > </CubeGrant>
    >
    > This, however works - setting the [BU] levels to all and then
    > specifically revoking access to undesired children.
    >
    > <CubeGrant cube="FI" access="all">
    > <HierarchyGrant hierarchy="[ProfitCenter]"
    > access="custom" rollupPolicy="partial">
    > <MemberGrant member="[ProfitCenter].[All
    > ProfitCenters]" access="none"/>
    > <MemberGrant member="[ProfitCenter].[BU2]"
    > access="all"/>
    > <MemberGrant
    > member="[ProfitCenter].[BU2].[H2002]" access="none"/>
    > <MemberGrant member="[ProfitCenter].[BU1]"
    > access="all"/>
    > <MemberGrant
    > member="[ProfitCenter].[BU1].[H1001]" access="none"/>
    > <MemberGrant
    > member="[ProfitCenter].[BU1].[H1002]" access="none"/>
    > <MemberGrant
    > member="[ProfitCenter].[BU1].[H1003]" access="none"/>
    > </HierarchyGrant>
    > </CubeGrant>
    >
    > What is the difference between these 2 examples - they should
    > logically have the same results right? I have many left nodes, so
    > specifying each
    > "no access" will give me a really large role I think there is a
    > bug somewhere since the first role seems to work just fine, except if
    > {[BU1],[BU2]} are on the slicer with an MDX such as:
    >
    > select {[Measures].[amount]} ON COLUMNS,
    > {[Account].[Primary cost].[01_Base salary].[70000]} ON ROWS
    > from [FI]
    > where {[ProfitCenter].[BU1], [ProfitCenter].[BU2]}
    >
    > the resulting cell is the correct result X
    > count([BU2].VisibleChildren) (yes.. it counts the visible children of
    > the last element if it is custom access - I'm guessing the
    > LimitedRolupMember's expression is somehow involved??)
    >
    > I'd be happy to try to duplicate the problem with the
    > cmdRunner/Foodmart, but I can't see how I can specify a role there?
    > Is there a way to do this??
    > Cheers/Gnite
    >
    > On Mon, Apr 11, 2011 at 8:39 PM, Ati Rosselet <ati.rosselet (AT) gmail (DOT) com>

    wrote:
    >> Using cmdRunner it turns out that the problem is somewhere else
    >> completely. If I remove the role I had assigned to the user
    >> (basically grant all. explicitly), and just use an 'allaccess' role,
    >> then the problem goes away, so it's got something to do with the role
    >> based permissions again... although how that could result in MULTIPLES
    >> being returned rather than less than expected... no idea yet... will
    >> write again if I figure something out...
    >>
    >> Cheers
    >> Ati
    >>
    >> On Mon, Apr 11, 2011 at 4:24 PM, Ati Rosselet <ati.rosselet (AT) gmail (DOT) com>

    wrote:
    >>> Hi, I just ran into the problem MONDRIAN-791, and applied the patch..
    >>> seems to work fine thankfully, but this was just a side problem.
    >>> The main problem is with the values returned when the last filter
    >>> element (when more than one member) have more than 1 child. E.g.
    >>> When I select a filter such as:
    >>>
    >>> where Crossjoin({[Measures].[amount_inv_EUR]}, {[ProfitCenter].[BU2],
    >>> [ProfitCenter].[BU3]})
    >>>
    >>> Where BU2 has 2 children and BU3 has 12, then the resulting cells are

    12x value
    >>> Reversing the order {[ProfitCenter].[BU3], [ProfitCenter].[BU2]}
    >>> gives 2x values
    >>> Testing with another (BU4) with 3 Children gives me values 3x expected.
    >>> If I select just the children , even if many, the results are correct
    >>> (I'm assuming since each is 1 element and not a group of elements).
    >>>
    >>> This seems to indicate to me something wrong with either the creation
    >>> of the set in the filter? or application of the filter ....
    >>>
    >>> Any ideas, or even on where this might be happening?
    >>>
    >>> Cheers.
    >>>

    >>

    >


    _______________________________________________
    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 - 2019 Hitachi Vantara Corporation. All Rights Reserved.