OBIA 7964 – Multiple Calendar Support Configurations In Five Easy Steps

Customer is implementing BI Apps 7964 based on E-Business source system. The source system has been configured for two sets of business lines within a flagship company. One is retail while other one is around oil, which is very valid business scenario in modern businesses. The retail and oil business have different ledgers with different type of calendars. The retail works on 13 period calendar while oil business works on 4-4-5 type of calendar. The implementation challenge for customer is to configure two calendars in single data warehouse for OBIA Financials and Project Analytics for two business units and should work seamlessly based on selected ledger.

This blog is an attempt to showcase the configuration which I have done to achieve multiple calendar implementations within single business analytics warehouse. 

Calendar Formats

Oracle Business Intelligence Applications Version 7.9.6 supports the following 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 (Source Calendars) –are defined in ERP sources and brought into the warehouse via ETL
• Generated Calendars - are fiscal calendars generated in the warehouse based on configuration files

Calendar Configuration

Configuration for Base Day Dimension

W_DAY_D needs to be populated as a prerequisite for the multiple fiscal calendar tables.  The configuration required is to set $$START_DATE and $$END_DATE parameters for the task SIL_DayDimension to load the calendar data in W_DAY_D.

STEP  1: - Set Parameters $$START_DATE and $$END_DATE for task SIL_DayDimension  

 Configuration for Enterprise Calendar  

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 source system parameters $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID. This can be done at DAC container level.

Step 2 : Set source system parameters $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID for Enterprise Calendar – Either of 4-4-5 or 13 Period

 

Calendar formats with possible parameter values for E-Business source system are as follows

E-Business Sourced Calendar	Parameter $$GBL_CALENDAR_ID Value Format MCAL_CAL_NAME~MCAL_PERIOD_TYPE Example 'Accounting~41'
	$$GBL_DATSOURCE_NUM_ID - It should be data source number
Warehouse generated calendars (4-4-5 or 13 period type)	Parameter $$GBL_CALENDAR_ID – It should be Calendar Id Value Format 4-4-5 calendar - CALENDAR_ID of '10000' 13-period calendar - CALENDAR_ID of '10001'.
	$$GBL_DATSOURCE_NUM_ID - It should be data source number

Configuration for Warehouse Generated Fiscal Calendars

OBIA supports two generated fiscal calendars i.e. 13 Period Calendars and 4-4-5 calendars.  Following tables are important in process of fiscal calendar data population

W_MCAL_CONTEXT_G	This context table is used by Financials and Project Facts to lookup the calendar Id for a given ledger or Operating Unit The population is done by default execution plan , no need to do anything from configuration perspective
W_MCAL_CONFIG_G	This configuration table controls how generated calendars are created. This is sourced from the csv file - file_mcal_config_g.csv For configuration make sure that file_mcal-config_g.csv has all records required, make sure that calendar id is unique and acts as primary key

Step 3: Make sure the Context and Configuration is in place, this is just validation task only.

Task Level DAC Parameters needed for Generated Calendars

Make sure that following task level DAC parameters are set for generated calendars

$$13P_CALENDAR_ID	Task – Include SIL_TimeDimension_MCalWeek13Period. Value CALENDAR_ID from file_mcal-config_g.csv
$$445P_CALENDAR_ID	Task – Include SIL_TimeDimension_MCalWeek445 Value CALENDAR_ID from file_mcal-config_g.csv

Step 4: Set task Level Parameters for $$13P_CALENDAR_ID and $$445P_CALENDAR_ID

Include Multiple Calendar Support

The Multi-Calendar tasks are included in the TASK_GROUP_Load_DayDimension in the DAC. Inclusion of this task group is not enough, in addition configuration tag named ‘Multiple Calendar Support' needs to included to extract multi-calendar tasks.

Subject Area > Configuration Tags > Select ‘Multiple Calendar Support'

Step 5: Include configuration tag named ‘Multiple Calendar Support', re-build execution plan once done

The process flow diagram for Time Dimension load is shown below.

I have tried my level best to simplify the multi calendar setting configuration for OBIA 796x for fiscal calendars, I hope you find this implementation experience sharing useful.