Oracle BI – Where is the time

Oracle BI (cloud or on-premise) has powerful functions for displaying data over time. Examples of this are:

• Compare previous periods (Ago)
• Medium past periods (PeriodRolling)
• Compare current month with the current year (ToDate)

The underlying engine is a well-designed dimension Time with associated hierarchy or structure.

The fact that there are some pitfalls in this has already partly become clear in my previous blog BI- A week in a life. It has already been briefly mentioned here that using the usual structure and hierarchy of a calendar will function particularly well. If we look at a date in the calendar, it can be rolled up perfectly to the corresponding month, quarter and year. The date and time functions that Oracle BI offers can be used perfectly.

Fiscal Periods

Company financial administrations do not necessarily work via the calendar structure, but use tax periods (possibly not connected to the calendar). Here we can recognize three variants:

1. The fiscal periods are synchronized one-to-one with the calendar months: Twelve fiscal periods are used that start on the first day of the calendar month and end on the last day of the calendar month.
2. The fiscal periods have a broken financial year. For example, Japanese companies start their fiscal year on April 1st. If you use whole calendar months, the suffering can be overseen. We only need to keep in mind that fiscal period 3 corresponds to calendar month 6. However, the end dates of the periods still correspond with end dates of calendar months and the time structure is still at our service.
3. The fiscal periods run straight through the calendar months. For example, if a company uses 13 fiscal periods of 4 weeks, the end and beginning of each fiscal period will run straight through the calendar. If the end of a fiscal period is not the end date of that month, but the end date is used in the last working week, then … that is … the real challenges start here.

In situations 1 and 2, there is virtually no concern: The fiscal time structure is equal to the natural calendar structure. However, leap years can cause unwanted effects. See the next section for this.

The latter scenario is directly in conflict with the standard functionality of Oracle BI. The time structure is always unrolled under water to a period of dates and comparison with previous years takes place via that hierarchy. Or: the Ago function will expand a specific chosen period to underlying dates to select the same set of dates in, for example, the previous year. If fiscal period 2017/04 ends on 27-04-2017 and tax period 2016/04 ends on 29-04-2017 then this will not yield comparable data for Oracle BI and the YearAgo function (for comparison last year) will go astray.

I encountered this incident with a recent implementation of Oracle BI. Of course the customer in question really wanted a good working solution. Of course it is there:

1. Details: Provide transactions at day level. This will be excellent for journal entries and logistic transactions. Each transaction can be traced back to a date and that date can be compared with last year. The fiscal time structure can roll up to the corresponding tax period and year. However: Balance sheets (eg JD Edwards General Balance F0902 and JD Edwards Fixed Asset Balance F1202) aggregate data on fiscal period and therefore only have an end date fiscal period (via JD Edwards Fiscal Date Patterns F0008), which can therefore be different per year.
2. Release the date / time functions of Oracle BI: Auw. This does equally. That beautiful advanced calendar features of Oracle BI just forget? Yes. That’s just the way it is. Functionally, it never comes out well. I have seen examples coming along, in which the solution was sought in the middle day of the fiscal period (ie, day 15 of each month). This may resolve some issues (such as Leap years below, and unequal end dates of tax periods), but with 13 tax periods of 4 weeks, this is not going well either.
   How? I myself have achieved the best results so far by providing the period table FQ09PER ™ (Cadran AddOn) with a column Fiscal Year and Fiscal Period. An SQL script sets the correct values for each calendar date based on the design of the Fiscal Date Patterns in JD Edwards (F0008). Now can very well be selected with so-called Column Filters on current year, current period and next year’s current period. The Ago function is not used. For Loss and Profit accounts and all sorts of balance sheets this works flawlessly. The performance is above expectation good, because these functions are executed in the database by the physical SQL statement, while the calendar functions take place in the Oracle BI Server itself.

Leap year

Another leap of a box in the world of BI is the leap year. There is suddenly the date February 29 and it is difficult to compare with last year. The YearAgo function will not show anything on the 29th of February. After all, the previous year is not a leap year. The whole year suddenly has 366 days and in fact one day more work / produced / converted. To compare figures on an annual basis, the data of a leap year should actually be corrected (by the factor 365/366) to be in line with normal years. The dates of a February from a leap year may then have to be corrected by 28/29.

Tables in JD Edwards, which work with an end date (fiscal) period, such as Sales Forecasting (F3460), or that are linked to the Fiscal Date Patterns (F0008) may have to be stripped of leap years. By replacing February 29 through February 28, good comparisons will be made with all years.

A solid approach is to let go of the time functions that Oracle BI offers (yes, sniff, really). Traditionally, a filter (like YearNr = YEAR (CURRENT_DATE) would work fine.) A column Revenue and a column RevenuePY (with an Ago function at year level) would be the conventional approach, but it is also very possible to work with Column Filters on the column Revenue and the column RevenuePY.This means that the first column selects the current year and the second column the previous year.Define this Column Filter with the period (eg MonthNr = 2) and a month February is easily compared, leap year or not.

A pragmatic approach could be to not use the YearAgo function, but to provide the table with all date columns (see FQ09PER ™) with a column last year. That column then contains the Julian Date of last year. At 29 February 2016 (julian date 116060), the value 115059 (28 February 2015) appears in the Last Year column. The same applies on 28 February 2016. This distorts things: After all, 1 day from 2015 is compared with 2 days from 2016 and to be pure the amount of 2016 should be halved. The YearAgo function can no longer be used, but via that column JulianDatePY itself will have to be retrieved in the BI model or in the Analytics. This seems to be a point of gain in performance-technical terms, although this is not subject to exclusive research. Provide the Period Table (FQ09PER) of a Julian Date Previous Year. This then becomes the date key with which it can be compared last year. For each leap year, it will then be necessary to look closely at julian-dates-previous-year at each julian-date-this-year. A nice explanation of this can be found in http://obiee101.blogspot.nl/2009/01/obiee-leap-year-challenge.html. This blog is about Oracle BI 10g, but the version of the software (or even the supplier) does not change this phenomenon.

A ‘dirty’ approach is to exclude the date February 29 by setting up a filter. With a bit of cleverness this can be made generic. However, this is as if this date does not exist and both the accountant and the tax authorities will like this. For generic comparisons between years at a higher level (such as Revenue per month per product group) this can give a clearer picture. A column Last Year will obviously never be able to compare well with last year if we look on February 29. That date is obviously not last year.

The conclusion is that there is no good solution. There is always an apple-pear comparison. The most pure method would be to completely let go of periods (such as month) and to work with a YearToDate (January 1st up to and including today’s current year and the same period last year). If, for example, we look at 1 March 2017, we will look at a period of 60 days. If we compare that period with the previous year, we will also look at a period of 60 days, although it will then run (by chance) until 29 February. Only at the end of the year on December 31st is it a bit crooked. But having seen a whole year has to do with more correction layers, such as holidays, which happen to happen one year at the weekend and the other year in the middle of the week.

Profit & Loss from February 2017 in Oracle BI with data from JD Edwards. AmountPY therefore refers to February 2016 (being the leap month).

Author:  Rick Brobbel
BI Consultant bij Cadran Consultancy