[Mondrian] Re: slicer/filter..

[Ati Rosselet]

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.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.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.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.
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20110411/d7765200/attachment.html