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.

Calculation Manager, BSO Planning, and ASO Planning combine for an awesome ASO Essbase procedural calculation hack -- Part 3

Introduction

This is the third and final installment of a three part series on ASO calculations, and specifically, ASO Planning calculations.  Thus far I’ve showed how to use the @CalcMgrExecuteEncryptMaxLFile via Calculation Manager which is pretty cool, and then how to make ASO procedural calculations in MaxL fast, fast, fast.  That’s all well and good, but how does that relate to ASO Planning?

I’m awfully glad you asked that, because these two hacks combine in ASO Planning to create ASO Planning procedural calculations that are both unbelievably fast and slick.  Read on, and all will be revealed.

The path not taken

Before I go any further, you are likely thinking, (Are you?  Really?  Really?  If so, you’re just as sad as I.  We both should seek help.) ‘arf a mo’, Cameron, why wouldn’t you use the ASO procedural calculation functionality in Calculation Manager?  Why indeed?

It isn’t as though ASO Calc Mgr procedural calculations aren’t available in ASO Planning 11.1.2.3.500 – they are.

But what is also there is a bug, and I have to say quite a reasonable one.  I like to think of myself as the kind of person that can break anything, if I try long enough.

A short review

The essence of fast procedural calculations in ASO Essbase is (or would be) to use a NONEMPTY modifier in the calc script.  Unfortunately, at this time that is not available although I understand it is somewhere on the product enhancement list.  What my prior post explained in great detail was the hack Joe Watkins came up with to use the ASO procedural allocation grammar to copy the results of a member formula to a stored member.  That member formula (dynamic, and in the case of currency conversion, only valid at level zero) can use the NONEMPTYTUPLE keyword to make Essbase only consider existing data and in turn it moves it to a stored member.

The next few paragraphs are a rip-and-read from that post but it’s short, explains everything, and I am too lazy to paraphrase all of it.

Additional member

In the Analytic dimension of my Planning app, I created a calculate-only member called MTD USA.  It contains the member formula to calculate fx conversion.

MTD USA’s member formula

Note the NONEMPTYTUPLE command that makes the member formula only address non empty data.

The CASE statement is a MDX version of the BSO currency conversion calc script.

Execute allocation

It’s all pretty simple from here on, thanks to Joe.  All I need to do is kick off an execute allocation in MaxL, set up my pov aka my FIX statement, identify the source (Local) and target (USD).  By not defining a spread range other than USD, Essbase copies everything from MTD USA in Local to MTD in USD.

Did you see the $5, $6, and $7 in the code?  If it’s MaxL, it can be driven through parameter variables.  

Got it?  MTD member formula with NONEMPTYTUPLE + ASO procedural allocation that allocates 100% of that dynamic formula member to a stored member equals fast, fast, fast.

So what didn’t work?

I know that the Calc Mgr team is quite proactive and I suspect that this bug will be fixed soon, but in the meantime, and because this is a Most Excellent Hack with lots of possibilities outside of Planning, I’ll show how to get round it.

Specifically, what went KABOOM?

Oracle never thought anyone would allocate 100% of a level zero member to another.  And I can hardly blame them for thinking it.

Here’s the relevant screenshot in Calc Mgr.  It (again, quite reasonably) assumes that when you allocate a data value, you do it from an upper level member all the way down to the bottom.  And that is the normal way to do an allocation, except the fast ASO procedural calc hack doesn’t do that – it allocates a level zero member to a level zero member.  And that doesn’t work.

How I solved this

I found this defect as I was writing the joint presentation I gave with Tim German for Kscope14 and I wasn’t exactly doing it months before conference.  I was stuck.

But I remembered seeing the @CalcMgr functions back in Essbase 11.1.2.2.  What if I could write a BSO Calc Mgr rule and drive an ASO procedural calc via MaxL?  

And it turns out that in fact there are a lot of ways to run a MaxL script from BSO:

• @CalcMgrExecuteEncryptMaxLFile (privateKey, maxlFileName, arguments, asynchronous)
• @CalcMgrExecuteMaxLEnScript (privateKey, maxlScripts, arguments, asynchronous)
• @CalcMgrExecuteMaxLFile (user, password, maxlFileName, arguments, asynchronous)
• @CalcMgrExecuteMaxLFile (user, password, maxlFileName, arguments)
• @CalcMgrExecuteMaxLScript (user, password, maxlScripts, arguments, asynchronous)
• @CalcMgrExecuteMaxLScript (user, password, maxlScript, arguments)
• RUNJAVA

And it gets better

Once I realized this, it hit me that I could likely drive it off of ASO Planning forms and pass the Page, POV, and even the User Variable values on save into a BSO Calc Mgr rule and from there into a MaxL script that runs the allocation.  OMG, Essbase ASO procedural calc nirvana could ensue.  Or the end of the world.  If igniting the atmosphere side bets are good enough for Manhattan Project physicists during atom bomb tests, surely giving this a whack seems worthwhile.

The short story is that all of this somewhat amazingly works, and works quite well.  I’ll cover the straightforward setup and application of this and then go into some of the more interesting possibilities.

Doing it @CalcMgrExecuteEncryptMaxLFile style

You will remember from the first post that it is very important, if you only mean to run the ASO procedural calc once, to limit the scope of @CalcMgrExecuteMaxLFile to one and only one block.  And oh yes, that block must exist for this to work.  Here’s the code:

See part one for all this approach’s requirements beyond blocks.  You will note that this BSO script does not have any Calc Mgr variables but I could have easily used them.

RUNJAVA, RUNJAVA, RUN RUN RUN

Again, see part one for all of the rules.  Note that the FIX and the existing block requirements do not apply.  But what I want you to focus on the {varYear}, {varBRUVAccount}, {varProduct}, and {varPostCode} Calc Mgr variables.

Here are the variables as defined in Calc Mgr.  NB – These are Calc Mgr variables passed from an ASO to a BSO Calc Mgr rule.  Coolness.  And awesomeness.  And a great hack.

Here’s the (again, BSO)  rule associated to the (again, ASO) form in Planning.  Note the Use Members on Form tag:

ASO procedural calc

Here’s the MaxL code containing the ASO allocation script:

And the output from that fx conversion.  Note how ASO Planning form values got passed to Calc Mgr variables and then to MaxL to run the ASO procedural calculation (see the pretty pastel highlight colors):

And now the demo

And here’s a very short movie showing it executing.  Please excuse the editing (with clock) at the end as I was trying to spare you all the trauma of me searching for the calculation time in the Essbase application log.  In any case, the time logged to MaxL (0.027 seconds) shows up in the application log as well.

Numbers don’t lie

Finally, you know from part two of this series how fast this can be.  The times you are seeing below are slower than what I demonstrated because they represent full database size (my database is just a fraction of the full dataset because of disk space constraints – trust me, these numbers are real):

Process	BSO	ASO	X Fast
Allocate	106	3	35
Fx	400	1.2	333
Aggregate	1,772	N/A	N/A
Total	2,278	4.2	542

Using this technique, the ASO fx is over 300 times as fast as the equivalent BSO outline and data.  A little slice of Essbase performance heaven, isn’t it?

Conclusion, or is it?

A combination of the Calc Mgr CDF that is in every copy of Planning (and Essbase, for that matter), the tried and true POV/page/and now row and column set passing to Calc Mgr, and a little creative ASO Essbase procedural calculations gives the Planning community access to an amazing amount of power and functionality.  

Cool, eh?  But this technique can be taken quite a bit further.

Where this starts getting really interesting

The demo you see above is from a Planning application that has BSO and ASO plan types that mirror one another.  As such, the dimensionality in the BSO application mostly matches ASO.  Is this required?  Absolutely not.

In fact, all that I need to run ASO procedural calculations in Planning is a BSO plan type with exactly one block of data (for @CalcMgrExecuteEncryptMaxLFile) or one that is completely empty (for RUNJAVA) and I can then address any ASO Planning plan type, even ones across multiple Planning applications or even servers.  The Calc Mgr functions call MaxL and MaxL can address any Essbase database to which it is provisioned whether that be that a Planning plan type, an ASO Essbase database, a BSO Essbase database, some combination of the above, etc., etc., etc.  

Calc Mgr itself isn’t even required (or even Planning) if you wish – you could use this all in a pure Essbase database and use command line substitution variables to drive scope, or just hard code it all.  You can go absolutely wild, relatively speaking, with this approach and do just about anything with it.  It is a very powerful technique and one that I hope will be exploited.

I find this all oddly stimulating.  But I’m weird.

Now the real conclusion and a question for you

This is one of my longer posts – almost 30 pages in Word which equates to approximately 6,500 words in total.  Does it make sense to write multiple part posts like this or would the EPM community be better served with me trying to write things like this as a white paper?  Write care of this blog or just email me.

Be seeing you.