Members to Strings, Planning Expressions and More CDF Fun

Posted on: August 5, 2017 May 10, 2020
Categories: Tips and Tricks
Tags: CDF, Essbase, HSP, PBCS, Smartlist

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