Sunday, 15 September 2013

Mysterious Custom Functions - Part 2

Today, in our quest to discover custom functions that came along with new Planning modules, those which start with @hsp, we will see some very useful string functions. Developers at Oracle have written 5 new functions for string manipulation and boolean control. And again, names are quite self-explanatory for the functionality itself. So my short descriptions will not surprise anyone. Here they are:
  • @HspNumToString (NumericInput): Converts the given number to text. Takes member and variable as well as constant integer but who wants to pass constant integer, right?
  • @HspDateToString (NumericDateInput): Converts planning date format to date string in dd/mm/yyyy format.
  • @HspConcat (StringArray): Concatenates all the strings in a given array. No restriction of two parameters (Yay!)
  • @HspStringCompare (srcstring, trgtstr): Compares two strings and returns true if they match and false if they don't.
  • @HspStringCompareArray (srcstring, trgtstr[]): Compares source string to an array of strings and returns, strangely, doesn't return boolean result rather integer 1, 2 and/or 3.

Frankly speaking, none of these functions are rocket science or very tough to code in Java and what they do one way or another can be done using native Essbase functions. However, these come from Oracle, which means they are convenient and they are designed to ease your life. In most cases, they greatly shorten the script. Now onto the examples and real life use-cases.

@HspNumToString: You can pass any number into @HspNumToString and it throws a string which can be used in other string functions such as @Substring, @Concatenate, @Match or better with one of the other @Hsp custom string functions which you'll find in today's blog. It accepts constant number, variable or member as parameter. Usage is simple:

@HspNumToString(10) Returns 10 as string

Var num1=10;
@HspNumToString(a) Returns 10 as string

"Sales"=100;
@HspNumToString("Sales") Returns 100 as string

@HspDateToString (NumericDateInput): Oracle has been criticized by Hyperion Planning community due to the fact that date data type in Essbase and Planning are not compatible. Essbase keeps the date the way Excel keeps it but rather more precisely. The numeric corresponding value for a date in Essbase is the number of seconds that elapsed since January 1 1970. Although there are ways to interchange one format to another, yet there was no out of box function to calculate differences between two dates or to roll a given date by a given number of date parts (i.e. days, weeks, months). We will touch upon date type custom @Hsp functions in the next entry in the blog.

@HspDateToString is a cross between string and date type functions. What it does is quite simply converting Planning's date format to a dd/mm/yyyy string date format (including forward slashes). You may ask why anyone needs this function. Curiosity grows considering the fact that you cannot return or store a string value in an Essbase calculation. At least this was what I had thought when I first noticed this function. However, couple this function with Essbase's own date functions and you will have unrestricted access to native Essbase date functions.

All Essbase date functions accepts a numeric value that is calculated as above and @TODATEEX function converts a given date in a specific string format to this number. Here is a sample:

var date1, date2, weeksduration;
date1=@TODATEEX("dd/mm/yyyy",@HspDateToString("Project Start Date"));
date1=@TODATEEX("dd/mm/yyyy",@HspDateToString("Project End Date"));
weeksduration=@DATEDIFF(date1,date2,DP_WEEK);

Above example, as you might have already noticed, calculates duration of a project in weeks. Believe me, it has never been any simpler... But it will be even simpler! Wait until next post...
@HspConcat (StringArray): If I shout like "tell me the biggest problem with @Concatenate function in Essbase", I imagine most of the audience would shout back to me like "the limitation of two string parameters!" Ok, that's a too long sentence to shout together in a group but you got my point I guess. @HspConcat allows you to merge n number of strings into a single string. It also accepts other string functions as input parameter. Below is how:

@HspConcat(@LIST("Project","-","2013","-","001")) Returns Project-2013-0001 as string.

With @Concatenate however, you would have ended up using multiple nested functions to get the same result.

If your purpose is to You can pass this into @Member to convert the string into a member.

@HspStringCompare (srcstring, trgtstr): This simple boolean function returns true if given two string matches and false if they don't. Here is how:

IF(@HspStringCompare("abcd","abcd"))
calculation;
ENDIF

@HspStringCompareArray (srcstring, trgtstr[]): I am not sure whether there is a bug with this function or it's how it's designed but this function is not a boolean function. It returns 1, 2 and possibly 3. The result is 1 if not all the string values in array are equal to first given string. The result is 2 if all strings in the array are equal to first given string. The result is 3, well, I am not sure when, but if the results were limited to two, Essbase would have accepted it as boolean. Below calculation works:

IF(@HspStringCompareArray("abcd",@NAME(@LIST("abcd","abcd","abcd")))==2)
calculation;
ENDIF

This concludes today's blog on the string functions in @hsp function series. In the next post, we will look into date type functions.

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?!

Sunday, 8 September 2013

New blog on the block (what?)

I must admit that I am guilty for not starting a blog much earlier. I must have started writing a blog on EPM a long time ago. I had both time and desire to do so especially when I was frantically posting replies to OTN Planning forums. Had I spent half of the time I spent posting messages to the forum on posting blogs, I would have had a pretty decent library of best practices, tips and tricks. Not that I know so much about Hyperion, Oracle EPM and everything around it of course, I learnt so much reading others' posts and blogs if I put half of the information I collected from here and there into a blog in an organized manner, that would have been quite a decent reference for myself and I wouldn't have been happier if it helped others. There are number of people out there doing so. You already know them: John Goodwin, Celvin Kattookaran, Gary Crisci, Cameron Lackpour and Glenn Schwartzberg are just a few of those I followed all these years. Having mentioned all due credits, here I am, much later than I should have, I started writing this blog. I chose the title for this blog EPM Universe and to my fortune it was available. 

Why EPM Universe? Well, Universe is big right, so is EPM. 

Finally, my manifesto for this blog: "To infinity and beyond!"