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

A History of Dates, Essbase and Why Unix time sucks

Summary
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:

EDate(MyDate1,10)

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

MyDate2=EDATE(MyDate1,1)-DAY(MyDate1)+1

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

MyDate2=EOMONTH(MyDate1,0)+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 */
NewDate2(
/* 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 */
ELSE
	NewDate2 = (yr*10000) + ((mth+1)*100) + 1;
ENDIF

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

NewDate1inSecs=@TODATEEX(“dd/mm/yyyy”,@HspDateToString(“NewDate1”));

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

Conclusion
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 11.1.2.4 & 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

Cheers,
Pete

Time and Dynamic Date Factors

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

Time
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

Requirements

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.

StartPoint

  • 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…

FirstdayinMonth
FirstDayInMonth

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…
DaysinMonth
DaysinMonth
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:
Script_1
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.

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

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

Script_4
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
FinishingPoint

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.

Cheers

Pete

SET UPDATECALC OFF;
SET AGGMISSG ON;
/* Fix on future years, level zero periods and the Scenario variable */
FIX(@IRSIBLINGS(&Year_Curr),@RELATIVE(“Yeartotal”,0),Rolling,Final)
/* 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 */
“FTE_Payroll”(
IF (&Assump_Year->”BegBalance”->”Start_Date”!= #MISSING)
startdate = &Assump_Year->”BegBalance”->”Start_Date”;
enddate=&Assump_Year->”BegBalance”->”Contract_End_Date_Payroll”;
IF(enddate == #missing)
enddate = 99999999;
ENDIF
ENDIF
/* 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”)
datefactor=1;
/*2: start and end dates are fully exclusive of current month */
ELSEIF(startdate “FirstdayinMonth”+”DaysinMonth” OR enddate”FirstdayinMonth”)
datefactor=#missing;
/*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”;
ENDIF;
/* Calculate FTE using the datefactor */
FTE_Payroll = “FTE_Payroll”->&Assump_Year->”BegBalance” * datefactor;
);
ENDFIX
ENDFIX