Wednesday Jan 22, 2014

Configuring Enterprise Calendar for Oracle BI Apps Time Dimension

Author: Chuan Shi

Introduction

One of the key common dimensions of Oracle BI Apps is the Time dimension. It contains calendars of different natures to support different types of analysis within various subjects in Oracle BI Apps. Different types of Calendar include:

  • Gregorian Calendar
  • Fiscal Calendar (multiple)
  • Enterprise Calendar (unique enterprise wide)

The Enterprise Calendar (or reporting calendar) enables cross subject area analysis. The Enterprise Calendar data warehouse tables have the W_ENT prefix. Within a single BI Apps deployment by the customer, only one fiscal calendar can be chosen as the Enterprise Calendar. The purpose of this blog is to explain how to configure the Enterprise Calendar.

Configure the Enterprise Calendar

The Enterprise Calendar can be set to one of the OLTP sourced fiscal calendars, or to one of the warehouse generated fiscal calendars (e.g., the 4-4-5 calendar and 13 period calendar supported by Oracle BI Apps). This can be done by setting the following source system parameters in the Business Intelligence Applications Configuration Manager (BIACM):

  • GBL_CALENDAR_ID (used to set the ID of the calendar to be used as the Enterprise Calendar)
  • GBL_DATASOURCE_NUM_ID (used to set the DSN of the source from which the Enterprise Calendar is chosen)

The following sections show how to set up these two parameters for the Enterprise Calendar in different scenarios.

Scenario 1: Using an Oracle EBS fiscal calendar as the Enterprise Calendar

  • GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. In EBS, it should have the format of MCAL_CAL_NAME~MCAL_PERIOD_TYPE. For example, GBL_CALENDAR_ID will be 'Accounting~41' if MCAL_CAL_NAME = 'Accounting' and MCAL_PERIOD_TYPE = '41'.

Note 1: MCAL_CAL_NAME and MCAL_PERIOD_TYPE are sourced from PERIOD_SET_NAME and PERIOD_TYPE of the GL_PERIODS table (an Oracle EBS OLTP table). To see a valid list of combinations of MCAL_CAL_NAME~MCAL_PERIOD_TYPE, run the following query in the OLTP:

SELECT DISTINCT PERIOD_SET_NAME || '~' || PERIOD_TYPE FROM GL_PERIODS;

Note 2: The available EBS calendars are also loaded into the OLAP warehouse table W_MCAL_CAL_D. Therefore, they can be viewed by running the following query in DW:

SELECT MCAL_CAL_ID, MCAL_CAL_NAME, MCAL_CAL_CLASS, DATASOURCE_NUM_ID

FROM W_MCAL_CAL_D

WHERE DATASOURCE_NUM_ID = <the value corresponding to the EBS version that you use>;

  • GBL_DATASOURCE_NUM_ID: For EBS, this parameter should be the DATASOURCE_NUM_ID of the source system from where the calendar is taken. For example, if you are running EBS R11.5.10 and the DATASOURCE_NUM_ID for this source is 310, then you need to set GBL_DATASOURCE_NUM_ID to 310.

GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID are set in BIACM, and this will be covered in a later section.

Scenario 2: Using a PeopleSoft fiscal calendar as the Enterprise Calendar

  • GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. In PSFT, it should have the format of SETID~CALENDAR_ID. For example, GBL_CALENDAR_ID will be 'SHARE~01' if SET_ID = 'SHARE' and CALENDAR_ID = '01'.

Note 1: SETID and CALENDAR_ID are sourced from the PS_CAL_DEFN_TBL table (a PeopleSoft OLTP table). To see a valid list of combinations of SETID~CALENDAR_ID, run the following query in the OLTP:

SELECT DISTINCT SETID || '~' || CALENDAR_ID FROM PS_CAL_DEFN_TBL;

Note 2: The available PeopleSoft calendars are also loaded into the OLAP warehouse table W_MCAL_CAL_D. Therefore, they can be viewed by running the following query in DW:

SELECT MCAL_CAL_ID, MCAL_CAL_NAME, MCAL_CAL_CLASS, DATASOURCE_NUM_ID

FROM W_MCAL_CAL_D

WHERE DATASOURCE_NUM_ID = <the value corresponding to the PeopleSoft version that you use>;

  • GBL_DATASOURCE_NUM_ID: For PSFT, this parameter should be the DATASOURCE_NUM_ID of the source system from where the calendar is taken. For instance, if you are running PeopleSoft 9.0 FSCM Instance and the DATASOURCE_NUM_ID for this source is 518, then you need to set GBL_DATASOURCE_NUM_ID to 518.
Note: OLTP sourced calendars are not supported in PeopleSoft HCM pillars. Therefore, should you want to choose an enterprise calendar if you are running PSFT HCM, you need to choose one of the DW generated calendars (i.e., 4-4-5 or 13 period calendar), which is explained in Scenario 3.

Scenario 3: Using a warehouse generated calendar as the Enterprise calendar

  • GBL_CALENDAR_ID: This parameter should be the CALENDAR_ID of the DW generated Calendar (so either 4-4-5 or 13 period calendar). By default, the 4-4-5 calendar has a CALENDAR_ID of '10000', and the 13-period calendar has a CALENDAR_ID of '10001'.
  • GBL_DATASOURCE_NUM_ID: If Enterprise Calendar is a DW generated Calendar, this parameter should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse). For example, if the DATASOURCE_NUM_ID for your DW is 999, then GBL_ DATASOURCE_NUM_ID should be set to 999.

Note 1: Customers can generate additional warehouse generated calendars which can be picked as the Enterprise Calendar.

Note 2: The available data warehouse calendars are also loaded into the OLAP warehouse table W_MCAL_CAL_D. Therefore, they can be viewed by running the following query in DW:

SELECT MCAL_CAL_ID, MCAL_CAL_NAME, MCAL_CAL_CLASS, DATASOURCE_NUM_ID

FROM W_MCAL_CAL_D

WHERE DATASOURCE_NUM_ID = <DSN of your DW>

Setting GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID in BIACM

To set GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID, log into BIACM, and click on Manage Data Load Parameters from the navigation bar on the left. Once being directed to the Manager Data Load Parameters page, type in GBL_CALENDAR_ID in the parameter field and choose Code as the parameter type. Then click the Search button after which the parameter with its current value will be returned. Refer to the example below, which shows 10000 as the current value of GBL_CALENDAR_ID.

To change the value of GBL_CALENDAR_ID, click on its current value, and then an edit dialog pops up.

Provide the desired value in the Parameter Value field (note that you do not need to include single quotes in the value, e.g., use Accounting~41 rather than ‘Accounting~41’), and then click Save and Close to save your change. The new value of GBL_CALENDAR_ID has been set.

The setting procedure for GBL_DATASOURCE_NUM_ID is similar. It should be first retrieved by searching for this variable. Once it is returned, click on its current value and then an edit dialog pops up. Change its parameter value there and then save the change.

Setting GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID in a multi-source ETL

In a multi-source ETL run, multiple fiscal calendars from different data sources can be loaded. However, in this case, ONLY ONE calendar can be chosen as the Enterprise Calendar. For example, if you have two data sources, PeopleSoft and Oracle, then you can only choose either a calendar from PeopleSoft or a calendar from Oracle as the Enterprise Calendar. The two parameters GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID should be set in BIACM according to the calendar that you choose. Never provide more than one value to GBL_CALENDAR_ID or GBL_DATASOURCE_NUM_ID in BIACM. That would fail the ETL run.

Configure summary calendar file (file_summary_calendar.csv) for PeopleSoft ETL

Author: Chuan Shi

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:

  1. A detailed calendar defined in PSFT source;
  2. A summary calendar defined in PSFT source that is appropriate for the detailed calendar;
  3. 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:

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

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

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

About

Oracle Blogs Admin-Oracle

Search

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