Members to Strings, Planning Expressions and More CDF Fun

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.
    Regular Expressions

    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

    PBCS and Fun New Features

    KScope Wrap!

    G’day All,

    So Kscope16 is over, done and dusted, and I’m back home. A quick thankyou to everyone who I met – was amazing to be able to put a face and a voice to the names/blogs/twitter accounts I’ve followed over the years!

    For everyone else – you’ll be glad to know the likes of Jason Jones, Cameron Lackpour, Tim German and Celvin Kattookaran (and others I’ve naught mentioned for brevity, but you know who you are) are as tragically nerdy amazingly bright, excited and enthused about this space as their blogs suggest. I was very endeared by their absolute state of gregarious comradeship – a spirit of learning and challenging yourself to become better – lead to both a humbling and inspiring couple of days.

    A really quick story before I get started on the actual post. I was standing in the main foyer with a few people watching the boats go by when Celvin (a Chi-City native) commented that: ‘It’s amazing you know – tour guides on the boats volunteer all their time! They don’t get paid at all, they just do it because they love the city of Chicago’. Silence reigned for a few moments – until the irony of Celvin (of all people) commenting in shocked admiration that ‘people that volunteer their time, just for the love of it’ sunk in!

    Moving on…

    So this will be the first in a series – perhaps not as compleat as others – looking at some of the new features in PBCS that will hopefully wend their way back to On-Premises at some point!

    This is a 16.07 PBCS Test environment – released early July.

    First thing I’ll be looking at is the automated Smartlist generation.

    Step 1 – Nice and Easy

    Nice and simple, we create a few new members. You’ll see here I’ve created a list of members under a parent Super Rates.

    Create_New_Members

    The keen eyed amongst you might notice one of the other changes – duplicate aliases are now in place and supported completely. Aliases can have the same name as the member (and other members!) as long as the member names are unique.  To quickly reference the release notes:

    “When referencing an alias that resolves to multiple members in free-form mode, an error is returned noting that the alias cannot be resolved to a single member. You can do this either by using the Member Selector to select the correct alias name or by hand-typing the qualified name. Using the parent member name as a qualifier should be sufficient in most cases, given that only duplicate alias names are supported, not duplicate member names.”

    Step 2 – Nothing Doing

    Next up, create a smartlist, same as normal.

    Create_Smartlist

    At the bottom you’ll see the “Create From Members” Checkbox with a member selection. I’m obviously picking the descendants of Super rates rather than hard coding the list (which becomes very cool shortly.

    Step 3 – Too Easy

    After saving the smartlist, returning to the entries page shows the members!

    Review_Smartlist_Delete

    You can see that the Name is set to the Member Name, while Label has been updated to the Alias. In an earlier test I used the Aliases as the member names (because smartlist names have some limitations) – the spaces were removed as well as the special characters (%) and replaced by underscores, but the label was still correct.

    Step 4 – Mixing in the Awesome

    You’ll now be wondering what happens if you create a new member – well let’s have a looksee.

    Adding_New_Members

    Created a new fake member under Super_Rates, and upon refreshing the database…

    Step 5 – Bada Bing Bada Boom

    Review_Smartlist_Add

    Awesome.

    But there’s no point if it hangs around, so let’s see what happens if we delete the member and refresh it

    Step 6 – I’ve Overdone the Pringles Thing…

    Huzzah!

    Manage_Smartlist

    Wrapping Up

    So that’s awesome and should help massively in defining smartlists going forward! Stay tuned for some more new features (as I get a chance to find them and test them!)

    Cheers

    Pete