Stupid Programming Tricks No. 27 -- Expanded dimension security in Planning

Expanding your horizons while locking them down at the same time

I was recently on a Planning engagement where planners were eschewing Planning forms for the Awesomeness That Is Essbase.  Note that this is an on-premises site as a direct Essbase connection isn’t possible in PBCS.  Why is the connection type important?  ‘Cos the open and closed months that Planning defines through the Scenario dimension do not resolve to Essbase filters.  This is the advantage that Planning brings to the table with its Scenario dimension and just isn’t doable in Essbase.  

Or so I thought.  Let’s have a look-see at how this works.

Scenario dimension

Since Planning’s Year Dot, the Scenario dimension has been where open and closed months by Year and Period are defined:

Planning then closes off the closed (naturally) months and leaves open the open (are you seeing a pattern here?) months:

All very nice and just what is wanted if one is in the last forecast of the year.

NB – Although this example only applies to the current year, I should note that Planning also applies this open/closed rule across years, i.e., if the start year/period is FY15/Oct thorough FY16/Dec, only the first nine months of FY15 are closed – October through December are open in FY15 and all 12 months are open in FY16.  More on this anon.

Again, all of this is well known and I am exercising my well-known penchant for stating the obvious.

But what happens to Period security (security isn’t exactly the right word, but Essbase filter security is an analogous concept) this is applied to Essbase?  

Oh dear, oh dear, oh dear

Here’s what it looks like in Essbase with a non-administrator Planner.  Note that the months are wide open .  This is a real problem in Estimate or Forecast or whatever current year mix of Actual and Plan happens to be.  What happens when the data is sent to the closed-by-Planning but not-closed-by-Essbase months?

Put in numbers and fire away.  But watch out where you aim…

Ready, shoot, aim

Unpossible!  

Do you see how all of 2015’s months are open for input?  If January through September is supposed to be closed, and the Essbase user can enter data into the closed months, planners could inadvertently change Actuals and in fact, given the vagaries and foibles of humans, almost certainly will.   This is generally accepted as a Bad Thing.

So what’s the solution?

I thought there was none, and told the client the same.  And then my BFF Jessica Cordova disabused me of that notion.  Imagine my chagrin (and, I’ll wager the delight on the part of you not-so-Gentle-Readers) when I tested it and she was right, right, right.  Bummer.  And awesome, all at the same time.

How did I get this wrong?  I thought that only Account, Scenario, Version, and Entity were the Must Have Security dimensions.  Why?  

Is this a RTM failure?  RTFM failure?  Ultimate fail?  You decide.

From the 9.3.1 Planning administrator’s guide, I read (and given how long I’ve been doing this, the 2.1 Planning admin guide or whatever it was called back then):

User-defined dimensions. Assign access permissions to members by selecting the dimension property Apply Security. If you omit setting Apply Security, all users can access the dimension's members. By default, the Account, Entity, Scenario, and Version dimensions are enabled for access permissions.

I’ve always understood that to mean (and hey, maybe I misunderstood this since 2001 but I don’t think so):  security on Account, Entity, Scenario, and Version are required; custom dimensions are the developer’s choice.

Note that Year and Period – both required dimensions – are not on that list, as in both dimensions only receive security through the Scenario dimension opening or closing a combination of both dimensions.

That wording, and I believe that meaning, hold true through Planning 11.1.1.4 and 11.1.2.1.  

And then it changed.

Read what the 11.1.2.2 administrator guide has to say (emphasis added by yr. obt. svt.):

Dimensions, including user-defined dimensions. Assign access permissions to members by selecting the dimension property Apply Security. If you omit or clear the Apply Security setting, all users can access the dimension's members. By default, the Account, Entity, Scenario, Version, Year, Period, and Currency dimensions are enabled for access permissions.

Whaaaaat?  They changed it (and yeah, duh, obviously they did or Jessica wouldn’t have told me so) and, as far as I can tell, didn’t bother to put it anywhere other than these four little words.  And yes, I read the Read Me’s and the What’s New documents as well as checking in the Cumulative Feature Overview tool.  Nothing.

Good grief, this is like figuring out what a certain politician meant when he said, “It depends on what the meaning of the word 'is' is. If the--if he--if 'is' means is and never has been, that is not--that is one thing.”  Yup, I went there.  I only need to bring sex and religion to this blog and the world will explode upon me.  Can we just agree that lawyers parsing words are…lawyers?  And that maybe they work at Big Red?  Or is it more likely that I simply failed to RTFM and thus pay the price that the ignorant always pay.  Probably.  Definitely, even.  Such is life.

What did it look like?  Thanks to Robin Banks I’m able to bring you evidence in the now hard to find release of 11.1.2.1.  Security is nowhere to be seen, as it has been Since the Beginning.  Thanks, Robin old chum, for proving that I’m not insane, or at least not insane in this particular area.  

Bugger, but there it is 11.1.2.2.  My ego is destroyed.

Driving the spike home in 11.1.2.3.500.

Another hammer blow in 11.1.2.4.

Bugger.  

I think we can safely agree it’s here to stay.

And now the useful bit

Once applied, security at the individual Period level is possible:

And that resolves to an Essbase filter for those Planners with Essbase Write Access provisioning.

Note that with the None filter line, Planning specifically writes read access to all of the filters (security is defined at the quarter level although it could have been done at the month) and then just write to the inclusive children of Q4.

And what does it do?

Let’s create a send sheet with the 12 months of the year and three years.  Can you guess where this is going?

I would write Unpossible! except of course this is exactly what one would expect.  The last quarter is open and there is no mention of years.  That is not exactly what one might hope for.

Can we do this to Year?

If one supposedly impossible security assignment is possible, why not another?  What about Year?  That’s yet another period that in theory cannot have direct member security assignments – it’s supposed to be handled by the Scenario dimension.

Yup, it’s there.  The below shot is 11.2.3.500:

And here it is in 11.1.2.4:

An alternate reality

There are two choices in the Simplified Interface.  The “normal” approach is to use Navigator and then navigate (ahem) to the Dimensions editor as shown above.  There is another way, one that I don’t particularly understand givem the Dimension editor approach, that allows the same assignment.  Weird but there it is.  And yes, you’re welcome as I live for this sort of useless information.

Go to Console and click on Dimensions.

And then Year.

Wowsers, there it is.  Apply Security for Years.  Why can this be done two different ways?  I’m not sure.  Hopefully the digression has been fun.

Moving right along

Now that security has been enabled, let’s put it to the test.

Assign the Year(s) (I am showing FY14 but FY14, FY16, and FY17 read, FY15 write, FY13 None).

Perform a security refresh and look at the filters:

Note that only FY15 can be written to and in fact FY13 is set to #NoAccess.  That’s different from security even in Planning within forms.  Tell me how you’d turn off read access using the Scenario dimension.  Exactly.  It simply can’t be done that way although there is a hybrid approach below.

And now try to send in All The Wrong Places:

And…

It works!  

So what won’t this do?

This all works in a single year as shown above.  But what about crossing years and periods the way the Planning Scenario dimension allows so that the last quarter of FY15 is open as is the first quarter of FY16?  No, it cannot as only the combination of FY15 (single assignment to Write) and Q4 (single assignment to Period) are defined in Planning.

If only Planning allowed what are essentially AND coniditons.  Would you be shocked that Essbase can do just that?  No, me neither.

If Planning allowed an AND condition (the world+dog has been screaming for this for, oh, 14+ years – more on this in a moment) the filter might look like this:

With a Write specification on the second line for FY16, submitting this:

Would end up like this:

Huzzah!  Wouldn’t that be nice?  

So what about Valid Combinations?

At least of the writing of this blog post, 15 October 2015, Valid Combinations are not:

1. Available for on-premises Planning (you are looking at my VM, so yeah, definitely on-premises)
2. Not available in Essbase connections because they are not currently available on PBCS and that product only supports Planning connections.  As a side note, if you have Planning-only connections, the Scenario dimension member setting (mostly, you’d still have to use security to turn off FY13 but now you know how) handles all of this, so why bother?
3. It is unknown, at least to yr. obt. svt. when Valid Combinations will evince themselves in on-premises Planning.
4. And even if #3 happens, no idea if VCs (Viet Cong? An aeroplane from Vickers-Armstrong (Aircraft) Ltd?   Victoria Cross?  I vote for the last.)  will ever be manifested into Essbase filters.

So basically, I have no idea.  As I have no idea in many, many, many aspects of both my professional and personal life I am not over worried by this.  

What this means for you is that if you want this kind of functionality, you’ll need to read the tables (again, not an option in PBCS) and dynamically generate the filters.  That may be something I look at in future but for the time being I leave that as an exercise for the reader.

Thanks again to Jessica and Robin for showing me and confirming that this is new-ish functionality that is actually quite useful.  I sure wish it had been documented a bit better but such is life.

Be seeing you.

Hybrid Planning security addendum

If the FY13 None security assignment is used, this form layout:

Results in this dropdown.  FY13 is nowhere to be seen.  None really means None in Planning forms.

If FY13 None is switched to Read or if Apply Security is turned off completely for the Year dimension, FY13 is now selectable.

It is a hybrid solution and a Stupid Trick all at the same time, but interesting nonetheless.

Be seeing you once again.

Stupid Planning security trick 4 of 4 -- dynamically generating Planning reporting cube filters

Just how do ASO Planning reporting cubes work?

Well, I could retype (in my own words, ‘natch, as I am no plagiarizer) the Planning administration guide but instead I will direct you to The Great and Good John Goodwin’s as-always excellent explanation right here.  

Did you read it?  All pumped up?  Ready to deploy an ASO reporting application?  Did you catch the bit that John missed?  Actually, that’s not fair, John didn’t miss anything.  Did you catch what Oracle missed?

The missing link

What’s missing is the security side of reporting.  Unless of course you are planning (oh, I slay me with my puns, but maybe no one else) on giving access to everyone, or hand crafting filters.  Neither of which are much use, or fun.

What do I mean by missing security?  Well, you have built your reporting cube, loaded it with data, scripted the whole thing but where are the filters for that supercalifragilisticexpialidocious reporting database?  The answer is:  nowhere because they don’t get created.  Oops.

And of course you have created all kinds of rules based on usernames and groups within Planning (you have been paying attention during the last three posts on Planning security, right?)  Which is all now a bit pointless as those dimensional security rules only apply to Planning because the definitions are in, wait for it, Planning.  

Whew, a bit tautological, but you get the idea – Planning security is like the Hotel California.  Or maybe the Roach Motel.  Once in, it ain’t getting out.  Except of course with the power of SQL and the queries I have given you, in fact you can get security out of Planning and apply it elsewhere.  How?  Read on, gentle reader.

A solution in two parts

Part the first

For those of you smart (or foolish or merely desperately sad) who follow my blog posts on Planning security know that with a query that figures out what rights a Planner has using both explicit and implicit, group-defined security, you can get the bones of an Essbase reporting application’s filter definition.  

Think about it – you can write a query that knows everything, and I mean everything, about dimensional (and other, but only dimensions are germane to this issue) security from a Planner perspective.  If the output from that query looks like a MaxL filter security assignment, you have the guts of a fully dynamic, programmatically derived Essbase filter.

Part the second

Once you derive the filter you have to assign it to the Essbase application/database.  I’m not going to show you how to do that in this blog because:

1. Running this MaxL from a msh nesting command keyword in your own MaxL script is really easy to do
2. If you don’t know how to do the above maybe you shouldn’t be trying this
3. The solution that I implemented is pretty specific to the client.  

High level overview of the scripting

To address the bit that is specific to the client, without of course naming the (un)lucky recipient, realize that I had no access to proper scripting and had to improvise.  Dan Pressman gave me the suggestion to use SQL*Plus as a scripting environment to generate the script.  It worked a treat – and is completely useless if you are doing this on SQL Server.  Roll your own or contact me care of this blog and we can discuss the gory details off line.

The query

A caveat

One thing to note – when I ran this in PL/SQL, I used the undocumented-but-out-there command wmconcat to give me a comma delimited filter.  You should probably use LISTAGG but the need to go over the varchar limit of 4,000 bytes and my inability at the client to write a custom function to point LISTAGG to a CLOB data type mean that wmconcat was my only option.

Interestingly (well, interesting to me), a CLOB can contain up to two gigabytes of information.  I was limited by the 32K size limit of SQL*Plus for a column.

As far as I can tell, in SQL Server the sort-of equivalent of wmconcat/LISTAGG is FOR XML PATH and its limit seems to be two gigabytes as well.  

Ah, the joys of an almost-standard.

The code

WITH

 Dimensions AS

   (

     SELECT DISTINCT

       O.OBJECT_ID,

       O.OBJECT_NAME AS "Member",

        (SELECT OB.OBJECT_NAME

       FROM HSP_OBJECT OB

       WHERE OB.OBJECT_ID = M.DIM_ID) AS "Dimension"

     FROM HSP_OBJECT O

     INNER JOIN

       HSP_MEMBER M ON M.MEMBER_ID = O.OBJECT_ID

   ),

 ObjType AS

   (

     /*

       2 = Dimension

       31 = Scenario

       32 = Account

       33 = Entity

       34 = Time Period

       35 = Version

       37 = Currency

       38 = Year

       50 = User Defined Dimension

     */

     SELECT DISTINCT

       OBJECT_TYPE AS "OBJECT_TYPE",

       TYPE_NAME AS "TYPE_NAME"

     FROM HSP_OBJECT_TYPE

     WHERE OBJECT_TYPE IN ('2', '31', '32', '33', '34', '35', '37', '38', '50')

   ),

 ObjectID AS

   (

     SELECT

       OBJECT_ID,

       OBJECT_NAME,

       OBJECT_TYPE,

       SECCLASS_ID

     FROM HSP_OBJECT

   ),

   FinalCTE AS (

   SELECT --DISTINCT

     --OT.TYPE_NAME AS "Type",

     CASE

       WHEN O_ID.OBJECT_TYPE != 50 THEN OT.TYPE_NAME

       ELSE (SELECT D."Dimension"

               FROM Dimensions D

               WHERE O_ID.OBJECT_ID = D.OBJECT_ID)

     END AS "Type",

     O_ID.OBJECT_NAME AS "Object",

     CASE

     -- Subquery to get user or group type

         (SELECT OA.OBJECT_TYPE

             FROM HSP_OBJECT OA

         WHERE OA.OBJECT_ID = AC.USER_ID)

         WHEN 5 THEN 'User'

         WHEN 6 THEN 'Group'

     END AS "Security Type",

    (SELECT OA.OBJECT_NAME

         FROM HSP_OBJECT OA

         WHERE OA.OBJECT_ID = AC.USER_ID) AS "User/Group Name",

     CASE AC.ACCESS_MODE

         WHEN 1 THEN 'Read'

         WHEN 2 THEN 'Write'

         WHEN 3 THEN 'Write'

         WHEN -1 THEN 'Deny'

     END AS "Read/Write",

     CASE AC.FLAGS

       WHEN 0 THEN '"' + O_ID.OBJECT_NAME + '"'

       WHEN 5 THEN '@CHI("' + O_ID.OBJECT_NAME + '")'

       WHEN 6 THEN '@ICHI("' + O_ID.OBJECT_NAME + '")'

       WHEN 8 THEN '@DES("' + O_ID.OBJECT_NAME + '")'

       WHEN 9 THEN '@IDES("' + O_ID.OBJECT_NAME + '")'

      END AS "Hierarchy function"      

   FROM ObjectID O_ID

   INNER JOIN

     ObjType OT ON OT.OBJECT_TYPE = O_ID.OBJECT_TYPE

   INNER JOIN HSP_ACCESS_CONTROL AC

     ON O_ID.OBJECT_ID = AC.OBJECT_ID

     ),

 -- Commented out, but this is where you might limit the scope of the dimensions by name

 EmployeeEntity AS

 (

   SELECT

   *

   FROM FinalCTE

   --WHERE

   --  ("Type" = 'Employee'

   --  OR "Type" = 'Entity'

   --  OR "Type" = 'Geography')

   --  --AND "Security Type" = 'Group'

 ),

 UsersInGroups AS

 (

   SELECT

     O1.OBJECT_NAME AS "Group",

     O2.OBJECT_NAME AS "User"

   FROM HSP_USERSINGROUP G

   INNER JOIN HSP_OBJECT O1

     ON G.GROUP_ID = O1.OBJECT_ID

   INNER JOIN HSP_OBJECT O2

     ON G.USER_ID = O2.OBJECT_ID  

 ),

 UserDefinedSecurity AS

 (

   SELECT

     *

   FROM EmployeeEntity

   WHERE "Security Type" = 'User'

 ),

 GroupDefinedSecurity AS

 (

   SELECT

       E."Type",

       E."Object",

       E."Security Type",

       U."User" AS "User/Group Name",

       E."Read/Write",

       E."Hierarchy function"

   FROM EmployeeEntity E

   INNER JOIN UsersInGroups U

     ON U."Group" = E."User/Group Name"

 ),

 UserAndGroupDefinedSecurity AS

 (

   SELECT

     *

   FROM UserDefinedSecurity  

   UNION

   SELECT * FROM GroupDefinedSecurity

 )  

SELECT DISTINCT

    'create or replace filter "appname"."dbname"."f' + U1.[User/Group Name] + '" meta_read on ' + '''' +

    STUFF((    SELECT ',' + U2."Hierarchy function"

            FROM UserAndGroupDefinedSecurity U2

            WHERE U1.[User/Group Name] = U2.[User/Group Name]

            FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')

    + '''' + ' ; '

    + 'grant filter "appname"."dbname"."f' + U1.[User/Group Name] + '" to ' + U1.[User/Group Name] + ' ; '

    AS 'DefineAndGrantFilter'

FROM UserAndGroupDefinedSecurity U1

/*  Oracle PL/SQL code

 SELECT

   ('create or replace filter "appname"."dbname"."f' || "User/Group Name" || '" meta_read on ' || ''''

   || wm_concat("Hierarchy function")

   || '''' || ' ; ') ||

   ('grant filter "appname"."dbname"."f' || "User/Group Name" || '" to ' || "User/Group Name" || ' ;') AS "DefineAndGrantFilter"

 FROM UserAndGroupDefinedSecurity

 GROUP BY "User/Group Name"

*/

The MaxL code

Write (this is the bit I am not including as it was so specific to a very narrowly defined environment) this code out to a text file, then pull it in via MaxL’s msh nesting command:

You are actually seeing two lines of MaxL code for every user:  one to create or replace the filter, the other to grant it to a specific user.

And yes, that is the inherited and explicitly set security for all four users in my little test application.  Pretty cool hack, is it not?  

So why isn’t this part of Planning?

Up to Planning 11.1.2.3, I think the answer to that question is because Oracle have no idea what you are doing with your reporting application.  The above query generates MaxL to write filters and grant them which makes perfect sense so long as your reporting application mimics the dimensionality of the BSO Planning Plan Types.  

But what happens when your reporting application goes beyond what Planning has?  How could Oracle have tied the filters to an unknown?  The answer of course is they could not, and did not, and will not going forward.  Knowing the unknowable is sort of difficult.

11.1.2.3 is a horse of a different color – with ASO Plan Types one could argue that the ASO database now generated by Planning is good for both input and reporting (I am leaving aside the fact that a lot of BSO Planning Plan Types have been used for just that since The Year Dot).  And if your ASO Planning cube is generated by Classic Planning (the only choice, btw), then it follows that the filters your Planners need will be there.  So these filters are there finally.

But for everyone else not on 11.1.2.3, the above technique makes sense and who knows – you may want to take that Planning security definition and apply it to other databases.  You now have a query that will do just that.

I hope you enjoyed the hack.  

Be seeing you.