Why the @SPLINE function is worth a look

The Spline function according to the Oracle documentation is defined as:

On the face of that - I'm not sure I would ever use it, all it does is take a data set and attempt to flatten the line to remove the outlier data points.  So what makes this useful? We can copy data points using @XRANGE, we can take one data point and assign it to multiple cells, and in doing so will create blocks in the target data set.  As it creates blocks in the target range we don't have to calculate these members independently, making the rule run quick.

So, before I start looking at the @SLPINE function, I will spend a bit of time looking at another function that makes this even more useful: @CalcMgrDateToString. I am going to assume we have a Period Dimension with months and a Year dimension separate to that, as is the case in 90% of standard PBCS implementations.

 So if we parse a data value of 20190320 in the @CalcMrgToString function we can get it to return a value in the SimpleDateFormat in Java. The two Java date formats I constantly use are MMM and yy. MMM will return Jan, Feb etc, yy will return the last two digits of the year.  This means we can write a function using a data value to return a member using the following syntax:

@MEMBER(@CalcMgrDatetoString(20190320,"MMM")) = "Mar"

and

 

@MEMBER(@CONCATENATE("FY",@CalcMgrDatetoString(20190320,"yy"))) = "FY19"

Now we have that, let's have a look at how we can use the @SPLINE Function:

First we are going to look at how we can take a single cell FTE and populate a range according to a start and end date.

Here is our starting point

Using a "No Year" - or base Year as we have called it, we enter our data variables; FTE, start date and end date.

To Populate all years going forward including creating blocks in those periods we can use the following script:

The results are as follows:

As you can see, the FTE is populated for all required periods just by fixing on FY10 we populate all years/months (in this model years dimension is sparse, as mentioned we don't have any block creation problems with this script).  Just because we can, I have included a section of code to calculate the proportion of FTE in the first and last periods. 
NB. the extra code is useful as the @SLPINE function requires two unique cells in the target, so if you have a start and end date in the same month it will not work.
     
     

 

Code

 This will give the following results:

Another useful feature of the @SPLINE functions is that we can "Copy" data using within an @XRANGE from a source to a target data set.

I use this when modeling employee leave as we don't calculate a salary for employees on leave. Copying the date range that the person is on leave to a member I call "FTE Before Leave", the following code runs this process.

Our starting point is as follows:

Entering a start and end date as shown:

Then on save in our data entry form we run the following code.

Leave Code

The code runs the following processes

1. Copies any pre entered FTE_Before_Leave back to FTE, so this assumes there is no leave and the FTE is restored to a full FTE
2. Clears out the FTE_Before_leave entered so it is a clean slate, this is done to ensure if leave is canceled the FTE returns to a normal FTE profile.
3. Using the start and end dates of leave populate the FTE_Before_Leave by taking a copy of the FTE for the months the person is on leave and assigns it to the FTE_Before_Leave member
4. Calculate the FTE for for periods when the person is on leave taking the portion of the month that the person is away away from the FTE, so if they are away for the entire month the FTE for that month is zero
5. Copies the leave dates to another period so next time the calc is run it can run step 1.

The results are as follows:

So as you can see the SPLINE function is handy, flexible and provided you keep the data set to a minimum a very fast function - and if you don't currently include it as part of your logic give it a go..