Prevent users from inputting data into consolidated cells

Post Reply
User avatar
Ogram
Posts: 8
Joined: Thu Dec 13, 2012 8:03 am
OLAP Product: TM1
Version: TM1 9.5.2 - 10.2.1
Excel Version: 2003 2007 2010

Prevent users from inputting data into consolidated cells

Post by Ogram » Mon Sep 15, 2014 9:20 am

To be honest I feel a bit ashamed to be asking something apparently so simple, but for the death of me I haven't been able to find an answer no matter what/how I googled...

How do I prevent users from being able to enter data into consolidated cells in my applications? E.g. users should only be able to input data into leaf-level elements (months and specific accounts) and the consolidated cells should be greyed out/locked. I don't mean simply disabling spreading but preventing users from entering data into C-level cells altogether. They should simple reflect the changes users make to the lower level leaf cell data. Can I do this with a rule within the actual cubes or will I need to look into cell/element security? I could simply just fire away with []=C:ConsolidateChildren('Dim1','Dim2'...'DimLast') but I assume the performance would be atrocious as it would effectively skip SKIPCHECK.

I'm working with TM1 10.2.1 with only PROD available to me (and I know how terrible this is, but this is what I need to make-do with).
20 % cooler.

Wim Gielis
MVP
Posts: 2345
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by Wim Gielis » Mon Sep 15, 2014 9:47 am

Hello

Do not use that ConsolidateChildren, have a look at element security. Not dimension security.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

User avatar
Ogram
Posts: 8
Joined: Thu Dec 13, 2012 8:03 am
OLAP Product: TM1
Version: TM1 9.5.2 - 10.2.1
Excel Version: 2003 2007 2010

Re: Prevent users from inputting data into consolidated cell

Post by Ogram » Wed Sep 17, 2014 10:32 am

Wim Gielis wrote:Hello

Do not use that ConsolidateChildren, have a look at element security. Not dimension security.
Thank you, you confirmed what I was thinking about. Could you please further clarify the following to make sure I really got the gist of things:

1. If user belongs to two different CAM groups, user will have the highest permission level available in those groups.
2. If there are different restrictions affecting a single cell (e.g. Cell Security cube grants WRITE, Dimension security grants READ and Element Security grants NONE), the highest restriction will be applied. In the example, it would effectively hide the element since the user would have NONE level rights to it.
3. The application will automatically apply all the security elements available, for example it will use both the cube-specific Cell Security cube and the element/dimension-specific security cubes to enforce security if they can be found.
4. If I set a consolidation element to READ and leave the lower level elements blank, will those blank elements count as having NONE, READ or WRITE-level access or is the access in this case decided by security restrictions inherited from other security sources
5. If I want to prevent users from entering data into consolidated cells, I should be able to achieve this by
  1. Adding a rule to the automatically created Cell Security cube (that only contains the approval hierarchy and }Groups) which changes all the C-level elements to READ
  2. Adding a similar rule to the Element Security cubes for each dimension that contains C-level elements and is used in the applications visible to the end user
Thank you. I wanted to write this out in detail just in case someone else might be looking at a similar problem. IBM documentation is a tad prone to either ambiguity or hitting everything but the mark.

Regards,
Jussi
20 % cooler.

User avatar
Alan Kirk
Site Admin
Posts: 6028
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by Alan Kirk » Wed Sep 17, 2014 10:48 am

Ogram wrote:
Wim Gielis wrote: Do not use that ConsolidateChildren, have a look at element security. Not dimension security.
Thank you, you confirmed what I was thinking about. Could you please further clarify the following to make sure I really got the gist of things:

1. If user belongs to two different CAM groups, user will have the highest permission level available in those groups.
2. If there are different restrictions affecting a single cell (e.g. Cell Security cube grants WRITE, Dimension security grants READ and Element Security grants NONE), the highest restriction will be applied. In the example, it would effectively hide the element since the user would have NONE level rights to it.
3. The application will automatically apply all the security elements available, for example it will use both the cube-specific Cell Security cube and the element/dimension-specific security cubes to enforce security if they are available.
4. If I set a consolidation element to READ and leave the lower level elements blank, will those blank elements count as having NONE, READ or WRITE-level access or is the access in this case decided by security restrictions inherited from other security sources
5. If I want to prevent users from entering data into consolidated cells, I should be able to achieve this by
  1. Adding a rule to the automatically created Cell Security cube (that only contains the approval hierarchy and }Groups) which changes all the C-level elements to READ
  2. Adding a similar rule to the Element Security cubes for each dimension that contains C-level elements and is used in the applications visible to the end user
Thank you. I wanted to write this out in detail just in case someone else might be looking at a similar problem. IBM documentation is a tad prone to either ambiguity or hitting everything but the mark.
You are aware that users cannot write to consolidated cells, right?

The two exceptions are:
(a) If the element in the last dimension is an S (String (that is, text)) type; or
(b) If you are doing a data spreading Clear function.

In any other case they can hammer away until the cows come home and they will never be able to punch a number into a consolidation element.

If you're trying to prevent input into string elements then as Wim said, you use Element security. You can assign "READ" access to every element that is higher than level 0. This can be done either manually or by a rule, though to be honest I dislike using rules for security unless there is a need to make the security dynamic in some way.

Let me rearrange your explanation of security.

TM1 operates on a "least restrictive" basis. That means that if the user is in two groups, and group 1 says they have write access and group 2 says that they have read access for the same type of security (be it cube, dimension or element), they have write access.

If group 1 says they have read access and group 2 says that they have no access, they have read access.

This applies no matter whether it's cube, dimension or element security.

Cube security determines the access that they have to a particular cube. If they have no access to it, then the security assignments for dimensions and elements is academic.

Dimension security determines whether they can see the dimensions. If they have read or write access to a cube they should have read access to all of the cube's dimensions. If you assign cube security through the GUI this is taken care of for you.

Element security determines whether they have read or write access to the specific elements. To be able to write to a cell the user must have WRITE access to the relevant element of EVERY dimension. They will have this by default for any dimension which does not have element security on it. For any dimensions which DO have element security, they must be assigned permission separately. If they have WRITE access to the elements of one dimension in the cube but only READ access to elements of another dimension, then they have only read access to that cell. This is not a violation of the "least restrictive" rule because the element security for each dimension operates independently of the other ones.

Also security doesn't "cascade down" as I think you were suggesting in point 4. If you set security for a consolidation element it doesn't automatically apply to the elements below. (Consider what would happen if you had multiple hierarchies and in one the parent element had READ access, and in another it had WRITE access; what security would apply to the child element? For that reason security needs to be set for each element separately, whether by rules or manually.)

In summary then to write to a value they must have:
WRITE access to the cube;
At least READ access to all of the dimensions; and either
The dimensions should not use element security OR they have WRITE access to the elements for every dimension which uses element security.

Oh, and unless the element in the last dimension is a String type, NONE of the elements can be consolidations.

Security is a bit of a black art but with a little practice and experience it's not that hard to get your head around.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
jim wood
Site Admin
Posts: 3765
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by jim wood » Wed Sep 17, 2014 11:47 am

Alan,

I didn't read all of your post. I caught the bit about "Less restrictive". Unless something has changed in the last couple of years that I wasn't aware of, read always takes presedence (I can't spell I know) over write. So if you are in 2 groups, one with read and one with write, you get read. That's as I have known it since starting with it about 17 years ago. I say this not to brag, but to point out when I was informed of this,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
Michel Zijlema
Site Admin
Posts: 706
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by Michel Zijlema » Wed Sep 17, 2014 12:10 pm

jim wood wrote:Alan,

I didn't read all of your post. I caught the bit about "Less restrictive". Unless something has changed in the last couple of years that I wasn't aware of, read always takes presedence (I can't spell I know) over write. So if you are in 2 groups, one with read and one with write, you get read. That's as I have known it since starting with it about 17 years ago. I say this not to brag, but to point out when I was informed of this,

Jim.
Hi Jim,

I was typing an answer but I see Alan already responded. Anyway...

Within one security group most restrictive right wins - unless you're using cell security, which works as an 'overlay' and takes precedence over everything.
When you're member of multiple groups, your access rights will be a join of the rights of the various groups, where the least restrictive wins.

Michel

User avatar
Alan Kirk
Site Admin
Posts: 6028
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by Alan Kirk » Wed Sep 17, 2014 12:11 pm

jim wood wrote:Alan,

I didn't read all of your post. I caught the bit about "Less restrictive". Unless something has changed in the last couple of years that I wasn't aware of, read always takes presedence (I can't spell I know) over write. So if you are in 2 groups, one with read and one with write, you get read. That's as I have known it since starting with it about 17 years ago. I say this not to brag, but to point out when I was informed of this,

Jim.
To paraphrase Al Borland, "I don't think so, Jim". Allow me to demonstrate.

I have created a read group and a write group. To the read group, I hath bestowed read permissions to cube and element, and no more. To the write group, I hath bestowed write permissions. And verily have I dumped a client named JWood into both groups (you'll need to scroll on this one to see all of the settings):
ViewFromAdminLand.jpg
ViewFromAdminLand.jpg (179.06 KiB) Viewed 8613 times
And I looked upon my work, and it was good. And verily did I log mineself into Architect, impersonating yourself in the process.

And so did the WriteTo group bestow upon the me that impersonated you, Write permissions unto the cube:
TheViewFromUserLand.jpg
TheViewFromUserLand.jpg (227.35 KiB) Viewed 8613 times
And thus has it been for my own 16 years in the desert as best I recollect it, that the security shall be of the least restrictive type.

Edit: In the light of Michel's post I should clarify (or more accurately "reiterate" since I did mention it in the original post) that the "least restrictive" rule applies to multiple groups assigning rights to the same object. Michel is right that if it's a single group, and that group gives only Read access to the cube, then it doesn't matter if it also gives Write access to the elements. The Read access for the cube wins. But if the user is in another group that gives Write access to the cube, then Write access to the cube is what they have.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Alan Kirk
Site Admin
Posts: 6028
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by Alan Kirk » Wed Sep 17, 2014 12:13 pm

Michel Zijlema wrote:
jim wood wrote:Alan,

I didn't read all of your post. I caught the bit about "Less restrictive". Unless something has changed in the last couple of years that I wasn't aware of, read always takes presedence (I can't spell I know) over write. So if you are in 2 groups, one with read and one with write, you get read. That's as I have known it since starting with it about 17 years ago. I say this not to brag, but to point out when I was informed of this,

Jim.
Hi Jim,

I was typing an answer but I see Alan already responded. Anyway...
Alan did, but then {cough} realised that he screwed up one of the screenshots and has reposted the corrected version.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Ogram
Posts: 8
Joined: Thu Dec 13, 2012 8:03 am
OLAP Product: TM1
Version: TM1 9.5.2 - 10.2.1
Excel Version: 2003 2007 2010

Re: Prevent users from inputting data into consolidated cell

Post by Ogram » Wed Sep 17, 2014 12:40 pm

Alan Kirk wrote:Security is a bit of a black art but with a little practice and experience it's not that hard to get your head around.
Well, it seems I had the right of it, at least in a ballpark sense, but I might have worded my question a bit ambiguously while blaming the IBM documentation for doing the same :?

What I'm trying to achieve is to prevent users from spreading down from a consolidated cell by preventing them from being able to input data into a consolidated cell directly, only via its children.

Regarding the cascading security, I meant to say that if there's no security specified for an element, it would use the security level of the nearest contextual source when in a cube (either from other elements of the specific cell or from cube-level security, not from a parent or ancestor in the same dimension). So I was under the correct impression but worded my explanation in a lacking manner.

So basically the security is 'Least restrictive' within a security cube for a user who belongs to multiple groups (Group1 vs. Group2 vs. etc...) but 'most restrictive' vertically (Cube -> Element -> Cell)

Thank you Alan et al., I think I'm slowly getting there. It's a simple thing really, had someone at the IBM had the ability to put it so.

PS. This post was written before Jim & others joined in but was not posted due to coffeebreak related reasons.

EDIT:
Michel Zijlema wrote: Within one security group most restrictive right wins - unless you're using cell security, which works as an 'overlay' and takes precedence over everything.
When you're member of multiple groups, your access rights will be a join of the rights of the various groups, where the least restrictive wins.
So it's 'Most Restrictive' in order 'Cube -> Element' UNLESS there's a cell security cube, which takes precedence. So when there's only the automatically generated }CellSecurity_CubeXXX-cube that contains only the }Groups and Approval dimensions, the element security in other dimensions is rendered irrelevant?
20 % cooler.

User avatar
jim wood
Site Admin
Posts: 3765
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by jim wood » Thu Sep 18, 2014 1:51 pm

Taken from the IBM documentation:
Interaction of Different Object Security Rights

If you apply different security rights to the objects that identify a cell of data, TM1® applies the most restrictive security right to the cell.


Scenario 1

Suppose you assign a user Read access to the SalesCube cube, and Write access to the elements in this cube. In this scenario, the Read access of the cube overrides the Write access of the elements, and the user can view cube data but cannot update the cube data.

Scenario 2

The SalesPriorCube cube contains the following dimensions:

Actvsbud


Region


Model


Account1


Month


Suppose a user has Write access to the SalesPriorCube cube, Read access to all of the elements in the Actvsbud dimension, and Write access to all of the elements in the other dimensions. The elements in the Actvsbud dimension identify every cell in the cube, and therefore the user cannot update any cube data.

Scenario 3

You can change the security rights for both cubes and dimensions. When groups have security rights for a cube, those rights apply to all dimensions in the cube, unless you further restrict access for specific dimensions or elements.

Suppose you want several regional groups of users to read all data in the SalesPriorCube cube. You also want each group to update data in its own region. For example, you want salespeople in the North America group to update North America data.

To implement this security scheme, you could:

Create groups that reflect sales regions.


Add users to the appropriate groups.


Grant each regional group Write access to the SalesPriorCube cube.


Grant the North America group Read access to those elements that do not reflect data for the North America region.


The TM1 sample data reflects this security scheme. Usr1 is in the North America group, which has Write access to the data associated with countries in the North America region, and Read access to the data associated with countries in other regions.
http://pic.dhe.ibm.com/infocenter/ctm1/ ... nt_Ob.html
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

declanr
MVP
Posts: 1638
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by declanr » Thu Sep 18, 2014 2:04 pm

I think everyone is coming from different angles here and it could confuse the hell out of people who stumble across this post. Everyone is correct - you're all just arguing different points.

Alan pointed out that least restrictive wins on a single type of security:
e.g. User has access to Groups A & B
Group A - Write access to CUBE Z
Group B - No access to CUBE Z
= USER has WRITE access to CUBE Z.

Jim pointed out that most restrictive wins on complimentary security types:
e.g. User has access to Groups A & B & C
Group A - Write access to CUBE Z
Group B - No access to CUBE Z
= USER has WRITE access to CUBE Z (as above)
BUT:
Group A & B - No access to elements of DIM X in CUBE Z
Group C - Read access to elements of DIM X in CUBE Z
= USER has READ access to elements of DIM X in CUBE Z

Therefore overall result is that the user only has READ access to CUBE Z because although he could theoretically WRITE to the cube, he isn't allowed to WRITE to any of it's cell intersections.


All in all I have probably confused matters further... but everyone is WRITE (see what i did there?)
Declan Rodger

User avatar
jim wood
Site Admin
Posts: 3765
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by jim wood » Thu Sep 18, 2014 2:39 pm

declanr wrote:All in all I have probably confused matters further... but everyone is WRITE (see what i did there?)
None of understand what you mean, even after taking some time to Read it. I'll get my coat......
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

Wim Gielis
MVP
Posts: 2345
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by Wim Gielis » Thu Sep 18, 2014 5:50 pm

jim wood wrote:
declanr wrote:All in all I have probably confused matters further... but everyone is WRITE (see what i did there?)
None of understand what you mean, even after taking some time to Read it. I'll get my coat......
Can some Admin person over here lock the topic please? :-) Joking...
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

User avatar
Alan Kirk
Site Admin
Posts: 6028
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by Alan Kirk » Thu Sep 18, 2014 8:07 pm

declanr wrote:I think everyone is coming from different angles here and it could confuse the hell out of people who stumble across this post.
I think that will only be a danger if the person stumbling doesn't take the time to read the entire post, but rather takes two words from it and runs with them. Because the distinction between:
(a) Different security for the same type of object (which is the context in which I used the expression "least restrictive"); and
(b) Security at different levels of the object hierarchy
had in fact been made from the outset.

(a)
I wrote:That means that if the user is in two groups, and group 1 says they have write access and group 2 says that they have read access for the same type of security (be it cube, dimension or element), they have write access.
(b)
I wrote:Cube security determines the access that they have to a particular cube. If they have no access to it, then the security assignments for dimensions and elements is academic.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Michel Zijlema
Site Admin
Posts: 706
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Prevent users from inputting data into consolidated cell

Post by Michel Zijlema » Fri Sep 19, 2014 9:22 am

Ogram wrote:
Michel Zijlema wrote: Within one security group most restrictive right wins - unless you're using cell security, which works as an 'overlay' and takes precedence over everything.
When you're member of multiple groups, your access rights will be a join of the rights of the various groups, where the least restrictive wins.
So it's 'Most Restrictive' in order 'Cube -> Element' UNLESS there's a cell security cube, which takes precedence. So when there's only the automatically generated }CellSecurity_CubeXXX-cube that contains only the }Groups and Approval dimensions, the element security in other dimensions is rendered irrelevant?
The access assignments in the CellSecurity cube will only take precedence when there are actually access assignments there - an empty CellSecurity cube will change nothing in the 'underlying' security settings. Also note that when you set cube, dimension or dimension element security to NONE, the CellSecurity is 'irrelevant' - CellSecurity only takes effect on visible objects...

Michel

Post Reply