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.
Pinned topic Aggregation of Access restricted elements in rule file
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-08-01T18:15:43Z at 2012-08-01T18:15:43Z by Rizwan.Kaif
Rizwan.Kaif 27000219XW197 Posts
Re: Aggregation of Access restricted elements in rule file2012-08-01T18:15:43ZThis is the accepted answer. This is the accepted answer.This question is commonly asked by users. But if you need to look at the how the Consolidations work within a dimension. For example you have a Sales cube with a Region dimension in it. The region dimension has following structure:
Global->Europe 30 Global->Europe->France 10 Global->Europe->Germany 10 Global->Europe->UK 10
In this case the Sales of Europe will show as 30 because it is the sum of sales of the three countries. If a user only have access to France and Europe in the Region dimension then user will still see 30 under Europe. Because by just changing the Security rights the Geographical definition of Europe will not change.
Global->Europe 30 Global->Europe->France 10
One way of dealing with such situations is to create Alternate Hierarchies. For example in your case you can create an alternate hierarchy i.e.
X1 = A + B X2 = C + D X = X1 + X2
For User 1, give access to A, B and X1
For User 2, give access to C, D and X2
For User 3, give access to A, B, C, D, X1, X2 and X (Top level/ admin user)
Hope that helps!