Friday 16 November 2018

PBCS Cheap Tricks – Quick and Easy Backup Process


The November 2018 PBCS update for EPM Automate has come with a neat new command that can really streamline your rolling backup process. Introducing: renameSnapshot. Here’s the definition, straight from the horse’s mouth

As you will know, PBCS creates a daily snapshot during your maintenance window, the timing of which you can manage using the PBCS Application Settings. This recently got its own card in PBCS:

The daily maintenance window creates a backup of your application called the Artifact Snapshot. As Oracle suggests above, if you rename this backup using a simple EPM Automate script with a timestamp, then you have a daily rolling backup process stored entirely in the cloud.
How many backups will be kept? Again, straight from the horse’s mouth:

So, you have two limitations. The maximum size of the pod cannot exceed 150GB, and snapshots will be removed after 60 days regardless of size. Usually a backup will be smaller than the true application, so you can realistically expect Oracle to keep at least two weeks’ worth of rolling backups.

So how do we recommend using this new tool? My suggestion would be to move your daily rolling backup to the cloud, and simply download the snapshot once a week instead, on a consistent day. This reduces the storage space requirement on your automation server which your IT team will thank you for, and makes it much faster to restore recent backups which your users will thank you for.

Here’s my recommendations for the EPM Automate parts of both scripts. We have some additional email functionality and error checking which I’ve stripped out for ease of understanding:

Daily Backup Script (run after your maintenance window every day except Sunday)
call epmautomate login %USER% %PASSWORD_FILE% %URL% %ID_DOMAIN%
call epmautomate renamesnapshot "Artifact Snapshot" "~Backup %YYYY%-%MM%-%DD% 1:00am"
call epmautomate logout
Weekly Script (run only on Sundays, the same script as above with a line inserted before logout)
call epmautomate downloadfile "~Backup %YYYY%-%MM%-%DD% 1:00am"

I’ve preceded the new name with ~ as this is after the alphanumeric characters in the Unicode alphabet, so the backups will neatly appear at the bottom of your snapshot window. They’ll also be ordered nicely by year, month and day with the most recent at the bottom. Rather than use the current time when the script is run, I hardcode in our maintenance snapshot window time so there’s no confusion as to when the backup refers to.

This creates the nice, stacking system at the bottom of your screen.

Fun Fact – as you’ll see from my 15th November backup (which I created myself to simulate multiple days running, please forgive me), you can’t manually rename a snapshot to contain a colon, however you can do it via EPM Automate. This adds protection against cheeky users messing with your backups.

So there you have it! You’re now protected against accidental data clears and protected against Oracle suffering a critical failure as you can restore one of your saved backups. Here at Brovanture we can set this up for you with additional controls with lovely confirmation emails to keep you confident in your backup procedures:


As ever, drop me a comment below if this has helped you at all!

Cheers,

Mike

Wednesday 14 November 2018

PBCS Cheap Tricks – Dealing with Dates and Depreciation.


Dates. They’ve been the bane of many a Hyperion Administrator’s life for about 20 years now and this is mostly due to the inconsistent and unfriendly business rule formulae surrounding them. The difficulties of dealing with dates makes calculating something like depreciation or amortisation very awkward, particularly when the same calculation is a dream to calculate in Excel.
As an example, let’s look at a lease with the following characteristics (you’ll need to calculate this for all leases in IFRS16 going forward so we may as well start now):

Lease Start Date: 1st January 2019
Lease End Date: 31st December 2028
Annual Rent: £10,000

For Depreciation, that's all we really need. We'll make some broad-brush assumptions that it will depreciate in every period that the start date falls in, starting from 01/2019 and will reach a £0 asset value in 12/2028. This gives it a life of 10 years x 12 months = 120 months. Because we are simplifying and not considering the present value of the rental payments, we assume the Initial Asset Value is 10 x £10,000 = £100,000

(FYI - for IFRS16 you'll also need an interest rate and I'll explore how you can achieve that in Hyperion Planning or PBCS in future blogs - the maths is really neat)

For now, all that means is we need to stick a depreciation amount of -£833.33 into every month between the start and end date and we will achieve our simple goal. So how do we calculate the correct dates in PBCS and match up the months we want to the life of the lease?

The answer is to define two accounts, Period Start Date and Period End Date which will both be Date type members. We will populate these members with the start and end date of each period and use them as reference points for our calculations. Seems too simple to be true right? If it's simple and it works, don't mess with it.

However, manually keying in the date is rather inelegant and frankly I'm disappointed in myself that for a while that's exactly what I did; until my colleague Alin came up with a brilliant solution. The key starting point is recognising how Hyperion loads in dates. They are loaded in the format YYYYMMDD which the clever of you will notice is an 8-digit number. You'll also be aware through a strong primary school education that months always have a consistent number of days (except February but we'll come to that). Therefore, it stands to reason that we can calculate the YYYYMMDD format number for any date.

The logic starts by defining a variable, that I usually define as &IFRS16_FIRST_CALENDAR_YEAR and set it to, for example, 2018. That is the number 2018, not the member FY18. I'll usually also have a sister variable &IFRS16_FIRST_YEAR with the value "FY18". You can work the calculation with any starting point but if you make it match your Year substitution variable then it's simpler to maintain.

We also need to define a Year Number, which we will set to 0 for the &IFRS16_FIRST_YEAR and increment by 1 every following year. For simplicity of calculation I just evaluated this in BegBalance but for optimal calculation speed you may want to put it in every period.

/*Initialise the Year Number in the IFRS16 FIRST YEAR member */
       FIX(&IFRS16_FIRST_YEAR,"BegBalance")
        "Year Number"(
        @CREATEBLOCK("Working");
       "Working" = 0;
        )
    ENDFIX

 /* Run a prior function to roll the year number forward. */
   FIX(@RSIBLINGS(&IFRS16_FIRST_YEAR),"Working","BegBalance")
       "Year Number"(
              @PRIOR("Year Number"->"BegBalance",1,@DESCENDANTS("All Years"))+1;
       )
   ENDFIX


We then use the following business rule to calculate the Period Start Date for January:

FIX ("Period Start Date")
/*For this installation, Jan FY18 = Jan-18 so we take &IFRS16_FIRST_CALENDAR_YEAR (2018) + Year Number (0) to get 2018, multiply it by 10000 to get 20180000 and add 101 to get 20180101 which is 1st January 2018. */
/*The next year, we will get &IFRS16_FIRST_CALENDAR_YEAR (2018) + Year Number (1) to get 2019, multiply it by 10000 to get 20190000 and add 101 to get 20190101 which is 1st January 2019. */
       Jan (
       (&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 101;
        )
ENDFIX
/*Similarly, we take &IFRS16_FIRST_CALENDAR_YEAR (2018) + Year Number (0) to get 2018, multiply it by 10000 to get 20180000 and add 131 to get 20180131 which is 31st January 2018. */
FIX ("Period End Date")
       Jan (
       (&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 131;
       )
ENDFIX

This approach works in the same way for all months except for Period End Date of February, which as we know differs on a leap year. Fortunately, Oracle have done us a solid with the @CalcMgrIsLeapYear function.

Feb (
      IF (@CalcMgrIsLeapYear((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 228))
               (&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 229;
        ELSE
              (&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 228;
        ENDIF
)                                             
You can, of course, bring all the start dates together into one member formula and do the same for each of the end dates, and since it's fairly trivial to follow the logic I will do you all a solid and present the full code below:

FIX ("Period Start Date")
        Jan ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 101;)
        Feb ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 201;)
        Mar ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 301;)
        Apr ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 401;)
        May ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 501;)
        Jun ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 601;)
        Jul ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 701;)
        Aug ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 801;)
        Sep ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 901;)
        Oct ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1001;)
        Nov ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1101;)
        Dec ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1201;)
ENDFIX

FIX ("Period End Date")
        Jan ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 131;)
        Feb (IF (@CalcMgrIsLeapYear((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 228))
                (&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 229;
            ELSE
                (&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 228;
            ENDIF;)
        Mar ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 331;)
        Apr ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 430;)
        May ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 531;)
        Jun ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 630;)
        Jul ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 731;)
        Aug ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 831;)
        Sep ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 930;)
        Oct ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1031;)
        Nov ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1130;)
        Dec ((&IFRS16_FIRST_CALENDAR_YEAR + "Year Number"->"BegBalance") * 10000 + 1231;)  
ENDFIX

This generates the start and end dates for you for as many years as you have in the model:


Back to the point of the post, which was simple depreciation. Now that we have these weapons in our arsenal, we can calculate depreciation with ease. Simply load the lease start date, lease end date and asset value using your preferred method, and you can simply use the > and < operators to compare dates. For example:

IF("Lease Start Date"->"BegBalance" >= "Period Start Date" AND "Lease Start Date
"->"BegBalance" <= "Period End Date") - will return Jan-19 only
IF("Lease End Date"->"BegBalance" >= "Period Start Date" AND "Lease End Date"->"BegBalance" <= "Period End Date") - will return Dec-28 only
IF("Lease End Date"->"BegBalance" >= "Period Start Date" AND "Lease Start Date"->"BegBalance" <= "Period End Date") - will return Jan-19 to Dec-28 inclusive
IF("Lease End Date"->"BegBalance" > "Period End Date" AND "Lease Start Date"->"BegBalance" < "Period Start Date") - will return Jan-19 to Dec-28 exclusive

You can have a lot of fun with these comparators, because by using the Jan-19 to Dec-28 inclusive option, your rule has identified all the months that the depreciation calculation should place data into.

For a more complex solution you'll also need to calculate the Lease Life Remaining. This corresponds to the number of months remaining from the current date.

FIX (&IFRS16_CURRENT_YEAR, "BegBalance")
"Lease_Life_Remaining"(
/*If we don't have a Lease End Date then we will need to set this to missing*/
IF("Lease End Date" <> #Missing)
/*Here we use @CalcMgrDateDiff to find the number of months between the period end date and the lease end date. If this is less than the total lease life, we use this as the remaining life*/
       IF (@CalcMgrDateDiff("Period End Date"->&IFRS16_CURRENT_MONTH,"Lease End Date","month") < @CalcMgrDateDiff("Lease Start Date","Lease End Date","month"))
/*This evaluates as true in the situation where a lease started prior to our first period of interest, so depreciation must be applied retrospectively taking into account the depreciation that already happened*/
       @CalcMgrDateDiff("Period End Date"->&IFRS16_CURRENT_MONTH,"Lease End Date","month");
      ELSE
/*Otherwise, we use the Lease Life (in our example 119 months will be calculated by the formula.)*/
      @CalcMgrDateDiff("Lease Start Date","Lease End Date","month");
       ENDIF
ELSE
#Missing;
ENDIF
)
ENDFIX

So, at this point, we have the months that depreciation should be calculated in, the total asset value to depreciate, and the lease life remaining. Therefore, we simply need to place the depreciation in the correct periods.
We need to add 1 to our Lease Life Remaining to account for the fact that our model depreciates in the first month too - this can be easily configured if you prefer not to do this. The Initial Asset Value was simply the payments multiplied by the years.

"Depreciation"(
IF "Period End Date" >= "Commencement Date"->"BegBalance" AND "Period Start Date" <= "Termination Date"->"BegBalance)
(Initial Asset Value" -> "YearTotal" -> "All Years") / - ("Lease_Life_Remaining" -> &IFRS16_CURRENT_YEAR -> "BegBalance" + 1);
ENDIF
)

I've used a simplified version of our custom built IFRS16 model (using an interest % of 0) to show you how this will appear in PBCS.

If you want to learn more about our IFRS16 model in PBCS or on-premise Hyperion Planning, I’ll be posting more in-depth blogs on the calculations for some of the interesting parts including the present value, calculating the liability and visualising the different possible effects of interest rates, all through using the power of Essbase.

As always, leave a comment below if this helped you at all.