Tuesday, 10 September 2013

Mysterious Custom Functions - Part 1

Following the success of Capex and Workforce Planning modules, Oracle started investing its EPM team's time on pre-built modules more and more. It was and still is a sensible move if you consider the fact that general practice for budgeting and planning of workforce and capital expenditure don't vary much. Assets get purchased and depreciate, people get paid and go on to matternity leave, right? Right? For some, yes, for some others... well, lets leave this discussion to another post.

With the latest couple of releases we now have two more pre-built modules: Public Sector Planning and Budgeting and Projects Financial Planning. And if you did go ahead and upgraded or installed the latest incarnation of Oracle EPM software, and if you are a little into calc script editor in EAS Console, you might have noticed the addition of some custom functions. Have you not? Ok, just open a calc script editor in EAS Console under a Planning application Essbase database, scroll down to User Defined Functions in categorical list of functions. Expand it and scroll down. There you go! We are talking about this mysterious list of functions that start with @hsp... Apparently the developers at Oracle were a little nostalgic when they named these functions. Does HSP not stand for "Hyperion Solutions Planning" i.e. Planning's pre-Oracle name?

These functions apparently surfaced to contribute in the new modules and most notably to PSPB. However, they get registered to any planning application in Essbase regardless to whether you enable these modules or not.
Here, kitty kitty!
Hyperion veterans started fiddling around with these functions as soon as they see them. But there was a problem: There was no documentation... at all. So your chances were to try and find out what they were for. In the upcoming series of posts, I will try to uncover the mist upon these functions... well, as much as I can. Some of these functions are still really hard nuts to crack to me. Your contributions are more than welcome here. If you managed to unveil any of these functions, please post your findings in the comments section below.

Off we go! The first function we will look into is @HspNthMember. Name suggests a lot about the usage of this function. It returns the nth value in a given list of members. When you double click on the function in calc script editor, it indicates that it requires you to define a range and an position index number ( @HspNthMbr (memberRange, PosIndex) )
By definition it's similar to @ShiftSibling. However, with @ShiftSibling you are dependent on the member from which the index will iterate. With @HspNthMember, you are not asked an origin member, instead, you define a range and position index and it returns you the member in the designated position.

It's not meant to replace or supersede @ShiftSibling of course, finding a member in an outline that is n member away from an origin member is, in many cases essential. A simple practical example could be increasing a prospective project's priority by lets say 2 levels. You can move the project up to current priority +2 using @ShiftSibling very easily.

However, you start facing difficulties if the range you'd want to work is not in a flat hierarchy. @ShiftSibling works only with... it's siblings (surprising eh?) and shared hierarchies are not recognized (sigh). Ok, what if the hierarchy in your Priorities dimension is like below:
An hierarchy which makes @ShiftSibling cough
Now, how would you move a project from MP1 to HP3 based on priority shift that is input through a smart list (i.e. via an integer value). If you were to use @ShiftSibling you had to code exceptions for the first member under every parent. This is exactly when @HspNthMember come into play. Here, you can define a flat range on runtime and return the nth member. Here is how:
@HspNthMember(@Relative("Priorities",0),"sl_Priority") (note that sl_priority is the member that holds the smart list value)
One line! Now that's somehing, isn't it?!

No comments:

Post a Comment