Cell Security in TM1

Post Reply
tm1expert
Posts: 27
Joined: Sun Aug 02, 2009 2:57 am
OLAP Product: Cognos TM1/Cognos BI
Version: from 9.4 to 10.2.2
Excel Version: 2013

Cell Security in TM1

Post by tm1expert » Thu Oct 18, 2012 1:12 pm

Does anybody have any experience with CellSecurity implementation in TM1? We currently use ElementSecurity in our model. We have 2 dimensions (Region and Service) where we have implemented ElementSecurity so our users have been granted access to 1 or more Regions and to 1 or more Services, therefore they can access/modify the data related to these dimension elements in all the cubes where those dimensions are used. With this solution, if UserA was granted Access to Region_1 and Region_2, and to Service_1 and Service_2, then he will have access to any intersection of these elements. But our security requirements have changed, users want to be able to grant access only to Region_1/Service_1 and Region_2/Service_2 (Not Region_1/Service_2 or Region_2/Service_1). So we need to implement CellSecurity in order to achieve that. My concern is regarding the number of Security Groups we have to create in order to accomodate these kind of requirements, since we will have to create a group for each combination of Service and Region. Has anybody done something like that, and how does it impact the performance?

User avatar
George Regateiro
MVP
Posts: 326
Joined: Fri May 16, 2008 3:35 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP3
Location: Tampa FL USA

Re: Cell Security in TM1

Post by George Regateiro » Thu Oct 18, 2012 1:34 pm

As long as you have a good group convention the amount of groups is not really an issue. With the information given I think the easiest way to achieve this would probably be through a group per user rather then a group per intersection (the user name would also be the group name for a one to one match). This way you assign the all the privileges for a single user to the group named for them. As long as the number of Region\Services intersections remains somewhat manageable you should be able to create a decent way to manage it through a template interface (you many have to be creative on this part do to the amount of intersections).

As far as performance goes you should be fine as long as you do not use rules. We have thousands of clients with an equal number of groups and security is in no way the bottle neck to performance. Rule based security can be good for some smaller uses, but I would generally avoid it when you are at large amounts or element and cell security.

You will reach a point though that the Clients and Groups GUI stops working and you will have to have another way to mange adding clients and groups. Easily achieved through TI, but figured I would put it out there.

tomok
MVP
Posts: 2575
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cell Security in TM1

Post by tomok » Thu Oct 18, 2012 1:45 pm

George Regateiro wrote:You will reach a point though that the Clients and Groups GUI stops working and you will have to have another way to mange adding clients and groups. Easily achieved through TI, but figured I would put it out there.
This is a little misleading. I think what you mean to say is that the more groups you have, the less effective the security GUI is for maintaining the security because it can get really slow. It doesn't stop working. Having said that, I would never use the GUI anyway as either TI or modifying the security cubes directly is the best option.

I personally have not used Cell Security very often because most of the time the security can be done via Element Security combined with a well thought out group structure. In the OP's case, I would definitely not create a group for every intersection unless I absolutely had to. He should look at his organizational setup to see if there is a way to distribute security along that. Even if this structure is not in a dimension, he could still structure the groups along this.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

tm1expert
Posts: 27
Joined: Sun Aug 02, 2009 2:57 am
OLAP Product: Cognos TM1/Cognos BI
Version: from 9.4 to 10.2.2
Excel Version: 2013

Re: Cell Security in TM1

Post by tm1expert » Thu Oct 18, 2012 2:11 pm

To give you some more insight, we are planning to maintain the Users/Groups outside of TM1, in an Excel Spreadsheet and then load the Security Matrix Spreadsheet into TM1 through TI, so the TM1 Security GUI is not an issue for us.

I would like to know some more details on how we can avoid the creation of a group for each intersection, since this is my biggest concern.

Any idea on how can we re-orginize the Group Structure would be appreciated.

Thank you very much

tomok
MVP
Posts: 2575
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cell Security in TM1

Post by tomok » Thu Oct 18, 2012 2:23 pm

tm1expert wrote:Any idea on how can we re-orginize the Group Structure would be appreciated.
Sorry, I don't know how I could possibly help you. This is not a TM1 issue, but an organizational issue around how your company works and how you are planning on giving out security access. I know next to nothing about your situation.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

tm1expert
Posts: 27
Joined: Sun Aug 02, 2009 2:57 am
OLAP Product: Cognos TM1/Cognos BI
Version: from 9.4 to 10.2.2
Excel Version: 2013

Re: Cell Security in TM1

Post by tm1expert » Thu Oct 18, 2012 2:30 pm

Thanks Tom,

What I meant is any example of how you can avoid a group for each intersection when you want to make possible granting users access only to specific intersections:)

COmbining 2 dimensions into one is not an option for us

Anyway thank you very much

tomok
MVP
Posts: 2575
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cell Security in TM1

Post by tomok » Thu Oct 18, 2012 3:58 pm

tm1expert wrote:What I meant is any example of how you can avoid a group for each intersection when you want to make possible granting users access only to specific intersections:)
You don’t have to be a TM1 expert to figure it out because it’s not really about TM1, it’s about logically organizing data. For the sake of argument let’s say that security is assigned by person in TM1 (I know it’s not but you can make it so by creating a group for every person so that there is a one-to-one relationship). I don’t know the size of your Region and Service dimensions but I’m going to guess that the number of intersections between the two greatly exceeds the number of TM1 users. If that’s the case, and you can assign rights by user, why would you ever need to go beyond that level of granularity and have a group for every intersection?
Last edited by tomok on Thu Oct 18, 2012 4:41 pm, edited 1 time in total.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

tm1expert
Posts: 27
Joined: Sun Aug 02, 2009 2:57 am
OLAP Product: Cognos TM1/Cognos BI
Version: from 9.4 to 10.2.2
Excel Version: 2013

Re: Cell Security in TM1

Post by tm1expert » Thu Oct 18, 2012 4:34 pm

Thank you. We have more than 200 users and creating a group for each user will not be efficient, the combination of Region/Service gives us smaller number of Groups since we have lot's of users who will have access for "All Services" or "All Regions" and this reduces the number of combinations.

Thank you
Ardian

tomok
MVP
Posts: 2575
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Cell Security in TM1

Post by tomok » Thu Oct 18, 2012 4:45 pm

tm1expert wrote:the combination of Region/Service gives us smaller number of Groups since we have lot's of users who will have access for "All Services" or "All Regions" and this reduces the number of combinations.
That's an interesting tidbit of information considering that's not what you said in your original question:
tm1expert wrote:My concern is regarding the number of Security Groups we have to create in order to accomodate these kind of requirements, since we will have to create a group for each combination of Service and Region.
Nevertheless, you should pick the option that is the best combination of 1) ease of implementation, 2) ease of maintenance, and 3) performance.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

tm1expert
Posts: 27
Joined: Sun Aug 02, 2009 2:57 am
OLAP Product: Cognos TM1/Cognos BI
Version: from 9.4 to 10.2.2
Excel Version: 2013

Re: Cell Security in TM1

Post by tm1expert » Thu Oct 18, 2012 6:08 pm

Thanks Tom,

By analizing the data (and the existing security structure), I currently have to create 121 Groups if I create a group for each secured intersection, and I have 238 entries in }Clients dimension, so creating one group for each user is not the best option. But still creating 121 Groups in addition to other Groups that we already have (Around 50 other groups) is still a concern, and in the future I'm sure that we will keep adding combinations and we might end up with a model with 300 groups. In this case, how does it impact the performance? I know that there is a reccomendation to keep the number of Groups low (60 i guess is the recommended setting), but we obviously need to exceed that. Any advice would be appreciated

Ardian

User avatar
Steve Rowe
Site Admin
Posts: 1933
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Cell Security in TM1

Post by Steve Rowe » Fri Oct 19, 2012 10:28 am

Ardian,
I'm not convinced you need to create a group for every intersection, you need to look at the sets of combinations that are used to gether and create security groups for only them.

So one security group could cover insections A,1 B,5 G,8 etc
Another could cover A,1 C,5 G,8
and so on

Once you have some analysis about which users need access to which sets of intersections you should be able to minimise the number of groups required.
HTH

tm1expert
Posts: 27
Joined: Sun Aug 02, 2009 2:57 am
OLAP Product: Cognos TM1/Cognos BI
Version: from 9.4 to 10.2.2
Excel Version: 2013

Re: Cell Security in TM1

Post by tm1expert » Fri Oct 19, 2012 1:57 pm

Thank you Steve,

This is what I'm trying to do, but since it will be maintained from end users in an Excel Spreadsheet, I'm affraid that they will go crazy and enter too many intersections in the spreadsheet. What is you experience with Cell Security and Performance impact?

Ardian

Post Reply