OBIA Training: Configure summary calendar file (file_summary

Purpose and Audience

This article explains how to configure an ETL source file for time dimension from PeopleSoft (PSFT) adaptors, i.e., file_summary_calendar.csv. This file is needed to populate the fiscal quarter information for fiscal calendars sourced from PSFT. It applies to all Oracle BI Apps customers who use PSFT sources.

Oracle BI Apps supports calendars of different natures, including Gregorian calendar, fiscal calendar, and enterprise calendar. Fiscal calendars are stored in W_MCAL_%_D data warehouse tables, where W_MCAL_PERIOD_D is the most important table (loaded from the corresponding staging table W_MCAL_PERIOD_DS). Among many attributes of this table is the MCAL_QTR column which stores fiscal quarter information. The mapping logic of this column for PSFT OLTPs requires some special design. In what follows, we explain what needs to be done to ensure MCAL_QTR being populated correctly.

Populate MCAL_QTR (fiscal quarter information) for PSFT sourced fiscal calendars

In PSFT, there are concepts of detailed calendars (stored in PS_CAL_DETP_TBL) and summary calendars (stored in PS_CAL_SUMP_TBL). Definitions of all calendars are stored in PS_CAL_DEFN_TBL, where one can check if a calendar is a detailed or summary calendar.

Detailed fiscal calendars are loaded into W_MCAL_%_D tables. However, the detailed calendars by themselves do not have information about fiscal quarter. Therefore, for MCAL_QTR, an ETL logic is used to correctly allocate each period of a given detailed fiscal calendar to one of the four fiscal quarters (so that its MCAL_QTR is populated), by using a summary calendar that corresponds to the detailed calendar. As a result, the prerequisite of this ETL logic is to correctly connect a detailed calendar to a summary calendar. However, such dependency is not available from the PSFT sources, and it is a customer decision that determines which detailed calendar should be associated with which summary calendar.

Therefore, in our design, we create a source file called file_summary_calendar.csv. Customers are required to configure this csv file by associating each applicable detailed calendar to an appropriate summary calendar (i.e., each row in the file is a relationship between a paired detailed and summary calendar). This csv file is loaded into a DW table called W_MCAL_PSFT_SUMP_CONFIG_G. Consequently, this table contains relationships of different detailed calendars and their summary calendars. It is in turn used to help populate MCAL_QTR in W_MCAL_PERIOD_DS.

To summarize, in order to have MCAL_QTR correctly populated for a detailed calendar, you need:

A detailed calendar defined in PSFT source;
A summary calendar defined in PSFT source that is appropriate for the detailed calendar;
Put an entry of the pair of detailed and summary calendar in file_summary_calendar.csv file.

Examples of how to configure the csv file is provided in the appendix.

Commonly known reasons for MCAL_QTR being unpopulated or populated incorrectly

A detailed calendar and its corresponding summary calendar should be synchronized perfectly in PSFT OLTP source data and their pair should be put in this csv file for MCAL_QTR to be populated correctly. However, not all detailed calendars need to be put into this csv file, because the fiscal quarter (MCAL_QTR) concept does not apply to some detailed calendars by nature (we will provide examples below). Unsynchronized detailed and summary calendars lead to MCAL_QTR being populated incorrectly or unpopulated.

There are four commonly known reasons of why MCAL_QTR is unpopulated or incorrectly populated for a fiscal period. They are either due to source data issues or due to configuration of the csv file. The four cases are:

Case 1: The fiscal quarter concept is not applicable to a detailed calendar. For example, if a detailed calendar is a year level calendar, then each row of this calendar in W_MCAL_PERIOD_DS is a fiscal year. It does not have a granularity lower than year. Therefore, it is not possible (or does not make sense) to populate quarter information to this calendar.

Case 2: Not all the years of a detailed calendar are covered by the corresponding summary calendar. Therefore, MCAL_QTR is null for the periods in those uncovered years. For example, the detailed calendar covers from fiscal year 1990 to 2020, while its paired summary calendar only covers from fiscal year 2001 to 2020. In this case, the periods between 1990 and 2000 will not have MCAL_QTR populated.

Case 3: The summary calendar is not suitable for that detailed calendar. This means that the quarter allocation ETL logic fails to correctly allocate periods of the detailed calendar into one of the four fiscal quarters.

Case 4: Entries of appropriate detailed and summary calendars are missing in the .csv file.

I provide below several examples of detailed calendars. Refer to the Comment column to see if they should be put in the csv file along with an appropriate summary calendar.

Detailed Calendar Examples:

When you have to create a summary calendar for one or more detailed calendars (so that they can be put in the csv file), the key is that in any given year, all the periods of the corresponding detailed calendar (so in a weekly calendar there are 52 or 53 periods) should be correctly allocated to the 4 fiscal quarters. This requires the summary calendar to have correct values in DETL_PERIOD_FROM and DETL_PERIOD_TO of PS_CAL_SUMP_TBL. For example, if you want to create a summary calendar for all your detailed weekly calendars, then the data entries in PS_CAL_SUMP_TBL of a given year (say 1990) should look like

This is because this summary calendar will be used to assign fiscal quarters to those weekly detailed calendars, and such a calendar has 52 or 53 periods (i.e., 52 or 53 weeks) in a given fiscal year. So, the DETL_PERIOD_FROM and DETL_PERIOD_TO values indicate that periods 1-13 (of a weekly detailed calendar) belong to fiscal quarter 1, periods 14-26 belong to fiscal quarter 2, etc. As you can see from this example, this summary calendar can be used for multiple weekly detailed calendars. Therefore, you don’t have to create different summary calendars for different weekly detailed calendars. However, for each detailed weekly calendar, you have to put an entry for it, together with this summary calendar, in the csv file.

As a comparison, if instead you need to create a summary calendar used for monthly detailed calendars, you may expect to have its DETL_PERIOD_FROM and DETL_PERIOD_TO (for a given year, say 1990,) as:

This is because this summary calendar is used to assign quarters to those monthly detailed calendars, and a monthly calendar only has 12 periods (i.e., 12 months) in a given year. So, the DETL_PERIOD_FROM and DETL_PERIOD_TO values allow periods 1-3 (of a detailed monthly calendar) to have fiscal quarter 1, periods 4-6 to have fiscal quarter 2, etc. Again, you can use this summary calendar for multiple monthly detailed calendars that you may have, but each of them requires an entry in the csv file.

Appendix: Configuring file_summary_calendar.csv

This csv file has the following header:

Description,Contains static fiscal period (weeks) information,,,,,,,,,,,,

Examples,The flat file can provide data for the FISCAL_YEAR- FISCAL_MONT- FISCAL_WEEK_NUM and FISCAL_WEEK_START_DT as 1996- 03- 14- and 03121996,,,,,,,,,,,,

Last Updated By,Siebel Analytics R&D,,,,,,,,,,,,

Last Updated On,10/17/2001 20:13,,,,,,,,,,,,

DETAIL_CALENDAR_SETID,DETAIL_CALENDAR_ID,SUMMARY_CALENDAR_SETID_QTR,

SUMMARY_CALENDAR_QTR,SUMMARY_CALENDAR_SETID_YEAR,SUMMARY_CALENDAR_YEAR,

SUMMARY_CALENDAR_SETID_MONTH,SUMMARY_CALENDAR_MONTH,SUMMARY_CALENDAR_SETID_HALF,

SUMMARY_CALENDAR_HALF,W_INSERT_DT,W_UPDATE_DT,TENANT_ID,X_CUSTOM

The first four columns are the most important ones. Suppose that you have a monthly detailed calendar whose SETID = 'SHARE' and CALENDAR_ID = '01'; as well as a weekly detailed calendar whose SETID = 'SHARE' and CALENDAR_ID = '02'. Their corresponding summary calendars have SETID = 'SHARE' and CALENDAR_ID = 'QM', and SETID = 'SHARE' and CALENDAR_ID = 'QW', respectively. Then, you need to put the following two entries in the csv file (after the header):

SHARE,01,SHARE,QM,,,,,,,,,,

SHARE,02,SHARE,QW,,,,,,,,,,

And therefore, your final csv file becomes: