Planning Analytics

 View Only
Expand all | Collapse all

Show Employee their data only with employee dimension

  • 1.  Show Employee their data only with employee dimension

    Posted Mon January 11, 2021 05:21 PM
    I've had a look at the forum, but I think I might not know exactly what to search for in this case.  I have a cube that contains employee metrics, and has an employee dim for managers to review data for any employees that rollup to their team/cost center.  We've created user groups for these cost centers, and that makes it easy to restrict access to only your cost centers.

    We're now being asked if we can restrict each employee to only their data (data associated to their employee name in the employee dimension).  Without creating a 1-1 security group per employee, is there anything we can do to accomplish this?

    The PA models are also secured via CA user groups as this is a cloud offering, and our users will most likely access this data through a CA dashboard.

    Any thoughts are appreciated.

    ------------------------------
    Mark Ryan
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: Show Employee their data only with employee dimension

    Posted Tue January 12, 2021 09:37 AM
    Hi Mark, 

    This might not be the answer you are expecting but I'm afraid you'll need to create one security group per user. Security in TM1 is managed by group, thus for applications where you need to give each user a different access type you'll need to create one group per user.

    Actually since all applications tend to be more and more complex, this is beconming kind of usual. 

    These groups can be created and affected programmatically in TM1 quite easily instead of manually in CA. You just need one simple process to do it, then run it each time you create a new user.

    Best regards, 
    Laurent

    ------------------------------
    Laurent Henssien
    C L A R I T Y
    ------------------------------



  • 3.  RE: Show Employee their data only with employee dimension

    Posted Tue January 12, 2021 10:36 AM
    Thanks for the reply.  That was my first design assumption, but was hoping someone had done something I had not thought of or seen before.  I thought maybe an xref table/cube/dim that CA dashboard could reference to show/hide data.  That could work to only show a user their data in the dashboard/report, but wouldn't secure the data.

    Do you have a link to an example or a sample code that creates a CA security group from a TI Process?  I don't believe I've seen that done.  Our security groups are entirely maintained on the CA server and then added to the PA models.

    Thanks again,

    Mark

    ------------------------------
    Mark Ryan
    ------------------------------



  • 4.  RE: Show Employee their data only with employee dimension

    Posted Tue January 12, 2021 10:58 AM

    Acutally you don't need to create groups in CA since your data is hosted inside TM1. I'm speaking about creating native TM1 groups, which will prevent users from seeing other users data.

    You can build a simple process that does the following : 
    - loop thru all items of }Clients dimension (using a WHILE .. END or a subset of the }Clients dimension as a source of the process)
    - for each one do an addGroup of the user name (call it something like User_<userName> to distinguish from the "regular" groups)
    - do an assignClientToGroup of the user to this new group

    Then you'll have to manage security along the employee dimension in your data cube, which can be done easily with an attribute linking the client and the employee in the same process, or a secondary process, or in a rule.

    We have this logic in place for several applications, up to 2000 users (going on 4000).
    Using one group per user can seem counter-intuitive but on complex applications is actually far less complicated than using module / entity / domain / whatever groups which can't be finely tuned (or you might end up with hundreds of groups anyway).

    Cheers, 
    Laurent 





    ------------------------------
    Laurent Henssien
    C L A R I T Y
    ------------------------------



  • 5.  RE: Show Employee their data only with employee dimension

    IBM Champion
    Posted Tue January 12, 2021 05:35 PM
    Edited by System Fri January 20, 2023 04:37 PM
    Hi Mark & Laurent:

    One option you may consider is to put a rule on the Cell Security cube of the Employee Metrics cube that references the TM1USER() function. The rule would look something like this:

    [ 'User_Group' ] = S:
    IF ( DIMIX( 'Employees', !Employees ) = DIMIX( 'Employees', ATTRS( '}Clients', TM1USER(), 'Employee' ) )
    ,'READ'
    ,'NONE'
    );

    Where User_Group is the group that your users are a member of. It assumes your Employee dimension members are NOT the same as people's log-on IDs (in the }Clients dimension), and that you have set up an attribute to take care of the mapping.

    This will not restrict access to the Employees dimension, but it will prevent anyone from seeing data that is not associated with their own Employee member. After you save the rule, make sure you refresh security in order for the rule to take effect. 

    Maybe this will enable you to avoid proliferating user groups? It's a little challenging from a reporting point of view, you would probably need to create a dynamic report in Planning Analytics for Excel that uses MDX to limit the rows to the user who is logged in. This can of course also be published to PAW. Or you can just let people wade through all the "blank" (secured) cells until they find their data :-)

    Regards
    AG



    ------------------------------
    Ann-Grete Tan
    Chief Strategy & Marketing Officer
    QueBIT Consulting

    Analyze. Plan. Achieve.
    https://quebit.com
    ------------------------------



  • 6.  RE: Show Employee their data only with employee dimension

    Posted Wed January 13, 2021 04:43 AM
    Edited by System Fri January 20, 2023 04:44 PM

    Hello Ann, 

    That can be done but in this case you could do it directly in the dimension security cube instead.

    I would also avoid using rules on security objects since you'd need to run a SecurityRefresh with each structure change, which depending on the size of the model can be painful.

    In my experience using one group per user allows for a very fine approach of the security, especially on systems with multiple and complex hierarchies on big dimensions. There is no rule of thumb or best practice so, hey, why not ?  

    Cheers, 
    Laurent 



    ------------------------------
    Laurent Henssien
    C L A R I T Y
    ------------------------------



  • 7.  RE: Show Employee their data only with employee dimension

    IBM Champion
    Posted Wed January 13, 2021 09:56 AM

    Laurent:

    I am sorry I was not clear. With the approach I suggested, Security Refresh is only necessary ONCE, when you save the rule on the Cell Security cube. You do not have to do it after that one time.

    I have not recently tried to put rules on the Element Security cube, but I suspect you will potentially run into one or both the following problems:
    1) The trick of using the TM1USER function to detect the person who is logged in and adjust security in depends on TM1USER being reevaluated in real-time. I believe (but you should verify) that rules on the Element Security cube (unlike those on Cell Security) are evaluated just once - when they are saved. TM1USER at this time will evaluate to the Id of the rule writer and stay there. In other words, element security will NOT adjust in real time as we want. 

    2) If my hypothesis in #1 is incorrect you will run into the Security Refresh issue you pointed out, which I agree is not realistic.

    I agree that creating security groups for each user is a viable approach and automating their creation makes it easier. However keep in mind that there can be a performance cost to having too many security groups, so depending on other aspects of your model it can be good to know what options are available to you.

    Regards,
    AG



    ------------------------------
    Ann-Grete Tan
    Chief Strategy & Marketing Officer
    QueBIT Consulting

    Analyze. Plan. Achieve.
    https://quebit.com
    ------------------------------



  • 8.  RE: Show Employee their data only with employee dimension

    Posted Wed January 13, 2021 01:12 PM
    Well, I believe that if you add an employee to the dimension you'll need to do a SecurityRefresh to apply the changes either to the cell or the dimension security. 

    Plus the fact that you don't prevent users from seeing other employees, as you have already stated.

    If you don't use rules and don't need to do a security refresh the number of groups is not an issue, well at least up to several thousand, I've not tried with millions yet :)

    ------------------------------
    Laurent Henssien
    C L A R I T Y
    https://www.clarity.consulting
    ------------------------------



  • 9.  RE: Show Employee their data only with employee dimension

    IBM Champion
    Posted Wed January 13, 2021 03:03 PM
    Laurent:

    I agree that this approach does not stop people from seeing other employee's names or IDs in the raw cube viewer, but it does prevent them from seeing any of their data. Besides, there are all sorts of ways you can design the user interface to address or mitigate the problem.

    By the way I just ran a test where I added a new client, and it was not necessary to do a SecurityRefresh for the CellSecurity approach to work.

    My point here is not that this is always the BEST way to solve the problem, but that as developers it is good to know all the options available depending on the situation.

    The original question was whether there is a way to do this without adding a security group for every single user. The answer is - sort-of :-)

    Regards
    AG

    ------------------------------
    Ann-Grete Tan
    Chief Strategy & Marketing Officer
    QueBIT Consulting

    Analyze. Plan. Achieve.
    https://quebit.com
    ------------------------------



  • 10.  RE: Show Employee their data only with employee dimension

    Posted Wed January 13, 2021 03:03 PM

    Cell security is always dynamic, even after a dimension update, and does not require a SecurityRefresh. Only object level security requires a refresh when using rules.

     

    Cheers!

    Ryan

     

     

     

     






  • 11.  RE: Show Employee their data only with employee dimension

    Posted Thu January 14, 2021 06:08 AM
    All right then, we've had so many issues in the past with these that we try to avoid them as much as possible, so I'm probably a bit rusty :)

    I kinda prefer using Overlay cubes in this situation to evacuate group issues...

    ------------------------------
    Laurent Henssien
    C L A R I T Y
    https://www.clarity.consulting
    ------------------------------



  • 12.  RE: Show Employee their data only with employee dimension

    Posted Tue January 12, 2021 05:31 PM
    Hi

    One option is to create a security Group per Employee. I have a generic cascade cube so that for example a manager can be given WRITE access to a Cost Centre and this will automatically cascade down to give him/her WRITE access to all employees in that Cost Centre.This will make it easy to meet both your existing and new requirements.

    When you say you do authentication via CA, I presume you mean CAM? Having to create a security group per user in Active Directory (AD) to drive CAM can be a bit onerous. It is possible to use CAM to only do the user authentication but to largely ignore the Group assignment side of CAM, eg to just assign all users to a generic CAM derived security group in TM1. You can then create a standard TM1 security group per user in TM1 and assign each user to their Security Group using a TI process. When you first create a Group per user you will probably need to increase the GroupsCreationLimit setting in your TM1S.CFG, but that only needs to be increased for the initial run. After that it can be reset to the normal limit of 20 per session.

    However, the approach of creating a Group per user using TM1 Groups, rather than in AD will only work, if you don't also need those security groups for Cognos Analytics. Eg, in our case CA only queries relational data, not PA. If not then you might need to look at creating a Security Group per user in AD.

    .There isn't really another approach to solving this that I can think of, other than user specific security groups.Trying to write user specific rules is not a good solution. 

    Regards

    Paul Simon

    ------------------------------
    Paul Simon
    ------------------------------