OBIA Training: May 2015

Set Up an Enterprise Calendar Using an Oracle EBS Source System

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Source System Parameters tab.
Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:
- $$GBL_CALENDAR_ID: This parameter is used to select the Enterprise Calendar. Use the following EBS Source query to identify possible values for this parameter:
```
SELECT period_set_name || '~'|| period_type FROM gl_periods;
```
  This query returns a list of choices for the $$GBL_CALENDAR_ID parameter. To select the Enterprise Calendar, use one of the returned values as the value for $$GBL_CALENDAR_ID. Note that the format of the parameter $$GBL_CALENDAR_ID is a concatenation of the PERIOD_SET_NAME and PERIOD_TYPE.
- $$GBL_DATASOURCE_NUM_ID: This parameter is the data_source_num_id with which you have loaded the EBS calendars.
Note:
The tasks that load the Enterprise calendar will run as part of the Execution Plan for your Subject Area. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.

Set Up a 13 Period Calendar

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Source System Parameters tab.
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).
Using a text editor, edit the values in file_mcal_config_g.csv.
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. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.

Set Up a 4-4-5 Calendar

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Source System Parameters tab.
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)
Using a text editor, edit the values in file_mcal_config_g.csv.
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. There are no separate Subject Areas for common dimensions; they are included in the core Subject Areas.

Fiscal Calendar Loaded Through the Universal Adapter

In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
Display the Source System Parameters tab.
Set the value of $$GBL_CALENDAR_ID and $$GBL_DATSOURCE_NUM_ID as follows:
- $$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 Calendar: Should be the DATASOURCE_NUM_ID of the source system from where the Calendar definition is taken, if its defined in the file_mcal_period_ds.csv file then that value should be taken or else as defined in DAC for Universal adapter.
Using a text editor, edit the values in the w_mcal_config_g.csv file.
Using a text editor, edit the values in the w_mcal_config_d.csv file.
If you are using Oracle Financial Analytics and Oracle Project Analytics to lookup the calendar ID for a given ledger OU, using a text editor, edit the values in the w_mcal_context_g.csv file.
Using a text editor, edit the values in the w_mcal_period_ds.csv file (if required).
In DAC, set the value of $$GBL_CALENDAR_ID and $$GBL_DATASOURCE_NUM_ID (as in scenario 4).

Reload the Time Dimension Tables After the Data Warehouse Is Loaded

The data in time dimension tables is loaded once during the initial full load of the warehouse. Subsequently, the SIL_%_UpdateFlag mappings run during each incremental run to update the domain value codes, which indicate whether a day, week, month, quarter or year is 'Current', 'Next' or 'Previous' as of the current day. There are also other incremental tasks that run to populate the W_MCAL_% tables (Tables for Multiple Calendar Support). The range of dates for which data is loaded into the time dimension tables is determined by the parameters $$START_DATE and $$END_DATE.
Extending the data in the time dimension tables prior to the occurrence of $$END_DATE is recommended. If extension is not done prior to the occurrence of $$END_DATE, this will lead to failures, documented in technical note 1063484.1 on My Oracle Support.
In V7.9.6.3, there is an Execution Plan provided for each container in DAC, to extend the Day Dimension when required. These Execution Plans includes all time dimension related tasks and initiates a full load ETL run of W_DAY_D and all the related time dimension tables, including the W_MCAL_% tables.
To extend the Day Dimension:

A Subjects Area named 'Common dimension Extend Day Dimension' has been provided to facilitate the extension of the day dimension and related tables. By default, this Subjects Area does not include the W_MCAL_% related tasks. If you have Multiple calendar support enabled in your Day Dimension, then include the configuration tag named 'Extend Day Dimension Multiple Calendar Support.' Then, assemble this Subject Area.
Note: Only the Subjects Area 'Common dimension Extend Day Dimension' should be included for extension. Do not include any other Subjects Areas in this extension Execution Plan.
Build the corresponding Execution Plan (EP) for this Subjects Area with same name.
Choose the Task SIL_DayDimension_XTND within the Execution Plan, and set the parameter values as follows:
- $$START_DATE: Set this parameter to one day more than the old $$END_DATE value ($$END_DATE +1).
- $$END_DATE: Set this parameter to the new end date to which you want to extend.
Choose the SIL_TimeDimension_MCalPeriod_XTND within the Execution Plan and set the parameter values. Retain the original $$START_DATE, and choose a new $$END_DATE to which you want to extend. The $$END_DATE value should be same as the $$END_DATE value you chose for the task SIL_DayDimension_XTND in step 3. Note that this step is only needed if the configuration tag 'Extend Day Dimension Multiple Calendar Support' was included in step 1.
If necessary, change the following CSV files:
- FILE_MCAL_CAL_D
- FILE_MCAL_CONTEXT_G
- FILE_MCAL_PERIOD_DS (if using the Universal Adapter)
- FILE_MCAL_CONFIG_G
If any of these files are used as sources, they must be extended for the new date ranges.
Run this Execution Plan and verify that the dates in W_D_DAY and related tables have been extended.
Rebuild all affected fact aggregates. For more information on which aggregates to rebuild, as well as detailed steps on how to rebuild them, refer to technical note 1063484.1 on My Oracle Support. Note that technical note 1063484.1 was written for pre-7.9.6.3 customers.
Technical note 1063484 also provides steps for extending the time dimension and related tables. You can follow the steps in the technical note as an alternative to using the steps in this procedure.