Calendars in Oracle BI Applications 7.9.6

Technorati Tags: OBIEE,Calendar,Oracle BI Applications

Overview of Calendars in Oracle BI Applications

Oracle Business Intelligence Applications Version 7.9.6 supports different calendar formats:

• Enterprise (Global) - cross functional reporting calendar, which can be fiscal or gregorian.

• Fiscal - accounting or financial calendar.

• Gregorian - regular calendar that starts on January 1st and ends on December 31st.

• 13 Period - a calendar is which each year is comprised of 13 periods.

• 4-4-5 - each year is composed of twelve periods of either four weeks of 28 days or five weeks of 35 days.

 

Calendar Categories

Calendars are categorized into two types:

OLTP sourced (also known as Source Calendars) - OLTP sourced calendars are calendars that are defined in ERP sources and brought into the warehouse via ETL maps and standard DAC loads.

Warehouse generated (also known as Generated Calendars) - Generated calendars are fiscal calendars generated in the warehouse based on configuration files.

Both source calendars and generated calendars are stored in the Multiple Fiscal Calendar (known as MCAL) tables. MCAL tables have the prefix W_MCAL.

 

Calendar Tables in Oracle Business Analytics Warehouse

The tables used for Time Dimension calendars can be categorized based on the calendar types as below -

Gregorian Calendar Tables

• W_WEEK_D

• W_MONTH_D

• W_QTR_D

• W_YEAR_D

• W_DAY_D

Fiscal Calendar Tables

• W_MCAL_WEEK_D

• W_MCAL_PERIOD_D

• W_MCAL_QTR_D

• W_MCAL_YEAR_D

Enterprise Calendar Tables

• W_ENT_WEEK_D

• W_ENT_PERIOD_D

• W_ENT_QTR_D

• W_ENT_YEAR_D

 

W_DAY_D is the base table that represents the time dimension in the Oracle Business Analytics Warehouse. This table needs to be populated as a prerequisite for the multiple fiscal calendar tables. If W_DAY_D is not populated, then the fiscal calendar tables will not be populated.

There are two parameters $$START_DATE and $$END_DATE for the task SIL_DayDimension that need to be setup to load the calendar data in W_DAY_D. The SIL mappings use standard time functions to create records for each calendar day falling within the boundary defined by these two parameters. Once the records are created in W_DAY_D, the aggregate calendar tables are loaded by their respective SIL mapping. Then the fiscal calendar tables (known as MCAL tables) are populated.

 

Configuring Enterprise Calendars

An Enterprise calendar (or reporting calendar) enables cross subject area analysis. Enterprise calendar tables have 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 DAC container level:

• $$GBL_CALENDAR_ID

• $$GBL_DATSOURCE_NUM_ID

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

Source System DAC 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'.

• 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 as PeopleSoft and Oracle, and the Global Calendar is from an Oracle data source, then this parameter value should specify an Oracle data source.

Scenario 2 Using a warehouse generated calendar as the Enterprise calendar

Source System DAC Parameters for Generated Enterprise Calendars:

• GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 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 Generated Calendar: It should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse)

Scenario 3 Using a fiscal calendar loaded via the Universal Adapter as the Enterprise calendar

Source System DAC Parameters for Universal Enterprise Calendars:

• GBL_CALENDAR_ID: Should be the INTEGRATION_ID from the file_mcal_cal_d.csv file of the particular calendar which is defined as the Global Calendar.

• GBL_DATASOURCE_NUM_ID: If Global Calendar is not a Generated Calendars, then it should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken. If it is defined in the file_mcal_period_ds.csv file, then that value should be taken, or else as defined in the DAC for Universal adapter.

 

Generated Calendars

The following Task Level DAC Parameters are needed for Generated Calendars.

$$13P_CALENDAR_ID  - Task: SIL_TimeDimension_MCalWeek13Period. Required if you want to populate the 13 period type of calendar in your Data Warehouse. The value should be the CALENDAR_ID as defined in the W_MCAL_CONFIG_G table for the 13 period type of calendar.

$$445P_CALENDAR_ID  -  Task: SIL_TimeDimension_MCalWeek445. Required if you want to populate the 445 period type of calendar in your Data Warehouse. The value should be the CALENDAR_ID as defined in the W_MCAL_CONFIG_G table for the 445 period type of calendar.

If there is a week (starting on a Sunday and ending on a Saturday) that falls across two calendar years, the week is counted in both years. For example, the week that starts on 12/30/2007 will be counted in both 2007 and 2008. In 2007, the week start date will 12/30/2007 and the end date will be 12/31/2007. In 2008, this will be the first week with start date as 01/01/2008 and end date as 01/05/2008.

W_DAY_D stores 31 records for each month regardless of whether the month actually has 31 days. If the month has a fewer number of days, there will be records with null values in the Calendar Date and Day Date columns. These extra records are loaded for the calculation of Period Ago metrics in the Oracle BI Repository and will not affect the ETL or reporting.

There are some attributes on the W_DAY_D table that are not mapped in the Physical layer of the Oracle BI Repository. Therefore, before creating any new attribute in the repository, check whether the attribute is already available in the Physical layer and if it can be mapped directly.

If your fiscal calendar contains more than 12 months, the extra months will be assigned a value of 0 for the fiscal quarter. The same holds for the fiscal trimester and fiscal half values.

 

How to Include and Exclude Multiple Calendar Support for Subject Areas in DAC

The Multi-Calendar tasks are included in the TASK_GROUP_Load_DayDimension in the DAC.

When the TASK_GROUP_Load_DayDimension task group is added to any Subject Area, the Multi-Calendar tasks are not extracted by default. You need to add a Configuration tag to the Subject area before you run the ETL process to pull these tasks into the ETL process, as described in the following steps.

To include and exclude Multiple Calendar support for Subject Areas:

1. In DAC, display the Design view, and select the appropriate container.

2. Display the Subject Area tab in the upper pane and select the Subject Area to which you want to add Multi-calendar tasks.

3. Display the Configuration Tags tab in the lower pane.

4. Click Add/Remove to display the Choose Subject Area Configuration Tags dialog.

5. Select the configuration tag named 'Multiple Calendar Support' in the left pane and click Add, then OK.

6. In the upper pane, click Assemble.

7. Display the Execute view.

8. Display the Execution Plans tab.

9. Select the Execution Plan that includes the Subject Area to which you added the configuration tag named 'Multiple Calendar Support'.

10. Run this Execution Plan.

 

How to set up a 13 Period calendar

To set up a 13 Period calendar:

1. In the DAC, display the Design tab, and select an appropriate adapter.

2. Display the Source System Parameters tab.

3. Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:

   • GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 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 Generated Calendar: Should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse).

4. Using a text editor, edit the values in file_mmcal_config_g.csv.

5. In DAC, set the value of 13P_CALENDAR_ID to 10001.

   Note: The task SIL_TimeDImension_McalWeek13Period will run as part of the execution plan for your subject area. Please note that is no separate subject are for common dimensions. They are included in the core subject areas.

 

How to set up a 4-4-5 calendar

To set up a 4-4-5 calendar:

1. In the DAC, display the Design tab, and select an appropriate adapter.

2. Display the Source System Parameters tab.

3. Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:

   • GBL_CALENDAR_ID: Should be the CALENDAR_ID of the Generated Calendar (4-4-5 or 13 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 Generated Calendar: Should be the DATASOURCE_NUM_ID value of the OLAP (Data warehouse)

4. Using a text editor, edit the values in file_mcal_config_g.csv.

5. In DAC, set the value of 445P_CALENDAR_ID to 10000.

   Note: The task SIL_TimeDimension_McalWeek445 will run as part of the execution plan for your subject area. Please note that is no separate subject are for common dimensions. They are included in the core subject areas.