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.

• 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

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

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.

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