Introduction
Oracle BI APPs provide two data warehouse generated fiscal
calendars OOTB. They are the 4-4-5 calendar (with three different disciplines,
i.e., 4-4-5, 4-5-4 and 5-4-4, and one of them can be chosen) and 13 period
calendar. These fiscal calendars can be populated according to the
configuration rule chosen by the customer, such as when such a calendar starts,
when it ends, and if customer wants to use 4-4-5, 4-5-4, or 5-4-4 discipline
for the 4-4-5 calendar. This is done by configuring file_mcal_config_g.csv,
which is used by the ETL logic. The purpose of this article is to explain how
to configure file_mcal_config_g.csv. To start with, we briefly explain the
4-4-5 calendar and the 13 period calendar.
4-4-5 Calendar
This calendar is in general used for weekly and period
analysis. Each fiscal year is composed of twelve 4-4-5 periods. A 4-4-5 period
is defined to be either 4 or 5 weeks long. A week can start with any day
(Sunday, Monday, etc) and ends on the 7th day following the start
day. For example, if customer starts weeks on every Monday, then all weeks end
on the following Sunday. A 4-4-5 calendar can use a 4-4-5 discipline, a 4-5-4
discipline, or a 5-4-4 discipline.
In a 4-4-5 discipline, the first period is composed of the
first 4 weeks of a given fiscal year. The next 4 weeks compose the second
period. The next 5 weeks compose the third period and so on. For example, the
first three periods of a 4-4-5 discipline is shown in Figure 1. (5-4-4 and
4-5-4 disciplines are defined similarly.)
1 Week = 7 days (the first period can start from any day –
configurable)
Figure 1. First three periods of a 4-4-5 discipline
A regular 4-4-5 calendar has 52 = (4+4+5 + 4+4+5 + 4+4+5 +
4+4+5) weeks.
13 Period Calendar
This calendar is used when period to period comparisons are
needed. (Note the 4-4-5 calendar is not good for this, since a 5 week period is
25% longer than a 4 week period.) It is mostly used in Retail. Each fiscal year
is composed of 13 periods, where each period is 4 weeks long. Again, a week can
start with any day (Sunday, Monday, etc) and ends on the 7th day
following the start day. Figure 2 shows the first three periods of a 13 period
calendar.
1 Week = 7 days (the first period can start from any day –
configurable)
Figure 2. First three periods of a 13 period calendar
A regular 13 period calendar has 13 periods where each
period has 4 weeks. Therefore, a regular 13 period calendar also contains 52
weeks.
52 Weeks vs. 53 Weeks
From the introduction above, we see that for both the 4-4-5
calendar and 13 period calendar, a regular fiscal year contain 52 weeks, or
equivalently 364 days. However, each Gregorian calendar year contain either 365
days or 366 days (in leap years). So, if we require all fiscal calendar years
(or the 4-4-5 calendar or 13 period calendar) to precisely have 364 days, there
will be accumulated gaps between the fiscal calendar cycle and the Gregorian
calendar cycle.
Therefore, what happen is that, for every several such fiscal
years that have 52 weeks, the difference between the fiscal calendar cycles and
Gregorian calendar cycles becomes 7 days. Once that happens, we allocate that 7
days to the last period of the current fiscal year. It means that in every
several fiscal years of 52 weeks, there will be a fiscal year that is 53 weeks,
where the last period of that fiscal year has one more week. For example, in a
4-4-5 discipline, the last period of a 53 week fiscal year will have 6 weeks,
instead of 5. In a 13 period calendar, the last period of a 53 week fiscal year
will have 5 weeks, instead of 4.
Whether a fiscal year has 52 or 53 weeks depends on the
start date and end date of each fiscal year. (For example, a customer can
choose to load the first fiscal year with 53 weeks.) The start date of the
first fiscal year is chosen by customers. The end date of the first fiscal year
as well as the start and end dates of subsequent fiscal years of the calendar
are determined by an ETL logic that uses a parameter called REFERENCE_DATE
which is also set by the customer. We discuss them next.
Configuring DW Generated Calendars
For the two DW generated calendars, customers are allowed to
configure the following:
- Start date of the first fiscal year of a given calendar
- End date of the last fiscal year of a given calendar
- Discipline of the 4-4-5 calendar
- Reference date that determines if a fiscal year has 52 or 53 weeks
The configuration is done by using the csv file named
file_mcal_config_g.csv, whose content is provided below:
Description,,,,,,,,,,,,Examples,,,,,,,,,,,,
Last Updated By,,,,,,,,,,,,
Last Updated On,,,,,,,,,,,,
CALENDAR_ID,CALENDAR_NAME,CALENDAR_CLASS,PERIOD_TYPE,CAL_ST_DT,CAL_END_DT,CAL_OFFSET,WEEK_ALLOCATION_RULE,REFERENCE_DATE,W_INSERT_DT,W_UPDATE_DT,TENANT_ID,X_CUSTOM
10000,4-4-5,Generated,4-4-5,20021229000000,20301231000000,0,4-4-5,1230,,,DEFAULT,0
10001,13,Generated,13,20021229000000,20301231000000,0,13,1230,,,DEFAULT,0
Customers are required to provide desirable values for both
4-4-5 calendar and 13 period calendar. The columns to be configured are:
The first three columns are straightforward and therefore we
only explain the last one. Our ETL logic assumes that the end date of a given
fiscal year must be within +/- 3 days of the REFERNECE_DATE. For example, if a
customer sets REFERENCE_DATE to 1230 (Dec 30), then the ETL logic knows that the
end date of any given fiscal year should be from DEC-27 (which is DEC-30 – 3
days) to JAN-02 (which is DEC-30 + 3 days).
Recall that a week starts with the day that the CAL_ST_DT is
on and ends on the 7th day following it. As a result, in this
example with CAL_ST_DT = 20021229000000 which is a Sunday and REFERENCE_DATE =
1230, the end date of a given fiscal year will be the unique Saturday that
falls between December 27 and January 2 inclusively. Once the end date of the
fiscal year is determined, the number of weeks is also determined.
Consequently, this is how the ETL logic determines if a given fiscal year has
52 or 53 weeks.
The table below shows the data for fiscal years from 2002 to
2014 for a 5-4-4 discipline calendar with REFERENCE_DATE = 1230. We can verify
that the MCAL_YEAR_END_DT of all these years fall between Dec 27 and Jan 02.
Some of the fiscal years have 53 weeks while the rest have 52 weeks.
No comments:
Post a Comment