Aggregation of Access restricted elements in rule file

[Krish]

Hi Folks,

We are using TM1 10.1

I have applied element level security on a ‘County’ dimension.

Country Dimension has members A,B,C,D and X .
User 1 has access only to elements A,B and X
User 2 has access only to elements C,D and X
‘Revenue’ measure is loaded for all elements in country dimension (Except X, which is to store Sum)

Is there any way to store the sum of revenues in X, for the elements a particular user has access ?
Ex: In the case of User 1 it should be A+B, for user 2 it should be C+D

I have given a rule for Element 'X' as X = A+B+C+D assuming that C,D are access restricted for User 1 and they may not be included in Sum, but the sum includes C,D also.

Is there any way to included access enabled elements only in calculation ?

Any suggestions will be appreciated. Thanks.

[tomok]

The answer to all your questions is no. You cannot have a cell in TM1 where the calculation for different users is dependent on whether or not they have the appropriate security rights to it. The only way to do what you want would be to add the }Groups dimension to the cube, essentially creating a different set of calculations for each security group, and writing a rule that looks at the rights in the }ElementSecurity_Country cube for each group and makes the value calculate as appropriate. However, this would really be bad practice it would greatly increase the size of the model and open up a Pandoras box of other problems if you didn't fully comprehend what you were doing.

[qml]

Another example of a possible way to achieve this would be to use a combination of the TM1User() rules function and a reference to a lookup cube and/or the }ClientGroups cube to personalise calculations based on group membership or other factors. This of course would be outside of good practice in all but a few very specialised use cases, but it could definitely be achieved.

I have built a model in the past that had personalised attribute values (including aliases), different for each user and also time-dependent. It was more a proof of concept than something I would recommend, but hey, TM1 is a flexible beast.

[paulsimon]

Hi Krish

I have applied element level security on a ‘County’ dimension.

Country Dimension has members A,B,C,D and X .
User 1 has access only to elements A,B and X
User 2 has access only to elements C,D and X
‘Revenue’ measure is loaded for all elements in country dimension (Except X, which is to store Sum)

Is there any way to store the sum of revenues in X, for the elements a particular user has access ?
Ex: In the case of User 1 it should be A+B, for user 2 it should be C+D

I have given a rule for Element 'X' as X = A+B+C+D assuming that C,D are access restricted for User 1 and they may not be included in Sum, but the sum includes C,D also.

The simple solution is to create intermediate consolidations below X called eg AB as the total of A & B and CD as the total of C & D. Then give User 1 access to AB and User 2 access to CD.

I have implemented more complex variations of this where security was applied to more than one dimension. That involved adding an App_Security dimension to the Cube. The elements in this dimension represented valid combinations of the elements from the dimensions on which security was applied. This then had consolidations in it for each user which gave the total of the combined elements that they were allowed to access. Rather than applying security to the individual dimensions we then applied security to the App_Security dimension so that each user just got access to their User consolidation. That worked but was complex to maintain. It did not, however, actually increase the size of the base data in the cube.

At the risk of starting a philosophical debate, what you are trying to do is to mimic the behaviour of a Relational OLAP tool in a Multi-Dimensional OLAP tool. Personally I feel that the Multi-Dimensional OLAP approach is correct. If people are given access to X then they should see the correct total for X. I have worked on Relational OLAP tools as well and we always had complaints from users who wanted to know why their figure for X was different to some other users figure for X. The answer was generally the security access. From the business perspective, if someone is responsible for X then I feel that they should be able to see all the parts that make up X. If they are only responsible for only a part of X, then there should be an intermediate consolidation that represents the part of X for which they are responsible and they should have access to that - which is effectively my first solution.

I have given a rule for Element 'X' as X = A+B+C+D

By the way, if you can do something by consolidation in a dimension then do it that way - it will be much faster than rules.

Regards

Paul Simon

[Gregor Koch]

Agree with Paul on all accounts. Good post.