Summation on consolidation levels depending on security

[maretm1]

Hi!

My situation is following.
I have a cube with 4 dimensions:
- Time diimension t_mjesec (holds four next months and previous month)
- Measures dimension m_prodaja (consists of 4 measures, on image is show Prijedlog Plana - Proposed Plan)
- Hierarchy dimension h_artikl (created using data from the table from database) - list of products that has 5-level hierarchy. Also, we created alternative hierarchy based on suppliers for each product. This hierarchy has main consolidation level called SVI DOBAVLJACI - All Suppliers, and has two levels above level 0. Main hierarchy's highest consolidation level is SVI ARTIKLI - All products.
- Generic dimension p_mp_vp (consist of two elements and their consolidation)

Main hierarchy for products is:
Level 5: All Products (SVI ARTIKLI)
Level 4: Sectors
Level 3: Categories
Level 2: Groups
Level 1: Subgroups
Level 0: Products

Security is based on 3rd level of main hieararchy for products - categories. Each user has one or more categories he is in charge of and he can se only his node of data.
Problem is that even though user sees only some categories, on sector level is shown sum of all the categories in that sector, and it should show only sum for categories that user can see.

artikli.png (278.1 KiB) Viewed 7893 times

I have the same problem with alternative hierarchy because it shows sum for all of the products for one supplier, and user cant see them all (only few).

dobavljaci.png (282.18 KiB) Viewed 7893 times

Is there any way of achieving this without adding another dimension in cube?

We have set Element Security and Cell Security set. Users see cube through TM1 Web Application whose type is approval.

[tm1_bloke]

This is how TM1 dimension security works and you cannot change that fact. Of course we can argue what should be "the right way" to handle consolidated values when there is dimension security applied to underlying elements.

You need to work out a workaround to resolve you problem. One option could be creating consolidated elements by user and then applying respective dimension element security. This may not the most ideal approach as I can imagine the maintenance overhead it possibly creates.

[maretm1]

Thank you for your answer.
I have read that this is so, but I was hoping that this was resolved with new fix packs or something, because for me this doesnčt make any sense.
I know the workaround, but I would like to not having to do them every time I have his situation, and that is often.
Either way, thank you, workaround it is.
Hope this issue will be solved with new releases.

[tm1_bloke]

I am afraid that this will not be resolved in coming fixes. This is the way that TM1 security paradigm is and has been "always". And like I said, we can argue whether this implementation is correct or not. In my opinion, the existing way is the correct one - if you enable user to see consolidated value, it is obvious to me that all users will see the same value, not the value that is derived from their individual access rights to the children of consolidation.

[Wim Gielis]

I was hoping that this was resolved with new fix packs or something
Hope this issue will be solved with new releases.

Don't think so... This is so fundamental in the algorithms in TM1 that I doubt this will be possible any time soon.
Nevertheless it would be useful in some circumstances, though we must be cautious when values at C-level can change depending on who logs in.

[maretm1]

I am afraid that this will not be resolved in coming fixes. This is the way that TM1 security paradigm is and has been "always". And like I said, we can argue whether this implementation is correct or not. In my opinion, the existing way is the correct one - if you enable user to see consolidated value, it is obvious to me that all users will see the same value, not the value that is derived from their individual access rights to the children of consolidation.

But that is the number that they mustnt see, they can only see their values.
And it doesnt create big problem if you have one hierarchy, and you can hide consolidated levels that they shouldn't see (in this example I can hide Sectors and All products, and this should be fine), but when you have alternative hierarchy in the same dimension, then this is a problem.

[Steve Rowe]

I think that any security solution that ends up with users seeing different values for the same node would be pretty flawed and lead big issues with supportability.

In your situation where the object you are using to manage access cuts across the another structure then you probably need to modify your alternate hierarchy to include Categories, this will mean concatenating Category and Supplier codes.

Revised Alt hierarchy, you can use the current L3 security but can not see the total at the Supplier level.
Level 5: All Products (SVI ARTIKLI) 2
Level 4: Sectors 2
Level 3: Categories
Level 2: Supplier - Category
Level 1: XXXXX
Level 0: Products

or

Revised Alt hierarchy, New security required to manage access to the new Supplier - Category groups.
Level 5: All Products (SVI ARTIKLI) 2
Level 4: Sectors 2
Level 3: Supplier
Level 2: Supplier - Category
Level 1: XXXXX
Level 0: Products

[maretm1]

In your situation where the object you are using to manage access cuts across the another structure then you probably need to modify your alternate hierarchy to include Categories, this will mean concatenating Category and Supplier codes.

Revised Alt hierarchy, you can use the current L3 security but can not see the total at the Supplier level.
Level 5: All Products (SVI ARTIKLI) 2
Level 4: Sectors 2
Level 3: Categories
Level 2: Supplier - Category
Level 1: XXXXX
Level 0: Products

or

Revised Alt hierarchy, New security required to manage access to the new Supplier - Category groups.
Level 5: All Products (SVI ARTIKLI) 2
Level 4: Sectors 2
Level 3: Supplier
Level 2: Supplier - Category
Level 1: XXXXX
Level 0: Products

Thank you, but this is not a solution as my hierarchies are predefined by the data in the database.

I solved this by adding approval dimension (consisted of categories) in the cube. That way data is filtered by chosen approval element and users see only their data.
This is OK solution, but it would be great if it could be avoided.

[David Usherwood]

this is not a solution as my hierarchies are predefined by the data in the database

That's your problem.
Nothing stopping you importing secondary hierarchies from a flat file though.

[maretm1]

this is not a solution as my hierarchies are predefined by the data in the database

That's your problem.
Nothing stopping you importing secondary hierarchies from a flat file though.

By predefined I mean they have to be like that and I mustn't change them.
I know how to change existing hierarchy, but that is not an option. Hierarchies are made in that way for a reason, and they should stay like that.

[qml]

Hierarchies are made in that way for a reason, and they should stay like that.

Incidentally, TM1's element security is also made that way for a reason, and it should stay like that.

Multidimensional OLAP, which TM1 is a prime example of, uses different logic for security than relational databases (including Relational OLAP). In the relational world security happens via inner joins which are done before aggregation. In MOLAP security is applied after aggregation.

Personally I think the MOLAP approach to security is better. That way, each object has one consistent definition. There is only one company revenue, not as many versions of it as there are users. You can either see that revenue or you can't, but there is only one definition of it and that is arguably a good thing. It's a support nightmare when users run the same report with the same layout and selections, and they get different numbers because of different entitlements.

Having said that, there are a few ways in which relational-style security can be mimicked in TM1. The most straightforward one is to build personalised hierarchies for each unique security profile and using element security to give users access only to 'their' hierarchy. There are other ways too, but their use is hugely dependent on the structure of your dimensions and how security is managed for them (e.g. on which levels). And the front end you are using also plays a huge role, as it has to be able to elegantly utilise the implemented security workaround.

[paulsimon]

Hi

I tend to agree with QML. I have worked with both relational and OLAP databases, and the relational approach to security tends to cause huge arguments among users as to whose figures are right because two users can get a different number for the same consolidation. The OLAP approach is at least consistent. It should not be regarded as a bug that should be fixed in a future release.

If you want to do this in TM1 then one possible approach is to add a User dimension to the cube and have something like this where Entry User is where you load the input

['Entry User' , 'Value'] =N: STET ;

['Value''] =N: ['Entry User' , 'Value] * IF( DB( '}ElementSecurity_MyDim' , !MyDim, 'UserGrp_' | !User ) @= 'READ' , 1 , 0 ) ;

with the assumption that 'UserGrp_' | !User generates the name of the personal group for each user.

Having said that, doing the above by rules is inefficient and an alternate hierarchy per user would be more efficient. As a rough rule of thumb a consolidation based calculation is about 100 times faster than a rule based calculation.

Regards

Paul Simon