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

However, companies’ financial administrations do not necessarily operate on the calendar structure, often using tax periods that may not align with the calendar. There are three main variations:

• A company’s fiscal periods may be synchronized with calendar months, resulting in twelve periods that each start on the first day and end on the last day of the month.
• Some organizations use a broken fiscal year; for example, Japan’s fiscal year often starts on April 1. If fiscal periods still span full calendar months, minimal adjustments are needed; we only need to map fiscal period 3 to calendar month 6. This time structure can then easily align with the calendar.
• Another situation arises when a company uses 13 fiscal periods of four weeks each, so the periods do not neatly align with calendar months. If the fiscal period end doesn’t coincide with the month’s end but with the last workday, it presents more challenges.

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.

Challenges in Oracle BI

The last scenario, where periods do not match calendar months, poses a challenge for the standard functionality in Oracle BI. The time structure in Oracle BI is designed to roll periods consistently down to underlying data. For instance, the Ago function expands a specific period to its corresponding dates, allowing the same set of dates from the previous year to be selected. If fiscal periods in different years do not end on the same date, the YearAgo function cannot provide comparable data.

I encountered this situation during a recent Oracle BI implementation. The client naturally wanted a working solution—and there is one:

Solution: Using Day-Level Transactions

For data like journal entries and logistical transactions, working with dates usually works well. Each transaction can be associated with a specific date, which can easily be compared to the same date from the previous year. The fiscal structure can then be aggregated into its corresponding period and fiscal year. However, for balance tables, such as JD Edwards General Balance and Fixed Asset Balance, which only have the end date of a fiscal period, additional steps are required.

Alternative to Oracle BI’s Date/Time Functions

While it can be difficult to abandon Oracle BI’s advanced date functions, this approach offers a more stable solution. For specific situations, using the middle day of the fiscal period is an option. But with 13 periods of four weeks, this is often insufficient.

A Robust Solution

An effective approach is to expand Cadran’s period table (FQ09PER™) with columns for the fiscal year and fiscal period. A SQL script sets the correct values per calendar date based on JD Edwards’ fiscal-date setup, allowing for easy filtering on the current year, current period, and same period last year. The Ago function is thus unnecessary. This solution works flawlessly for profit and loss statements and various balance reports, with excellent performance as the database handles these functions via SQL.

Leap Years in BI – An Extra Challenge

Leap years can complicate BI analyses. For example, February 29 only occurs in leap years, making comparisons with other years challenging. The YearAgo function cannot show data for February 29 since that date did not exist in the previous year. Consequently, a leap year has 366 days instead of 365, which might mean more working days or sales days. For fair comparisons, data from a leap year could be adjusted, for instance, by correcting with a 365/366 factor.

In JD Edwards tables, such as Sales Forecasting (F3460) and tables dependent on Fiscal Date Patterns (F0008), it may be necessary to replace February 29 with February 28, allowing for consistent year-over-year comparisons.

A Practical Approach

One way to handle leap years is by avoiding Oracle BI’s time functions. Traditionally, you might use a filter, such as YearNr = YEAR(CURRENT_DATE). A common approach involves a column for current revenue and one for last year’s revenue, with the Ago function comparing year-over-year revenue. It often works just as well to filter current revenue and last year’s revenue via column filters, setting the first column to select this year and the second to select last year. By adding a filter for February, this comparison also works in leap years.

An alternative solution is to extend the date tables with a “previous year” column, containing last year’s date. For instance, February 28, 2015, could serve as the comparison date for both February 28 and 29, 2016. Although this may create a minor distortion, as one day from 2015 is compared to two days in 2016, it provides a practical solution for consistent comparisons without the YearAgo function. Adding an extra column with the previous year’s corresponding date (e.g., Julian Date Previous Year) allows for comparisons without special leap year logic. This can even provide performance benefits.

A Simplistic Solution

Another, though less ideal, approach is to exclude February 29 using a filter. This works for general comparisons like monthly revenue per product group but can cause discrepancies, especially in financial contexts.

Conclusion

Unfortunately, there is no perfect solution; a compromise is always necessary. The most consistent approach is often to focus on periods like yearToDate, comparing the period from January 1 to today with the same period last year. This helps avoid large discrepancies until the end of the year. In practice, we make corrections throughout the year, such as for holidays, which vary annually. Leap years are just one of many variables in this context.

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