Planning security: the wrong way and right way – which way is yours?

Introduction

Yes, again, not an Essbase subject, or more precisely, not strictly an Essbase subject.

Perhaps I ought to rename this blog to, “Cameron’s Blog For Planning Hackers,” as I seem to spend a lot of time talking about Planning. So, yes, this is a big hint that Planning is on the menu.

The subject of how to best define security in a Shared Services/Planning/Essbase world keeps on coming up with clients and was suggested to me by my much missed, ex-colleague (sob) Josie Manzano. If Ms. Josie suggests something, who am I to argue? It seems like the plurality of opinion is for it, and besides, I can tell my clients to, “Read my blog,” instead of having a conversation about security and hence drive traffic to this site. :)

How not to assign security

I can think of a few ways, all bad...

Assign security directly to a user name

Pro

• It works.

Cons

• Maintenance intensive – what happens when the user gets promoted (surely all who use Oracle EMP get promotions), quits, gets hit by a SEPTA (Southeastern Pennsylvania Torture Transit Authority) bus, etc.?

Assign security to groups without inheritance

Pro

• At least you’re not assigning to usernames.

Con

• Still maintenance-intensive, and you’ll have just as much many manual assignments. What were you thinking? Oh wait, you’re a Victim of Planning 4.x and before, so it isn’t your fault. Remember, all you have to lose are your chains.

Assign security to groups with inheritance

Pro

• Ah, you’ve reached Oracle EPM security nirvana.

• Low maintenance through inherited security.

• Inheritance design allows atomic security assignments.

Cons

• More than four levels of inheritance can bring poor performance, so don’t.

• You’ve had to read my drivel (346 words, thus far) to get to this point.

How does it all work?

1. Assign an upper level group in Shared Services to your Planning application and provision the group to access the application. In Planning; this typically means the Planners role.
   
2. Create one or more subgroups that are members of the overall group you just created. Typically, this is used to assign access to the Planning Plan Type. Note that the subgroups are provisioned to the application through security inheritance – there is no need to provision access to the application at this level.
3. Create yet a third (and in this example, the final) level of groups. There could potentially be many groups here (you may define many, it could be 20, it could be 50, dependent on how focused you define security). This third level is a member of the second group. Again, no need to assign provisioning to these groups.
4. Assign Planners to the third generation groups.
   

Three layers of groups seems a bit much just to provision access to an application. Surely there must be another reason to create this many layers, yes?

Indeed there is – now these multiple security groups are going to be applied to different slices of a Planning (or Essbase) application.

A mythical Planning application

The Planning application TotPlan has two Plan Types (Planning uses strange names for concepts that have been around since the year dot in Essbase) that correspond to databases in Essbase: Consol and Workforce.

The planners in these two Plan Types are mostly mutually exclusive; there are some users active in both, just to be difficult.

In this mythical application there are seven dimensions (note the modified hourglass order with the non-aggregating sparse dimensions at the bottom -- even examples should be optimized):

1. Account
2. Period
3. Entity
4. Employee
5. Year
6. Scenario
7. Version

Please ignore this message

Some dimensions don’t have security, so we can ignore them: Year and Period (Period has access to open and closed months, but this isn't done through security, so the tile of King Pedant remains safely on my brow).

But start paying attention here

Let’s look at the two simplest required dimensions: Scenario and Version.

Within Scenario, Actual will be read-only, Budget will be read/write.

Scenario

To give all of your provisioned planners (remember the first group and and inheritance) access to Actual and Budget, assign the topmost group to Actual, and give it read access. Do the same with Budget but make the access write.

Version

In Version, there are two members: Final and Working. Final gets the read-only setting and Working is set to write.  Again, use the topmost, first generation group to assign access.

That’s it, you’ll never have to worry about base dimensions again.  Note that security was done at a high level group (the highest, really) as access is the same for all Planners.

The big guns

Two required dimensions remain: Account and Entity

Chop the Accounts

When I build a Planning application with more than one Plan Type, I like to create upper level Account parents that segregate by Plan Type. This makes security and dimension builds as straightforward as possible. Yes, this does require extra dynamic calcs in the target (really, it’s the master) Plan Type to pull the XREF’d data from the source Plan Type(s), but I think it’s a small performance penalty to pay for clarity.  I reserve the right to bin the above approach if it doesn’t work for a particular application, dear client(s), so please don’t consider the above set in stone.

Mythical application example -- Account

To do this, name and order the Accounts like the below to split security by Plan Type:

Accounts

|--Wrkforce Accounts

|--Consol Accounts

NB -- Wrkforce, the source of employee expenses, is ordered before target Income Plan Type so that there are no forward dynamic calcs.

I can assign second generation groups to both Plan-Type-by-Account-parent assuming that all Workforce planners can see all Workfoce accounts and the same holds true for the Consol Plan Type.  Do you see the matchup between the upper level Accounts and the groups?
 

How do exceptions get handled?

More restrictive 
If a Workforce Planner did not have access to a single WorkForce Account, or range of accounts, apply his third generation group and assign None access.  This assignment on top of the second generation group Write access works because lower level, more restrictive, security will take precedence.

Access to both Plan Types 

If a Planner spanned Workforce Accounts and Consol Accounts, make his third generation group a member of both the Consol and the WorkForce second level groups. No need to create a special group just for that purpose – they grow like Topsy and quickly veer out of control.

Mythical application example -- Entity

Entity is the last of the required dimensions and it too must have security. Remembering that the default access is None, this is where the third level groups come into play as there (likely) is no general access to cost centers/projects/accounting units/etc.  Assign read or write access to Entity parents or, less optimally, to individual Entity members by third generation group.

Mythical application example -- Employee
This leaves Employee, a custom dimension in Planning-speak. Is this dimension to have security?  Probably. Can you get away with the same groups as used in Entity?  Almost certainly, as Employee dimensions have a habit of mimicking Entity dimension hierarchies.

The other alternative is to not turn on security in Employee, and let the Entity dimension access drive security. This is simpler and my recommendation.

Suggested naming conventions

Mnemonic names
Name the groups something that make a little sense.  You don't have to use my naming convention, but I've not found anything that makes more sense.

Names by generation

• Top level group that provisions access to the application and is used in the Scenario and Version dimensions: appname.
• Second level group for Plan Type access via the Account dimension: appname.PTname.
• Third level group for Entity access: appname.PTname.Entityname.
• BTW, the Entity in question would (could, but ain’t necessarily so) likely not be a level 0 Entity but some upper level parent. Take it easy on yourself and just tell your business owner it can’t be done. You’ll be thanked later when security management doesn’t take over the administrator’s life.

Real (as real as a sample in a blog) world example

Let us examine planner John Q. Public. A friendly sort of chap, with few vices (chiefly an excessive predilection for coffee), and several good points (he likes this brand), John is a planner in Entity 12345 in the Income Plan Type. 12345 is (yes, I said it was a bad idea, but it’s my example, so I do as I please) a level zero Entity.

How, oh how, does this mythical man of government forms get his security? (For non-North Americans – Canada, please note your inclusion by this Yankee because of the awesomeness of Tim Hortons, but actually I have no idea if John Q. Public means anything in the land of Timbits – John Q. Public is sort of the name of the man in the street.)

Steps in Shared Services

1. Create a Shared Services group (native, because my laptop doesn’t have MSAD) named PlnLCM.
2. Create two Shared Services groups: PlnLCM.Consol and PlnLCM.Workforce. Make both groups members of PlnLCM.Consol. NB – In the absence of any security overrides at this level, these groups inherit PlnLCM’s security.
3. Create a Shared Services group named PlnLCM.Consol.12345. Make this group a member of PlnLCM.Consol.
4. Make John Q. Public a user member of PlnLCM.Consol.12345.

Steps in Planning

As application administrator:

1. Migrate identities to ensure that the new groups are pulled from Shared Services if that hasn’t automatically happened already.
2. Perform dimension security as described above (PlnLCM.Consol is assigned to the parent Account "Consol Accounts", etc.).
3. Log out of Planning, and log in as John. Remember, this is a test id you created to prove that it works; real world users will be authenticated through LDAP or MSAD, and you are unlikely to know their password.
4. Note his restricted access – this is the payoff.

It gets better
When Jane Doe is to be added to Planning, you need only create an additional third level Shared Services group, make it a member of PlnLCM.Consol (if she is a Finance Planner), and assign her user name to PlnLCM.Consol.Entityname. As Jane (and John) are inexorably climbing the ladder of corproate success because of their Planning prowess (it could happen, ya just gotta believe), you, humble Planning administrator, need only move their usernames out of the third generation groups and move in new, soon-to-be-similarly-lucky Planners who are kicking down the door to ride the Planning elevator of professional success.

Wrapping it up 
As with most things in life, a little (and really, in the Planning world, the above has to fall into the e category of little) pain and planning deliver big results.

All of the above techniques would true for Essbase (had to bring it ‘round to the name of this blog sooner or later) and filters, except that instead of the Plan Types you will deal with databases.

So, not exactly a hack, and unfortunately, not exactly brief, but definitely a technique worth pursuing.  So maybe that kind of, sort of is a hack.

See you next time.