The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)
-
ETL / Customization
January 23, 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.
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.
Is this still true in 11.1.1.10.2? If yes, why was this decision made?
In a HCM only implementation of PSFT, if the fiscal calendars are not configured, then those calendars cannot be loaded into the warehouse and hence the only option is to pick one of the DW generated calendars as the Enterprise Calendar. However if both PSFT FINSCM and PSFT HR are implemented, then one of the OLTP fiscal calendars can be chosen as the Enterprise calendar. If PSFT HR pillar has started supporting fiscal calendars, then we will need an enhancement to bring in those calendars from PSFT HR into the DW. Once we do that, you can pick one of those calendars as the Enterprise calendar. Currently we only bring fiscal calendars from PSFT FINSCM. This is still true in 11.1.1.10.2.
We do have both PSFT HCM and FINSCM. How would we configure the PS FINSCM Fiscal Calendar in the HR Analytics Config though? The source systems/databases are different.
In that case you can follow the steps in Scenario 2 in this article. You can set the Enterprise Calendar parameters in the warehouse configuration from BIACM.
Our user has a requirement to replace the Gregorian calendar with Enterprise calendar im BI Apps with ODI version..
They are saying to replace w_Day_D with M_Cal_Day_d...Whats the best way to do it...
Please suggest
W_MCAL_DAY_D has day level entries for different calendars that are supported. One of these calendars is chosen as the Enterprise calendar. If you need facts to support Enterprise calendar, the fact ETL has to be modified to resolve the foreign key for the date columns of interest to the ROW_WID of W_MCAL_DAY_D. This day level table has entries for multiple calendars. Hence the foreign key has to be resolved using the calendar_id and the date as there will be multiple entries for each day for multiple calendars. The related RPD changes (joins, Pl changes) need to be done as well to switch the calendar.