Configure DW generated 4-4-5 calendar and 13 period calendar

Author: Chuan Shi

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.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Phil Wang-Oracle

Search

Categories
Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
      
Today