US and Worldwide: +1 (866) 660-7555
Results 1 to 8 of 8

Thread: How to apply forced filter on facts table depending on user/role?

  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Default How to apply forced filter on facts table depending on user/role?

    Hi,

    I have case like this: I have defined cube which has one dimension defined as person responsible for income/outcome.

    I would like to give access to this cube to every person (who can generate income/outcome) to analyze facts but only those assigned to that person.

    Standard role privileges does not allow to do that (when such user use time dimension will see totals for whole company instead of only his part). So - what I look for i something like this:

    <CubeGrant cude="inout">
    <Filter>[Responsible Person][All Persons][John Doe]</Filter>
    ...
    </CubeGrant>

    and that filter applied to EVERY data query without possibility to override with jpivot interface...

    Is there any way I can do that, or do I have to define it as separate cubes pointing to simple sql view with applied filter as facts table?

  2. #2
    Join Date
    Sep 2011
    Posts
    4

    Default

    Any suggestions? How is case like this usually solved in companies? Any hint is welcome...

    to make my case more clear: we have few (4-5) project managers. We want them to have olap access but data shoud be limited to only their projekts. dimensions should be same for everyone (klients, developers, time, location, etc) as the resources can be shared or assigned for projects. There is also 4 people of board which should have access without limitations. Do I have to prepare 5-6 cubes (one for each project manager and one for board) or is there any way to apply such filter based on role/login?

  3. #3
    Join Date
    Sep 2011
    Posts
    4

    Default ??

    Is there anyone who could even give a hint how to solve this case?

  4. #4
    Join Date
    Feb 2009
    Posts
    9

    Default

    Did you try to use MemberGrants?
    http://mondrian.pentaho.com/document...Access_control

    I can't really tell from you post.
    Markus Burger-Scheidlin
    ITGAIN Consulting GmbH Hannover, Germany

  5. #5
    Join Date
    Aug 2011
    Posts
    1

    Default

    This is a Tough problem, you can do it with successive grants. My solution doesnt scale past 100 grants
    (I need 200k grants)

    <Role name="11111">
    <SchemaGrant access="none">
    <CubeGrant cube="My Analyzer" access="all">
    <DimensionGrant dimension="Alt Level 1" access="all">
    </DimensionGrant>
    <DimensionGrant dimension="Level 1" access="none">
    </DimensionGrant>
    <HierarchyGrant hierarchy="Alt Level 1" topLevel="Key1" bottomLevel="Level 4" access="custom">
    <MemberGrant member="[Alt Level 1].[Key1].[11111]" access="all">
    </MemberGrant>
    </HierarchyGrant>
    </CubeGrant>
    </SchemaGrant>
    </Role>
    <Role name="22222">
    <SchemaGrant access="none">
    <CubeGrant cube="My Analyzer" access="all">
    <DimensionGrant dimension="Level 1" access="all">
    </DimensionGrant>
    <DimensionGrant dimension="Alt Level 1" access="none">
    </DimensionGrant>
    <HierarchyGrant hierarchy="Level 1" topLevel="Key1" bottomLevel="Level 4" access="custom">
    <MemberGrant member="[Level 1].[Key1].[22222]" access="all">
    </MemberGrant>
    </HierarchyGrant>
    </CubeGrant>
    </SchemaGrant>
    </Role>

  6. #6
    Join Date
    Sep 2011
    Posts
    4

    Default

    MemberGrants does not do exactly what I need... they just let me decide which dimension values user CAN use in filters (but is not forced to use them) - at least this is how I understand this. My problem is slightly different, mayby this interpretation will bring more light on my case:
    as for now I solved my problem by creating 5 different cubes and giving proper access to them to proper users. Cubes are all the same except they have different fact source defined. One (for board members) points to table with raw data populated by ETL, others are defined for project managers and they point to simple view as facts source: SELECT * from fact_table WHERE fact_table.id_project_manager = X;

    This way - every project manager can see only facts that are assigned to him.

  7. #7

    Default

    Quote Originally Posted by redguy View Post
    MemberGrants does not do exactly what I need... they just let me decide which dimension values user CAN use in filters (but is not forced to use them) - at least this is how I understand this. My problem is slightly different, mayby this interpretation will bring more light on my case:
    as for now I solved my problem by creating 5 different cubes and giving proper access to them to proper users. Cubes are all the same except they have different fact source defined. One (for board members) points to table with raw data populated by ETL, others are defined for project managers and they point to simple view as facts source: SELECT * from fact_table WHERE fact_table.id_project_manager = X;

    This way - every project manager can see only facts that are assigned to him.
    could you solve it in another way? I have the same problem

  8. #8
    Join Date
    Aug 2013
    Posts
    129

    Default

    hello every one

    i do have the same problem but my hierarchy is different i had a company with different plant under which different process i had to create cube to show them overall performance, with limiting the info limited to their working area like data protection from other plants and process wings.

    if i had to create a different cube for each one i had to create like 10-15 cubes so plz some help to get out of this

    Thanks for ur time
    pentaho enthusiast
    Last edited by suresh84c; 10-03-2013 at 02:59 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •