Notice: WP_Scripts::localize was called incorrectly. The $l10n parameter must be an array. To pass arbitrary data to scripts, use the wp_add_inline_script() function instead. Please see Debugging in WordPress for more information. (This message was added in version 5.7.0.) in /var/www/wp-includes/functions.php on line 5663

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.

	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);
        /*Push the data from Source to Target Business Unit and update the end date and transfer flag */
      "Emp_End_Date_Override" = @CalcMgrRollDay({Var_StartDate}, @_false);
      "Transfer" = {Var_SL_Target_BU};
      /* 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));


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


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.


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

  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);

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:


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.

    	/*Push the data from Source to Target Business Unit and update the end date and transfer flag */
    "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.

      /* 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));

    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:


    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.


    Conditional FIX statements

    A few years ago the very very talented Mike Henderson gave a Kscope presentation entitled Power Tools: Using VAR + LOOP for If-Then Procedure Logic and ARRAY for Speed (the presentation can be found here.) First and foremost the presentation has the easiest to understand and most detailed description of how arrays work in essbase – including a perfect use case and lashings of ginger beer code!

    It also describes a nifty way of building conditional IF statements, allowing differences in treatment between two scenarios, without the performance and code cleanliness hit of having to do repeated IF(@ISMBR(“Sparse”)) intersections.

    The only issue I had with Mike’s proposal is that…it’s slightly confusing.

      runForecast=0 Enter 1 to skip Forecast scenario; 
      runBudget=0 Enter 1 to skip Budget scenario; 
    VAR ForecastBreak = &runForecast;
      [Forecast code goes here]
    VAR BudgetBreak = &runBudget;
      [Budget code goes here]

    Basically it’s almost inverse of what you’re expecting. The variable ForecastBreak needs to be set to 1 if you want to SKIP the Forecast code. After I’d used the code in a few different places, I ended up practically shelving it because I was having to explain what the code was doing (sometimes to myself. I tried not to do this out loud.)

    That said, it’s still an elegant solution to a fairly fundamental problem. Most other coding languages have the ability to do a conditional IF statement, and it definitely has use cases within Hyperion.

    Fast forward a few years

    A few years later I was doing some (all too brief!) work with the brilliant Danish Hotta and noticed in his code the following:

    FIX("Forecast" AND {Var_Scenario})
      [Forecast code goes here]
    FIX("Budget" AND {Var_Scenario})
       [Budget code goes here]

    Being an inquisitive fellow, and having never seen that code pattern before, I immediately asked (somewhat incredulously in hindsight) what on earth that was meant to do. He explained that it was to split the code up between Forecasts and Budgets.

    I’d like to pretend that I believed him immediately and obviously didn’t have to have it proved to me…and given that this is my blog I shall take the liberty in stating that it only took around 10-15 minutes of patient explanation before I understood what it was doing.

    So what IS it doing then?

    Since this appears to be completely undocumented behaviour, let’s break it down to the component parts and work out what is happening.


    Emptymembersets is that function that you don’t need to understand, right up until it bites you and then you set it in every script.

    The documentation is actually very clear as to what it’s doing:

    If EMPTYMEMBERSETS is ON, and a FIX command evaluates to a (sic) empty member set, the calculation within the FIX command stops and the following information message is displayed: “FIX statement evaluates to an empty set. Please refer to SET EMPTYMEMBERSETS command.” The calculation resumes after the FIX command. If a calculation script contains nested FIX commands, the nested FIX commands are not evaluated.

    However, the pertinent point is ‘what does evaluation to an empty member set’ actually mean. It is not a evaluation of data, instead, an empty member set is where the intersection of the fix statements resolves to something fundamentally invalid. Often times this will occur when using functions within FIX Statements ie:

    SET EMPTYMEMBERSETS OFF; /* This is the default */
    CLEARDATA "Actual";
        Actual = 100;

    Begbalance is a level zero member, ergo it does not have children, so the reference it is an invalid. The EMPTYMEMBERSETS flag then defines the calculation behaviour, with the default behaviour completely ignoring the Fix statement and loading data to all periods.


    Setting EMPTYMEMBERSETS to ON then changes this behaviour – ignoring both the fix statement and all of the calculations within it

    CLEARDATA "Actual"; 
        Actual = 100;


    To further the point, the greatly venerated C.Lackpour wrote a very long blog post on this – going into detail about differences in behaviour of EMPTYMEMBERSETS with nested fix statements. I would strongly recommend reading it before continuing this post – I’ll wait.

    Okay…you’re back

    So now we know what EMPTYMEMBERSETS does – how does this help us with a conditional IF? This is where that Boolean AND function comes into play.

    CLEARDATA "Actual";
    CLEARDATA "Budget";
    FIX(Actual AND &Var_Scenario)
    	AUD = 100;
    FIX(Budget AND &Var_Scenario)
    	AUD = 200;

    In FIX statement above, the scenarios are hardcoded, and then a scenario variable is passed through with a Boolean AND statement. In this case I’m using Essbase run time sub variables but could just as easily be done in a Planning application with calculation manager variable.

    Tricka Technology

    Triggering the calc above and looking first at the output in smartview, we can see that the Budget data has calculated, and the actual data has not changed.
    Reviewing the log we can see the that the resolutions of Actual AND budget has resolved to an empty set, therefore ignoring the FIX statement and all of the code therein.

    Tricka Technology the Remix

    Changing the variable to Actual and rerunning?

    CLEARDATA "Actual";
    CLEARDATA "Budget";
    FIX(Actual AND &Var_Scenario)
    	AUD = 100;
    FIX(Budget AND &Var_Scenario)
    	AUD = 200;

    Exactly what we’d expect to see:

    The most interesting aspect of this is to look at the log. The evaluation of the fix statements actually occurs as the first step of the code – and essbase is therefore aware that this fix statement can be ignored and doesn’t even try to resolve it later. This can act as a staggering optimisation in any code because it completely skips over even the resolution of anything within the fix statement.

    So there you have it

    A clean and simple method to have conditional and variable driven FIX statements optimally calculating a component of a script. It works across both Dense AND sparse intersections and can be used both to standardise and simplify code – as well as providing the ability to selectively optimise any rules, particularly where you can pass through parameters from a planning dataform.


    HSP Date Functions in

    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
    Robert Gideon – 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.

    The Juicy Bit
    Where was I….

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

    All Date HSP Functions

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

    [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.


    IPMT and PPMT in Essbase

    G’day All,

    The subtitle for this post could be “How to make sure the guy after you doesn’t hate you – making code legible”

    As the newest Ace Associate and java-junky-Jason-Jones points out – code is read significantly more often than it is written

    He then also used the term “behooves” correctly – so perhaps he just felt in the mood to wax lyrical.


    The underlying logic for the functions PMT\IPMT\PPMT is not that difficult. Basically: for a fixed term loan you are to work out the full repayment, the interest component and the principle component. Obviously as you go through the loan period the ratio of interest to principle decreases, you pay more interest at the front when your principle amount is high and more principle at the end when the amount owing has diminished.

    For a thorough and absolutely fantastic walk through of how to convert the excel function into code (including examples of Java & SQL!) you should definitely go and read this article by Kevin Cross . Actually – you should go and read it anyway – it’s an incredibly clear yet detailed discovery document, stepping through all of the issues, giving exceptionally clear examples (and counter-examples) and then finishes with a multitude of questions and answers for specifics. If the rest of world’s software documentation was half as good as this then there would be significantly fewer stressed developers (and a hell of a lot less random posting to stack overflow)!

    And in Essbase?
    So. To convert what is there to essbase functions. It should be noted that I’m cheating a little – this is really the IPMT and PMT functions in their default state, with a simple loan type and no value remaining at the end.

    Tn is the length of the loan.
    n is the period in question.
    pv is the present value
    rt is the Interest Rate
    Lease Payments PMT = (rt / (1 - @POWER((1 + rt),-tn))) * pv;
    Interest On Lease IPMT = (pv * rt * (@POWER((rt + 1),(tn + 1)) - @POWER((rt + 1),n))) / ((rt + 1) * (@POWER((rt + 1),tn) - 1));
    Principal on Lease (PPMT) = PMT - IMPT */

    No worries right? Too easy.

    Where it all becomes a bit…messy

    I got to actually writing this piece of the code however, and I realised I was going to have a bit of a problem. Tn (length of the loan) was a calculated field based upon a lookup off a smartlist with an override. N was a dynamic calc member. pv was a piece of data entry against a completely different intersection and rt was another lookup, with another potential of an override! Given that the IPMT code was already of a significant length, how could I make this code actually readable?

    To give you an example of what this code looked like with all the cross-dimensionals (and note, this doesn’t include the overrides!)

    Interest_On_Lease_IPMT = ("Total_Cost"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * "Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 * (@POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),("Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12 + 1)) - @POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),"Loan_Period"))) / (("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1) * (@POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),"Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12) - 1));

    Yeah. Not wonderful. And to be honest, once I add in the 3 other alternatives with the overrides, quite ridiculous.

    The calculation for IPMT isn’t ever going to change – however, it’s possible (although unlikely) that the data locations might. Therefore, if down the track one of the data locations needs to be updated, somebody will need to go through this function piece by piece ensuring that they only change the right bit.

    Clean the yard
    So, how to make it easier? How about we make all the data locations variables instead?

    var tn = 0;
    var n = 0;
    var pv = 0;
    var rt = 0;
    /* Open Calc Block */
    	/* If there is a lease override use it, otherwise use the interest rate */
    	IF("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump <> #missing)
    		rt = "Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12;
    		rt = "Interest_Rate"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12;
    	/* If there is a asset life override use it, otherwise use the default asset life */
    	IF("Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump <> #missing)
    		tn = "Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12;
    		tn = "Asset_Life_Global"-> "Unallocated Organisation"-> "Unallocated Activity"-> "Unallocated Line"-> "BegBalance"-> &Year_Assump * 12;
    	/* Set Present Value and Period */
    	pv = "Total_Cost"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump;
    	n = "Loan_Period";
    	Lease_Payments_PMT = (rt / (1 - @POWER((1 + rt),-tn))) * pv;
    	Interest_On_Lease_IPMT = (pv * rt * (@POWER((rt + 1),(tn + 1)) - @POWER((rt + 1),n))) / ((rt + 1) * (@POWER((rt + 1),tn) - 1));
            Principal_on_Lease_PPMT = Lease_Payments_PMT - Interest_On_Lease_IPMT;	

    Much easier. The code matches up exactly with the actual function (thus making it significantly easier should somebody ever need to additional interest functions) and it’s also much easier to review (and change!) the data locations should they ever need to be updated.

    And trust me. The guy after you will thank you for it.


    A History of Dates, Essbase and Why Unix time sucks

    A multi-page rant about time, leap seconds, the amount of acid the Unix developers took in the 70’s and Essbase vs planning time.

    Note: As per the previous blog post – to my American audience – in this post I will not be using the arbitrary retarded roller coaster  of US date formats, instead I’ll generally be using the significantly more ‘standardised’ dd\mm\yyyy format or yyyymmdd as needed. In interests of fairness, I’ll probably flag the ones that are potentially confusing, but rest assured, I’m not creating new months.

    Where it all begins
    Since the beginning of computing – the interaction of dates within a binary computer system has caused issues. Unix time in particular has an interesting history – in summary, the original timing mechanism was designed to calculate 1/60th of a second – with the epoch time being 01/01/1971. However they quickly realised that utilising a 32-bit integer only gives you around 3 years of ‘time’ to play in – so the timing was changed to every second and the epoch date changed to 01/01/1970 . Entertainingly this means we face another Y2k bug in 2038 when we hit the 32-bit overflow tracking in seconds.

    Essbase dates are a variant on Unix Time – aka the number of seconds since 01/01/1970. That is to say, Essbase dates are rendered as a sum of all the seconds since 1970, with the exception of leap seconds, of which there have been 25 since 1970, with another due in June 2015.

    Confused yet? To be honest, it’s getting a little bit arbitrary…still, it could be worse

    In a sense this is similar to the date method inherit to Excel whereby dates aren’t really dates, but are sequential numbers counting the number of days since the 1st of January 1900.

    As an aside I always wondered why they picked that date. As it turns out, they did that in excel v1.0 (released in 1987) to match up with Lotus 1-2-3 (the incumbent at the time). However, not only did they pick that date to match up with Lotus – they also deliberately included the same error: both Lotus and Excel include 29/02/1900 – despite the fact that it doesn’t exist!

    To make this story slightly more entertaining – when VBA was released in 1993 it was decided to ‘fix’ the incorrectly added 29/02/1900 – however in order to keep the date function consistent between VBA and Excel, the date value in VBA actually starts on the 31/12/1899!

    Back to the point: one of the other key differences between Excel and Essbase is the ability of Excel to actually use those dates in a sensible way. Excel has the concepts of Day(), Month(),Year(), EDate() – all easily available and all exactly as you’d expect functionally. Essbase…doesn’t.

    Lets examine @DATEROLL for instance

    Functionally very similar to EDate() – it’s designed to add a number of x (where x can be defined as period type) to a date. This example adds 10 months to the date.

    So in Excel:


    In Essbase:

    MyDate2=@DateRoll(MyDate1, DP_MONTH, 10);

    That’s still not ‘too bad’ right? Lets try something a bit trickier. Find the first day of the next month after a defined date.

    In excel:
    Roll the month, take off the number of days in the entered month and add 1


    Alternatively – find the end of the current month and add 1!


    Okay, now in Essbase:

    Var DateNextMonth
    DateNextMonth = @DateRoll(MyDate1,DP_DAY,1);
    MyDate2=@DateRoll(DateNextMonth,DP_DAY,((@DATEPART(MyDate1,DP_DAY) * -1)+1));

    Getting needlessly messy but it’s not too bad. So what’s the problem you ask?

    Bringing Planning into the Mix

    Some/most of you will be aware of the ‘date’ data type in Planning. Gives you the awesome ability to load dates to a planning dataform – exceptionally useful for depreciation models, employee costing / payroll models, birthday reminder applications (seriously – consider it – automated financial reporting emails with birthdays on them, it’d be awesome. I wouldn’t forget my mother’s birthday ever again!).

    The issue is – for reasons that are unknown and are honestly baffling – the formatting of dates from Planning do not match a valid Essbase date format.

    These are the available Essbase Date formats.
    Valid Essbase Date Formas

    The one missing? Yep – the format that Planning uses when it pushes data to Essbase: “YYYYMMDD”

    So what does all that mean?
    Basically when you’ve got dates coming from a Planning application you need to deal with the fact that the incoming format isn’t the same as what Essbase expects. And as such, you’re going to need to massage it.

    Let’s look back up at our example above, finding the first day of the next month after a defined date.

    Option 1 – Converting Planning Dates to Essbase Numbers

    VAR NewDate1inSecs = 0;
    VAR NewDate1inSecsRolled = 0;
    Var NewDate2inSecs
    /* Convert Newdate1 to secs */
    NewDate1inSecs = @DATEROLL(0, DP_MONTH, (((@INT("NewDate1"/10000) - 1970) * 12) + 
    @INT(@MOD("NewDate1",10000)/100) -1)) + 
    (86400 * (@MOD("NewDate1",100) -1));
    /* Roll NewDate1 to the next month */
    NewDate1inSecsRolled = @DateRoll(NewDate1inSecs,DP_DAY,1);
    /* Net back the days to the first day */
    NewDate2inSecs=@DateRoll(NewDate1inSecsRolled,DP_DAY,((@DATEPART(NewDate1inSecs,DP_DAY) * -1)+1));
    /* Convert my date 2 back to a planning date format */
    Var NewDate2Format
    Var NewDate2Yr
    Var NewDate2mth
    Var NewDate2day
    NewDate2Format = @FormatDate(NewDate2inSecs,"yymmdd");
    NewDate2Yr = @INT(NewDate2Format/10000);
    NewDate2mth = @INT((NewDate2Format - NewDate2Yr*10000)/100);
    NewDate2day = @MOD(NewDate2Format,100);
    NewDate2= 20000000 + (NewDate2Yr * 10000) + (NewDate2mth * 100) + NewDate2day;

    Okay, that’s getting silly. Needing to convert in and out of dates every time is simply going to complexify your code. At this point, you might as well just start splitting the Planning date into strings and not bothering with the inbuilt essbase date functions.

    Option 2 – Using Math against Planning Date strings

    VAR yr = 0;
    VAR mth = 0;
    /* Calc within a block */
    /* Find the Year */
    yr = @INT(NewDate1/10000);
    /* Find the month */
    mth = @INT((NewDate1 - Yr*10000)/100);
    /* If December */
    IF(mth == 12)
    	NewDate2 = ((yr+1)*10000) + (1*100) + 1;
    /* Else roll the month */
    	NewDate2 = (yr*10000) + ((mth+1)*100) + 1;

    Definitely simpler – but manipulating strings is a bit inelegant!

    Let’s get exotic

    Okay, so what if you really want to/have to use the Essbase date functionality. Let’s have a look at the other options.

    In you’re very limited as the only HSP function you’ve got available is HSPDateToString – however what that does is very useful. Basically that will take a Planning Date string (YYYYMMDD) and format it as a DD/MM/YYYY. That in itself is fairly useless, but in combination with TODATEEX you can convert a planning date directly into an Essbase “number of seconds” value. Which can then in turn be used in the Essbase functions!


    This still has the underlying problem that you end up with an Essbase Date as the answer rather than a Planning one, but is useful for functions like @DATEDIFF

    From onwards, you can use start to use some of the ill-documented Custom Defined Functions:

    [CDATA[ @HspDateRoll(startDate, noOfPeriods, periodType)]];
    [CDATA[ Returns the next date, resulting from adding noOfPeriods to StartDate.]]

    Okay – similar format to DateRoll – but you don’t have to modify the date on the way in. Instead it uses the Planning dates and RETURNS a Planning date. So returning to our original problem – finding the first day of the next month after a defined date.

    Var DateNextMonth
    DateNextMonth = @HSPDateRoll(MyDate1,DP_DAY,1);
    MyDate2=@HSPDateRoll(DateNextMonth,DP_DAY,((@DATEPART(MyDate1,DP_DAY) * -1)+1));

    Much easier!

    We’ve also got the Planning equivalent of @DateDiff in @HSPDateDiff. This gives us a the difference between two planning dates – using the same functional format as DateDiff but using Planning Dates
    NumofWeeksDiff = @HSPDateDiff(MyDate1, MyDate2, DP_WEEK);

    As you can see – the default behavior of Essbase and Planning dates interaction is fiddly at best. However Oracle have definitely added some new functionality in recent versions that gets over most of the underlying issues.

    In the next post we’ll look at some of the & Cloud Functions that provide ‘real’ date functionality, and basically allow for pretty much any work with dates.

    Finally – in researching this post I’m heavily indebted to the following authors whom have written excellent articles about dates (and a variety of other things!) Check them out.
    Jason Biard
    Alp Burak Beder


    Time and Dynamic Date Factors

    As it is with most things, you start writing a blog post about something, and two weeks, 4 environments and 16 test cases later it’s morphed into 3 blog posts.

    So as follows:

    1. In this post, a useful tip about combining variables, dynamic calcs and scripts to deal with start and end Planning dates in Essbase
    2. Coming up: a multi-page rant about time, leap seconds, the amount of acid the unix developers took in the 70’s and essbase vs planning time. Also included is the different options you have to model dates.
    3. Finally: a short investigation of the ‘new world’ of custom HSP functions for dealing with dates

    Hang about, it’s going to get fun.

    A quick side note: To my American audience – in this post I will not be using the arbitrary retarded roller coaster of US date formats, instead I’ll generally be using the significantly more ‘standardised’ dd\mm\yyyy format or yyyymmdd as needed. In interests of fairness, I’ll probably flag the ones that are potentially confusing, but rest assured, I’m not creating new months.

    The concept of time in multi dimensional databases sucks. As soon as you’ve split time across two different dimensions (Years & then Months in Periods is a standard) you run into cross dimensional problems – now you need to reference multiple dimensions every time you write a single query, but how do you come up with a method to model data across multiple dimensions that is easy and consistent?

    Clients don’t help either! Whether it’s an employee costing app, a capital depreciation model, or even a simple balance sheet model – users are going to want to enter and use dates across a spectrum of data.

    So, you’ve got a problem. How to model the impact of ‘something’ across multiple dimensions in Essbase while meeting the three golden rules:

    1. Performance
    2. Ease of use
    3. Simplicity of maintenance


    So, we’ve got our requirements, lets build out a specific example. FTE – modelling Full Time Equivalent across periods to drive Salary Cost.

    A really basic example is shown here.


    • FTE value sourced from Payroll
    • Start Date sourced from Payroll (formatted as YYYYMMDD – straight from Planning)
    • Contract End date sourced from Payroll (you’ll note it’s the middle of the month)

    So, what we want to do is take the FTE data loaded in BegBalance and push the FTE value into the correct periods.

    Starting from the beginning then, we’re going to want to create some outline members to provide reference points. I’ve created these as dynamic calcs – primarily to make them appear at all levels and hence make them easier to use.


    And for my first trick…


    This is a dynamic calc member that will populate the first date of every month in every month in our application. It’s pseudo dynamic in that you have to set the first year (in our case FY13) – but it will continue to work as we add years. @COUNT on a @CURRMBR is not very efficient, but it’s more than fast enough for our purposes as a dynamic calc.


    And for my second trick…
    Exactly what it says it is. A count of the number of days in the month.

    Also – do you see my fancy formula in there to deal with Feb 29, except for century leap years every 100 years? Yeah – that’s me – future proofing code for the year 2100…


    Nothing up my sleeves…

    Okay – so now we’ve got these members, let us put them together in a script:

    Taking it from the top:
    Standard fix statements to get us to level 0. Nothing particularly fancy in there with the exception of VAR commands. You can see I’m setting up three variables: StartDate, EndDate and Datefactor for use in this script.

    Starting to define the variables. Startdate and EndDate are set to the member values from earlier on while EndDate is defaulted to the upper end of the range if it’s missing. You’ll note it’s not actually a real date. This is for many reasons – some of which will be clarified in the next post – instead we’re going to use the Planning dates as numeric ranges for this calculation.

    Defining out some case statements for the ‘DateFactor’ variable. You’ll see how the code uses a combination of the FirstDayinMonth dynamic calc member with the DaysinMonth calc member to define a range of days in the month. One further nice feature of this is making DateFactor=#missing for the periods that shouldn’t have data. This way, when you calculate the value (see next section) you end up with #missing rather than creating a zero value block.

    Final step – just simple multiplication. Datefactor has calculated a ratio in every period (a combination of Month and Year) and you can then multiply it by the value to generate monthly values.


    Multiply it out and Put a Bow on it

    So you can see the output of the calculation – we’ve now got FTE’s in each period and 0.5 FTE in the final period as per the date ranges.

    Lets come back to our original requirements and see how we did.

    1. Performance – it’s lightening quick. No stored members and everything calculated on demand in the script makes this about as fast as it’s possible to be
    2. Ease of use – The same concept of ‘datefactor’ can be reused everywhere you require dates to impact period calculations
    3. Simplicity of maintenance – This code piece is basically drop-in and forget. You only need to setup the variables to your start and end dates and then calculate the output at the end. Only potential issue is block creation in outer years as I’m using a dense member, but you could look at reworking the function to make it a sparse member assignment (using scenarios is an oft overlooked option)

    As always, full code available at the bottom. Note you’ll have to update all of the dimensionality to your own application!

    Hope that helps some of you – check back soon for more fun with dates.



    /* Fix on future years, level zero periods and the Scenario variable */
    /* Fix on Level 0 Sparse */
    FIX(@Relative(Organisation,0),@RELATIVE(Activity,0),@RELATIVE(“Position Employee”,0),@LEVMBRS(“Employee Type”,0))
    /* Define a set of variables to handle dates */
    VAR startdate = 0;
    VAR enddate = 0;
    VAR datefactor = 0;
    /* Open a calc block – this allows interdependent formulas as well as the variables */
    IF (&Assump_Year->”BegBalance”->”Start_Date”!= #MISSING)
    startdate = &Assump_Year->”BegBalance”->”Start_Date”;
    IF(enddate == #missing)
    enddate = 99999999;
    /* 5 case statements exist for datefactor
    1: start and end dates are fully inclusive of current month
    2: start and end dates are fully exclusive of current month
    3: start date is within current month, end date is inclusive of current month
    4: start date is inclusive of current month, end date is within current month
    5: both start and end dates are within current month
    /* 1: start and end dates are fully inclusive of current month */
    IF(startdate=”FirstdayinMonth”+1 AND enddate=”FirstdayinMonth”+”DaysinMonth”)
    /*2: start and end dates are fully exclusive of current month */
    ELSEIF(startdate “FirstdayinMonth”+”DaysinMonth” OR enddate”FirstdayinMonth”)
    /*3: start date is within current month, end date is inclusive of current month */
    ELSEIF(startdate “FirstdayinMonth”+1 AND enddate=”FirstdayinMonth”+”DaysinMonth”)
    datefactor=(“DaysinMonth”-(startdate – @INT(startdate/100)*100) + 1)/”DaysinMonth”;
    /* 4: start date is inclusive of current month, end date is within current month */
    ELSEIF(startdate”FirstdayinMonth” AND enddate”FirstdayinMonth”+”DaysinMonth”)
    datefactor=(enddate – @INT(enddate/100)*100)/”DaysinMonth”;
    /*5: both start and end dates are within current month*/
    ELSEIF(startdate “FirstdayinMonth” AND enddate”FirstdayinMonth”+”DaysinMonth”)
    datefactor=((enddate – @INT(enddate/100)*100)-(startdate – @INT(startdate/100)*100) + 1)/”DaysinMonth”;
    /* Calculate FTE using the datefactor */
    FTE_Payroll = “FTE_Payroll”->&Assump_Year->”BegBalance” * datefactor;

    EAS copy of a BSO Planning Cube post

    G’day All,

    A very quick and very essbase hacky one today (since we’re all essbase hackers at heart!)

    From onwards you can no longer take an copy of a Planning BSO Application through EAS. You can still copy ASO apps and Essbase only BSO apps, but can no longer take a quick snapshot of a Planning app to do some modelling in. The function is greyed out when you right click on the App.
    11111 - Copy

    11122 Onwards - No Copy

    Because that is still quite useful to do, particularly when:

    • testing data changes
    • reviewing historical recalculations so you can see a before and after
    • making sure my code actually works and doesn’t set fire to everything

    Below is some Maxl code to get around it. This will copy the outlines, rules, scripts and data across. Note the 8 character limit for both apps and databases.

    Create Application ‘TargetAppName’;
    Create Database ‘TargetAppName’.’SourceDB1′ as ‘SourceAppName’.’SourceDB1′;
    Create Database ‘TargetAppName’.’SourceDB2′ as ‘SourceAppName’.’SourceDB2′;
    Create Database ‘TargetAppName’.’SourceDB3′ as ‘SourceAppName’.’SourceDB3′;

    Best run from the MAXL editor in EAS.

    This is a staggeringly inglorious hack, so treat it with caution. It has obviously been removed for a reason – though this may simply be to remove the confusion of planning/essbase apps.

    You also need to be aware of your licensing agreement to make sure you’ve got coverage for Essbase only applications.

    Finally, and obviously, I strongly recommend running this only in your dev/test environments. But as a great poet once said “there’s no test like production”


    ps: I’m kidding. Don’t run this in Production. Seriously. They are Planning apps remember?? That’s why you’ve got LCM. Read the previous post. Sheesh.

    Error Handling the Max Power(shell) way

    Let’s start this off with a bang

    If, like most of us, part of your day to day role is support – one of the key advantages of using ODI in your overnight process is to have the full gambit of ODI error handling available to you. Errors are nicely caught, with email warnings for failures in batch processes basically a default state.

    However, what happens if ODI isn’t part of your implementation? What happens if the back end is basically a bunch of batch/maxl scripts hanging together (no matter how elegant it is). If it is the latter, at best you’re unlikely to have an easy method to trap errors and send notifications to the support team and at worst, you may not have any automated notification at all!

    So your options are:

    1. Hope against hope that you never ever have any errors in your overnight processes
    2. Write your own error handling piece and include it in the processing

    Rolling Your Own

    So you’ve decided to steal cable write your own. While this process would be possible in batch – certainly you’re likely to be better off selecting a more advanced language with slightly better handling of strings (See also: Java/Perl/Powershell/Cygwin/VBA).

    I’ve gone with Powershell: slightly because I’m generally on Windows environments, somewhat because it’s fairly decent at string manipulation and mostly because it’s one of the few languages I know.

    Code Overview

    So – to start with, basic documentation and variable structure. I’ve written this to poll through a folder containing log files (rules file errors, maxl return errors and an archive copy of all of the Essbase Log files from the previous day). These have been moved to this folder by earlier maxl/batch steps, mostly by using STDERR outputs.



    For each of the files in the log folder it scans for any lines containing errors and pushes the entire line to a central log file.

    I’ve added suppression to deal with a majority of ‘false positive’ errors – primarily related to errors in the Essbase log for Smartview user-side errors like ‘you’ve-got-suppression-on-by-default-and-your-top-level-retrieve-is-blank’ (man, if I had a nickel for that one…).


    Letting you Know

    Finally it checks if the log file isn’t blank and emails it through to the email addresses listed. The primary reason I’ve got the error suppression above is to make sure you’re only receiving automated emails when something has gone wrong. I find too many false positives in your inbox mean that the actual errors occasionally get lost in the underflow.

    Finally – the full code in all it’s glory. 20 Lines of working code all up!

    # Name: Hyperion Mail Error script
    # Purpose: Sends an email to the support team if there are any errors in the overnight
    # Author: Peter Nitschke - M-Power Solutions

    # Change Control
    # When Who What
    # 19/07/2014 PN Created

    # Script is built as a powershell command to be called at the end of the overnight
    # Script will poll through the available files in the log locations folder searching for errors
    # Some errors are suppressed (var SUPPRESSEDERROR), additional error codes to be suppressed can be added to this variable
    # If any errors are found, it will automatically send an email through to the support team

    Set-Variable ERRORFOLDER “..\Errors\OvernightErrors”
    Set-Variable ERRORLOG “..\Logs\OvernightErrors.log”

    #1020010 No data was generated: Suppress Missing = [%s], Zeros = [%s]. Sheet not overwritten.
    #1020011 Maximum number of rows [%s] exceeded [%s].
    #1020012 Maximum number of columns [%s] exceeded [%s].
    Set-Variable SUPPRESSEDERROR “1020007”,”1020011″,”1020012″

    # Create or clear the status file
    new-item $ERRORLOG -type file -force

    #For all of the error log files
    (Get-ChildItem $ERRORFOLDER |

    #Create array of objects
    #Define the name and content
    $name = $
    $content = Get-Content $_.FullName
    #look for errors in the file
    switch(($content | Select-string “Error” | Select-string $SUPPRESSEDERROR -NotMatch | measure | select -exp count) -gt 0) {
    #if errors found, print the filename and the error
    True {Echo $name >> $ERRORLOG
    $content | Select-string “Error” | Select-string $SUPPRESSEDERROR -NotMatch >> $ERRORLOG
    Echo “” >> $ERRORLOG}
    #Once the loop is finished, check if the status file has any content and email if true
    If ((Get-Content $ERRORLOG) -eq $Null) {
    #Calling send email function
    $smtp = “”
    $emailto = “”
    $emailfrom = “”
    $subject = “Hyperion Overnight – Errors” + (get-date -f “dd/MM/yyyy”)
    $body =(get-content $ERRORLOG | Out-string)

    send-mailmessage -to $emailto -from $emailfrom -smtp $smtp -subject $subject -body $body

    As an aside, I’m aiming to provide full code snippets as often as possible. Most of my learning over the years has been picking up code from everywhere and jerry-rigging it all together. (Subnote: greatest wikipedia ‘Not to be confused with’ ever!). Please feel free to use, send to colleagues, share with friends, or claim as your own to help you pick up ladies.

    However, standard rules of never putting something into your environment that you haven’t first tested and understood apply. I’m not to blame if it takes these lemons and burns your house down.


    Hopefully this has given you some ideas as to adding basic error handling to your own environments, allowing you to both rest easier at night and feel like you’re always one step ahead of any problems as they occur!