HSP – Essbase Down Under

G’day All,

So after a week of terrible terrible REST puns from yours truly and the usual suspects.

I figured I’d write up something non REST related….you might even say unREST rela…okay, I’m done.

So What Are We Breaking Today?

For a recent project, we have been building a standard workforce \ position management cube. Fairly basic – uses some of the time factor stuff I wrote about here – but fundamentally nothing too fancy.

During the first feedback session with the client, we were asked – specifically while showing the ‘Move Position to a New Org Unit’ section – could we set a flag on the position for the Source Business Unit and Target Business Unit so both Business owners knew where the employee had come from and gone to?

This is actually reasonably nuanced. Security is setup per Business Unit, so if you move an employee to a Business Unit you don’t have access to, you actually lose access to the employee and vice versa for the receiving Business Unit. Hence having the visibilty of the Source and Target BU at least provides visibility as to whom to contact.

Anyway, since it sounded like a really good idea, and I’m a ~~cocky little prick~~ confident young man I immediately said yes.

Hubris is wonderful.

What seems to be the problem officer?

Okay so the fundamental problem is that we need to be able to ‘Prompt’ for a Target Business Unit to move data into – therefore it really needs to return a member name. But we also want to grab both the Source and Target Business Units and update them to Smartlists.

This is interestingly difficult. Both Essbase and Planning scripts don’t ‘really’ like handling strings. Specifically it’s possible to transition strings into member names, but it’s difficult to use strings as variables and very difficult to create a text information on the fly. Needless to say, what I thought was going to be a 10 min job turned into a significantly longer process.

Now, as Celvin pointed out (possibly while sighing) all of this would be very simple in Groovy – but I’m old school. And Stubborn. And we have got a few new functions to play with in PBCS, so lets see what we do in a stock PBCS instance.

 
FIX([[PlanningFunctions.getUserVarValue("UV_Scenario")]],[[PlanningFunctions.getUserVarValue("UV_Version")]],&Year_Assumption,"Local","Month")
  FIX({Var_MovePosition})
    FIX({Var_BUSource},{Var_EntitySource},{Var_ProjSource})
      &Period_Assumption(
	IF(@COUNT(SKIPNONE,@MERGE({Var_BUSource},@MEMBER(@CONCATENATE("HSP_ID_",@HspNumToString({Var_SL_Target_BU}))))) == 1)
	  @RETURN("Source and Target Business Units cannot match",ERROR);
	ENDIF
      );
    ENDFIX
    FIX({Var_BUSource},{Var_EntitySource},{Var_ProjSource})
      &Period_Assumption(
        /*Push the data from Source to Target Business Unit and update the end date and transfer flag */
	@XWRITE({Var_EntitySource},@LOOPBACK,@CONCATENATE("HSP_ID_",@HspNumToString({Var_SL_Target_BU})),{Var_EntityTarget},{Var_ProjTarget});
      );
      "Emp_End_Date_Override" = @CalcMgrRollDay({Var_StartDate}, @_false);
      "Transfer" = {Var_SL_Target_BU};
    ENDFIX
    FIX(@MEMBER(@CONCATENATE("HSP_ID_",@HspNumToString({Var_SL_Target_BU}))),{Var_EntityTarget},{Var_ProjTarget},&Period_Assumption)
      /* Update the Target Start date and Transfer Flag */
      "Emp_Start_Date_Override" = {Var_StartDate};
      "Transfer" = @CalcMgrDoubleFromString(@CalcMgrFindFirst(@ALIAS(@NAME({Var_BUSource}),"SLAliases"),"[0-9]+", @_true));
    ENDFIX
  ENDFIX
ENDFIX

What?

Okay, I realise I’m using a few new functions here. Let’s start from the top and work down.

FIX([[PlanningFunctions.getUserVarValue("UV_Scenario")]],[[PlanningFunctions.getUserVarValue("UV_Version")]])

Okay – so the documentation on this has gotten significantly better. Basically this first section is calling User Variables as variables in a Business rule.

This is a massive time saving versus the old hack job that we’ve been using for years.

FIX({Var_MovePosition})
  FIX({Var_BUSource},{Var_EntitySource},{Var_ProjSource})

These are really simple, just standard member variables. The only thing to bear in mind here is that you need to setup your action menu so it brings in all the variables into the rule, otherwise it will show the Source members as prompts. The way action menus are best understood (in my mind at least) are that they will only natively bring in everything ‘left and up’ of where you’ve selected.

As such, I’ve elected to make the required parameter Position which means that I can only bring up the context menu by selection to the right of where Position is shown on the form. Note: Business Unit and Entity are in the Point of View.

The left hand panel shows a selection on the first column (Project), the right hand panel shows the selection from the second column onwards.

Essbase Error in Your Favour – Collect $200

&Period_Assumption(
  IF(@COUNT(SKIPNONE,@MERGE({Var_BUSource},@MEMBER(@CONCATENATE("HSP_ID_",@HspNumToString({Var_SL_Target_BU}))))) == 1)
    @RETURN("Source and Target Business Units cannot match",ERROR);
  ENDIF
);

Okay, the first of the fun lines. Functionally what this is designed to do is give an error message to the user if they pick the same Target Business Unit as the Source.

Starting from the right hand side, we’re first converting the Smartlist Target Business Unit {Var_SL_Target_BU} into a member:

 @MEMBER(@CONCATENATE("HSP_ID_",@HspNumToString({Var_SL_Target_BU})))

This all came out from some random code that one of my guys found in an EPBCS app and a conversation with Celvin.

To quickly recap I’d recommend reading Celvin’s post on the topic or alternatively Poh-Huat’s incredibly detailed maiden blog post for more details.

But in summary:

when you automatically create a smartlist in PBCS it also creates a hidden alias table

This alias table (SLAlias) is added to the members you used as a Source of your smartlist

It contains every member with HSP_ID_ as a prefix and the assigned integer as a suffix

By wrapping @MEMBER around the @CONCATENTATE we return a full valid member name using the SLAlias table. The @HspNumtoString function converts the Smartlist ID into a numeric string that can be concatenated.

 IF(@COUNT(SKIPNONE,@MERGE({Var_BUSource},@MEMBER(@CONCATENATE("HSP_ID_",@HspNumToString({Var_SL_Target_BU}))))) == 1)

We then wrap the @COUNT(SKIPNONE, around the function, passing through the {Var_BUSource} member and the Target member from the Smartlist.

The @MERGE function wrapped around both members will merge all duplicate members.

The end state for this is – if the Source Business Unit is the SAME as the Target Business Unit the count will be 1 and the @Return will fire stopping the code.

Ah. Push It

So for the second section of code, we push the data into the Target Business Unit.

FIX({Var_BUSource},{Var_EntitySource},{Var_ProjSource})
&Period_Assumption(
	/*Push the data from Source to Target Business Unit and update the end date and transfer flag */
	@XWRITE({Var_EntitySource},@LOOPBACK,@CONCATENATE("HSP_ID_",@HspNumToString({Var_SL_Target_BU})),{Var_EntityTarget},{Var_ProjTarget});
);
"Emp_End_Date_Override" = @CalcMgrRollDay({Var_StartDate}, @_false);
"Transfer" = {Var_SL_Target_BU};

Okay, still a few interesting functions here. @XWRITE is pretty common – but the optional @LOOPBACK function is useful in it’s ability to write back to the same database. Using @XWrite here has two advantages

We don’t have to worry about block creation (XWrite takes care of that)

Natively Xwrite accepts ‘strings’ and converts them into member names – makes the code significantly easy to manage

You can see here we’re using strings, so we don’t need to wrap the smartlist lookup @CONCATENATE(“HSP_ID_”,@HspNumToString({Var_SL_Target_BU})) with @MEMBER – the Alias string works just as well.

We’re also updating the Emp_End_Date_Override member with a new date using a fancy CDF. Specifically @CalcmgrRollDay wrapped with the @_false boolean flag to roll BACK one day (bonus points if you worked out @_true would roll forward one day).

Finally we assign the Transfer flag with the selected Smartlist. This is just an integer at this point, so can be directly assigned.

Flip It and Reverse It

Last step – assign the flag and start dates on the Target Business Unit.

FIX(@MEMBER(@CONCATENATE("HSP_ID_",@HspNumToString({Var_SL_Target_BU}))),{Var_EntityTarget},{Var_ProjTarget},&Period_Assumption)
  /* Update the Target Start date and Transfer Flag */
  "Emp_Start_Date_Override" = {Var_StartDate};
  "Transfer" = @CalcMgrDoubleFromString(@CalcMgrFindFirst(@ALIAS(@NAME({Var_BUSource}),"SLAliases"),"[0-9]+", @_true));
ENDFIX

Okay, the FIX is basically the same function again – this time wrapping it in @MEMBER so we can put it in a fix statement, but everything else is simple. Secondly Emp_Start_Date_Override just equals the start date (since we rolled the end date one day back in the previous step, there is no double up of costs on that day).

The last bit is fun though. We’re basically attempting to work out the Smartlist ID value that is associated with a Source Business Unit member variable.

To do that, we’re actually calling the @ALIAS function. This function, in PBCS at least, has a semi-undocumented feature of allowing you to select which alias table to look at. The documentation above is actually for Oracle Analytics Cloud – alternatively, have a look at the function itself within calc manager:

So we can convert the member name into the alias table, using the SLAliases alias list, which will give us our HSP_ID_xxxxxx.

At that point, we need to parse the function to get the number – and here regular expressions come into play.

We’re using the new @CalcMgrFindFirst function to provide the first match of the defined regular expression.

In order to match just the numbers in the string, I’m using the very simple match:

"[0-9]+"

Basically one or more of any number.

For anyone just starting in regular expressions (and I’m a neophyte myself), I cannot recommend Regex101 enough. I mean, look at it.

Once we’ve got a returned number from the regular expression match, we then convert it from a string into a number (strictly speaking, a double) using the @CalcMgrDoubleFromString.

 "Transfer" = @CalcMgrDoubleFromString(@CalcMgrFindFirst(@ALIAS(@NAME({Var_BUSource}),"SLAliases"),"[0-9]+", @_true));

So basically we’re converting a member to it’s alias so we can parse the ID from that string, then converting that to a number which just happens to be the Smartlist member that we actually want! Serendipitous.

And that’s that

Definitely didn’t expect to have to crack open regular expressions to get there, but I had some fun with lots of the new features and functions!

Hope you find some of this helpful.

Cheers
Pete

G’day All,

As promised – my final post on time\date factors\arbitrary rants about date systems.

This time, we delve under the covers to have a look at some of the new HSP functions – which massively simplify any work that you need to do with dates in Hyperion!

Quick Recap

H’okay. If you’ve read back through my previous posts about time in Planning and Essbase (specifically this one and that one) or alternatively if you’ve ever beaten your head against the desk repeatedly about the seemingly cumbersome way in which essbase and planning use dates then you’ll be aware of the following:

Planning and essbase have different date systems
What works in Planning doesn’t work in Essbase
What works in essbase doesn’t work…basically full stop
It’s really difficult to efficiently use dates
The ‘simplest’ method is often to convert dates into effective strings and do ‘math‘

But, with the new HSP functions – that’s pretty much all changed.

Surprise Documentation!

Since these functions have been added there have been screams (well…modest complaints) about the staggering lack of documentation. Fortunately some amazing members in the EPM community have taken it upon themselves to change this, and through a combination of blog posts over the years have added many notes:

Alp Burak Beder – HSP Custom Functions 1
Alp Burak Beder – HSP Custom Functions 2
Cameron Lackpour – Calc Manager BSO and PLanning
Celvin Kattookaran – Calc Manager Execute MAXL and MDX
Robert Gideon – Calc Manager HSP Update 11.1.2.3
Robert Gideon – 11.1.2.4 New Functions

And now it seems Oracle has come to the party – adding the following to their Calc Manager \ Business rule Documentation.
Oracle Working with Custom Defined Functions

So let’s have a look then!
Okay, so perhaps most importantly with these functions – they are designed to work directly on Planning input dates. As such, they are expecting an Essbase date in the format (“YYYYMMDD”) and a Planning Date in the selected date format in the Planning apps.

The nice thing about this (for you crazy Americans) is that you can use discrete date options per user, but still use the same underlying functions. See date entry in mm-dd-yyyy in Planning, while the Smartview retrieve is showing dd-mm-yyyy.

Or. You know. Your could standardise.
Standards

The Juicy Bit
Where was I….

Below is a listing of almost every date function in 11.1.2.4 – I’ve put in the function, the usage and the outputs (in both essbase and planning).

Some interesting functions in that list: CalcMgrRollDate – This is a cool function! Only for Month and Day

@CalcMgrRollDate:

[Adds or subtracts (up or down) a single unit of time on the given date field without changing larger fields.

For example,

    @CalcMgrRollDate(19960131,"month",@_true) will result in the date of 19960229.

    @CalcMgrRollDate(19960131,"day",@_true) will result in the date of 19960101.]

This is very useful. Rolling forward the ‘last day’ in the month without needing to deal with differing month lengths. It’s also setup for leap years, so massively reduces coding.

Another useful function is @CalcMgrGetCurrentDate. At first I couldn’t get it working, but after a quick google landed me on Robert Gideon’s blog and I was on my way. Could be invaluable if you wanted to script a data export to pass a date to the filename.

I did have issues with @CalcMgrGetDatePart and @CalcMgrAddDatePart returning slightly odd values or no values hence their exclusion above. However the main component of their functions have been replaced by more discrete functions, so they have limited utility anyways.

Anyways – I’ll hopefully be back soon with another blog post on the REST of the HSP functions. Any questions \ comments, hit me up in the comments.