Dynamic Subset Defined by Element Security?

[mitch23]

Try as I may, I have not been able to figure out how to create a dynamic subset based on element security. I need to define a subset limited to elements for which the current user has Write permissions. Is this possible?

FWIW - This all has to be done through Excel VBA such as the elements contained in such dynamic subset get returned to Excel, but that just context and I asume not necessarily relevant to the objective of creating such a subset in the first place (through whatever means).

Thanks in advance for any advice.

[declanr]

This is one of the many things in tm1 where it could be done via numerous methods.

My personal recommendation as it avoids too much extra TI etc is actually something that previously I didn't like much since it involves adding an extra Dimension that doesn't exist within any cube. I tended to not like putting anything in my models that "wasn't used" or at least not used in the traditional sense... anyway a few requirements down the line and I've seen that such things can sometimes be very helpful.

Step 1 - Create a dimension that is identical to your original dim. (this will need to be kept identical to the original so make sure that when elements are added/removed from the orginal they also go into the "mimic" dim.

Step 2 - Create a security cube and security rule on the "mimic" dim.
The rule will just check its corresponding "original" element and if that states "WRITE" it will state "WRITE" (or "READ" doesn't matter really) but if the
original states anything other than "WRITE" then ignore it.

Step 3 - Have the "mimic" dimension referenced in your spreadsheets and the user will only be able to see the elements that you have allowed them to. If they can't write... they can't see. And as the element names are identical to the original dim they can still be used in cube references.


Again this is most certainly not the only method but it would require little/no upkeep. Just make sure that when tidying up the model you don't accidentally delete the dim as its not used in any cubes.

[mitch23]

Thank you. One thing I'm not clear on is how this works with the reality that the current user's permissions is defined by there membership in the collection of groups they are members of so I am not sure how to determine the permission of a particular user to elements?

[rmackenzie]

One thing I'm not clear on is how this works with the reality that the current user's permissions is defined by there membership in the collection of groups they are members of so I am not sure how to determine the permission of a particular user to elements?

I don't think that you are going to achieve building the subset you want using MDX in the way it is implemented for TM1 and dynamic subsets. In TI, you could write a nested loop which iterates over the groups that a user is a member of, and then for each group, the elements to which it has write permission for a particular dimension. Remember you want the distinct list, so you need to keep checking to see if an element already exists in the subset. Replicating this in a dynamic subset will not be easy, if it is at all possible, as you need to reference two different cubes in the expression (}CilentGroups and }ElementSecurity_YOUR_DIM).

This all has to be done through Excel VBA such as the elements contained in such dynamic subset get returned to Excel

Is the security changing to fast that you need to re-evaluate this subset during the day? Why not have TI (per the above suggestion) build static subsets over-night and let Excel read in these subsets when people are using your report?