Configuring Enterprise Calendar for Oracle BI Apps Time Dimension

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.