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:

1. 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.
   
2. Build the corresponding Execution Plan (EP) for this Subjects Area with same name.
   
3. 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.
     
4. 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.
   
5. 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.
   
6. Run this Execution Plan and verify that the dates in W_D_DAY and related tables have been extended.
   
7. 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.