About Enterprise Calendars
An Enterprise calendar (or reporting calendar) enables cross Subject Area analysis. Enterprise calendar tables have the W_ENT prefix.
Enterprise calendars can be set to one of the OLTP sourced fiscal calendars, or to one of the warehouse generated calendars. This can be done by setting the following source system parameters at the Business Intelligence Applications Configuration Manager:
- GBL_CALENDAR_ID
- GBL_DATASOURCE_NUM_ID
The following sections show how to set up the source system parameters for the Enterprise calendar in different scenarios.
Scenario 1: Using an Oracle EBS fiscal calendar as the Enterprise calendar
Source System Oracle BI Applications Configuration Manager Parameters for Oracle EBS Enterprise Calendars:
GBL_CALENDAR_ID
This parameter is used to select the Enterprise Calendar. It should be the MCAL_CAL_NAME~MCAL_PERIOD_TYPE for Non-Generated Calendars. For example, GBL_CALENDAR_ID will be 'Accounting~41' if the Enterprise Calendar id = 'Accounting' and the calendar period_type = '41'.
Note: 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;
GBL_DATASOURCE_NUM_ID
If Enterprise Calendar is not a Generated Calendar: It should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. For example, if you have two data sources, PeopleSoft and Oracle, and the Global Calendar is from an Oracle data source, then this parameter value should specify an Oracle data source. The pre-determined values of DATASORUCE_NUM_ID for different Oracle EBS versions are provided in the table .
To set GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID, log into Oracle BI Applications Configuration Manager, 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, 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, for example, just 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 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.
Note: The available Oracle 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>;
Scenario 2: Using a PeopleSoft fiscal calendar as the Enterprise calendar
Source System Oracle BI Applications Configuration Manager Parameters for PeopleSoft Enterprise Calendars:
GBL_CALENDAR_ID
This parameter is used to select the Enterprise Calendar. It should be the SETID~CALENDAR_ID for Non-Generated Calendars. For example, GBL_CALENDAR_ID will be 'SHARE~01' if the Enterprise Calendar is = '01' and SET_ID = 'SHARE'.
Note:
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;
GBL_DATASOURCE_NUM_ID
If Global Calendar is not a Generated Calendar: It should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. For example, if you have two data sources, PeopleSoft and Oracle, and the Global Calendar is from PeopleSoft source, then this parameter value should specify a PeopleSoft data source. The pre-determined values of DATASORUCE_NUM_ID for different PeopleSoft versions are provided in the table below.
The settings of these two variables in Oracle BI Applications Configuration Manager are the same as those steps for Oracle EBS.
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>;
Scenario 3: Using a warehouse generated calendar as the Enterprise calendar
Source System Oracle BI Applications Configuration Manager Parameters for Generated Enterprise Calendars:
GBL_CALENDAR_ID
This parameter should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 14 period type of Calendars). 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 Global Calendar is a Generated Calendar: It should be the DATASOURCE_NUM_ID value of the OLAP (Oracle Business Analytics Warehouse), which is 999.
The settings of these two variables in Oracle BI Applications Configuration Manager are the same as those steps for Oracle EBS.
Note:
Note 1: Customers can generate additional warehouse generated calendars which can be picked as the Enterprise calendar.
Note 2: In Oracle Business Analytics Warehouse, the available calendars are also loaded into the OLAP 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 = 999;
Setting GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID in a multi-source ETL
In a multi-source ETL run, multiple calendars from different data sources can be loaded. However, in this case, ONLY ONE calendar can be chosen as the Global 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 Global Calendar. The two parameters GBL_CALENDAR_ID and GBL_DATASOURCE_NUM_ID should be set in Oracle BI Applications Configuration Manager according to the global calendar that you choose. Never provide more than one value to GBL_CALENDAR_ID or GBL_DATASOURCE_NUM_ID in Oracle BI Applications Configuration Manager. That would fail the ETL run.