Cell level security and subset driven consolidations

[Steve Rowe]

My users are very keen on using subset driven consolidations as a means of doing ad-hoc consolidations. They like to be able to create a private subset called My Temp Total, stick a random collection of elements in the subset and then reference My Temp Total in a spreadsheet with a DBR. Personally I don't like this functionality and I think consolidations should always go in a dimension.

Anyway, I've recently added rule driven cell level security to the system and this has broken the using subsets as sub-totals functionality. The system is now saying they don't have access to the consolidation. Since My Temp Total does not exist in the dimension when the cell reference is queried from excel the users are getting #N/A back.

Anyone know a way to fix this?

Cheers,

[wissew]

Sorry, there is no fix. I found this unsupported functionality (hidden feature) a few years ago when we had subsets with the same name as consolidated elements. Each time the sheet was callect a diferent result was returned. We urge our users to stay away from this back door for that reason and the same one you are discovering.

[Steve Rowe]

Hi wissew,
Agreed it's a horrid piece of functionality, I'd like to see it dropped too. From a users point of view though in can see it's attraction since they feel like they have some control.

It's a shame it's not properly integrated with other pieces of functionality. I guess I'll raise a bug report, not that I'll be holding my breath for a fix..
Cheers,

[wissew]

Steve,
Your right in not holding your breath. I brought this up in the old Applix days to the enginering group at a CAB meeting. In the old days I don't think they would address it, but wiht IBM????

Wes

[paulsimon]

Wes

I haven't tried it, but do you get the same issue if you use the Roll Up button rather than inserting a subset?

Could you get around it with a naming convention, eg if the subset name starts with their name could you arrange it for the cell level security rule to give access to that user, assuming that you can derive the security group from their user name? Probably a bit of a long shot since I am assuming that the subset must be a private subset, but it might be worth a try.

For adhocs the RollUp button in the subset editor works fine. However, if they require something more permanent, to maintain some central control, ideally they should be asking you to add the consolidation permanently to the dimension.

You could also consider giving them the ability to run a TI process that would add new consolidations to the dimension. We use a structure that looks like this

All Depts
Dept A
Dept B
Dept C
zAltHier-Depts weight 0
Adhoc Dept Consol 1
Adhoc Dept Consol 2
etc

By adding all the little Adhoc Consolidations under the zAltHier-Depts consolidation, we can be sure that they don't affect the master hierarchy.

You could incorporate this in to the TI Process to which you give them READ access. You could then make this process add their consolidation, under the zAltHier, again with a zero weight, possibly prefixed by their user name for traceability, so that you know who to ask when you want to delete it. The back end of the TI Process could put the appropriate entries in to the cell security to give them access to what they have just created.

The consolidation could be defined as a parent-child hierarchy, in a CSV file or a text cube. Alternatively if they just want a simple element roll up with no internal hierarchy, then they could create a subset of what they wanted, and the TI process would read that and create the consolidation for them. This could then be similar to their current process.

If you are doing this on 9.1 be sure to implement concurrency protection!

Regards


Paul Simon