Cell Level Security Rules on Consolidations

[damientaylorcreata]

Hi Guys,

I currently have cell level security setup with rules at the N level.. However I have been asked to extend this to consolidations. However I don't see how this is possible.

For example:

If I had a rule that said allow access to user "user1" to be able to see all P&L figures for sales office "Sydney" and "Melbourne" only! However if I have a consolidation "Australia" which also contains Brisbane and Perth and Canberra. How can I assign in the rule to also allow access to "Australia" without it giving the consolidated figures including these other offices? When they select Autralia, it should only provide them the figures for sum of Sydney and Melbourne only. Its almost like I need an N level rule that instead of simply providing access READ or NONE to the consolidation it needs to filter based on each of the elements within the consolidation. Any ideas how this can be achieved?

Thanks,
Damien

[tomok]

Keep in mind when it comes to security, just like with string values in cubes (well, actually because the security cubes contain strings), there is no additive feature in cells. In other words, READ access to all the children of a node does not mean READ access to the node istself. Each node in the hierarchy stands alone. You have a number of rule statements that may help you get what you want. Between the combination of ELPAR (returns the parent of node) and ELISANC (returns an indicator of whether or not one node is an ancestor of another) you should be able to write a rule to populate the cell security for the parent nodes. Good luck.

[mattgoff]

I currently have cell level security setup with rules at the N level.. However I have been asked to extend this to consolidations. However I don't see how this is possible.

For example:

If I had a rule that said allow access to user "user1" to be able to see all P&L figures for sales office "Sydney" and "Melbourne" only! However if I have a consolidation "Australia" which also contains Brisbane and Perth and Canberra. How can I assign in the rule to also allow access to "Australia" without it giving the consolidated figures including these other offices? When they select Autralia, it should only provide them the figures for sum of Sydney and Melbourne only. Its almost like I need an N level rule that instead of simply providing access READ or NONE to the consolidation it needs to filter based on each of the elements within the consolidation. Any ideas how this can be achieved?n

First, it sounds like you're using element security, not cell security. That's a good thing because cell security will really kill your performance.

You're right that by providing access to Sydney, Melbourne, and Australia, you're going to be giving access to more information than you want since Australia will contain the sum of all child elements. The only way around this is to add additional consolidation levels grouped by your security needs. E.g.

Australia
--Region 1
----Melbourne
----Sydney
--Region 2
----Brisbane
----Canberra
----Perth

Matt

[lotsaram]

Hi Guys,

I currently have cell level security setup with rules at the N level.. However I have been asked to extend this to consolidations. However I don't see how this is possible.

For example:

If I had a rule that said allow access to user "user1" to be able to see all P&L figures for sales office "Sydney" and "Melbourne" only! However if I have a consolidation "Australia" which also contains Brisbane and Perth and Canberra. How can I assign in the rule to also allow access to "Australia" without it giving the consolidated figures including these other offices? When they select Autralia, it should only provide them the figures for sum of Sydney and Melbourne only. Its almost like I need an N level rule that instead of simply providing access READ or NONE to the consolidation it needs to filter based on each of the elements within the consolidation. Any ideas how this can be achieved?

Thanks,
Damien

Hi Damien - there are OLAP tools out there where the security model and data model are tightly linked such that if as in your example a user only had access to Melbourne and Sydney only then the subtotal for Australia would simply be Melbourne + Sydney. However, TM1 is not one of these products! In TM1 the data model and the security model are distinct and independent concepts. The data model is hierarchical / tree based and will respect the relationships modelled in the dimensions. The security model being essentially string based is flat. On the whole I think this approach makes more sense, after all Australia is Australia and not Melbourne + Sydney, in TM1 it's possible to give access to Australia, Sydney and Melbourne ONLY and still see the correctly calculated sum-totals for Australia. This makes a lot of sense for reporting on % contribution of total measures (otherwise they would artificially be 100% in this instance ...)

However specific instances do crop up where the desired functionality is for subtotals to be respectful of security. In these cases you either 1/ inform the requester that TM1 simply doesn't do that or 2/ you need to do something similar to Matt's suggestion and implement a multi-hierarchy approach on the secured dimension. For element security on 1 or 2 dimensions only this isn't so bad but it could/would get unworkable for an overly complex security model.

[damientaylorcreata]

Thanks guys for all of your comments.. Yes I agree.. If the consolidation says Australia, it should give the total figures for Australia and not just a subset.. I had the wrong mindset on this.. I have decided to simply create the appropriate subsets and apply the appropriate security to those subsets instead. It just means that the users will need to be aware that that they need to choose the appropriate subsets in order to view figures.

Really appreciate the advice!.

Damien

[tomok]

I have decided to simply create the appropriate subsets and apply the appropriate security to those subsets instead. It just means that the users will need to be aware that that they need to choose the appropriate subsets in order to view figures.

Just making sure you understand you can't apply security to subsets, only members of the subset.