Oracle^® Business Intelligence Applications Installation and Configuration Guide > Installing and Configuring Oracle BI Applications on Windows > Setting Up The Time Dimension >

Process of Setting Up The Fiscal Calendar

To configure Fiscal Calendar by Fiscal Week

1. Open the file fiscal_week.csv using Microsoft WordPad or Notepad in the $PMRootDir\SrcFiles folder. Enter the Fiscal Year, Fiscal Month, Fiscal Week and the Start Date of Fiscal Week in YYYYMMDD. Records must be inserted in ascending order of Fiscal Year, Fiscal Month, Fiscal Week. Save and close the file.

   NOTE:  Make sure you enter accurate Fiscal Data as there is no check done within the Informatica mappings.

2. Deactivate the task SIL_DayDimension_FiscalMonth_Extract in the Data Warehouse container in DAC. Then right click and push to references.
3. Activate the task SIL_DayDimension_FiscalWeek_Extract in Datawarehouse container in DAC. Then right click and push to references.

To configure Fiscal Calendar by Fiscal Month

1. Open the file fiscal_month.csv using Microsoft WordPad or Notepad in the $PMRootDir\SrcFiles folder. Enter the Fiscal Year, Fiscal Month and the Start Date of Fiscal_Month in YYYYMMDD format. Save and close the file.

   NOTE:  Make sure that you enter accurate Fiscal Data as there is no check done within the Informatica mappings.

2. Activate the task SIL_DayDimension_FiscalMonth_Extract in Datawarehouse container in DAC. Then right click and push to references.
3. Deactivate the task SIL_DayDimension_FiscalWeek_Extract in Datawarehouse container. Then right click and push to references.

Reloading the Time Dimension Tables After Your Data Warehouse is Populated

The data in time dimension is loaded once during the initial full load. Subsequently, the SIL_*_UpdateFlag mappings run everyday 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. The SIL_Fiscal_UpdateFlag mappings also update the flags that indicate whether a fiscal week, month, quarter or year is 'Current', 'Previous' or 'Next' with respect to the system date.

You might want to extend the range of data that you have in your time dimension sometime after the warehouse is in production. In order to achieve this, please follow the steps below that will kick off the full load ETL run of the W_DAY_D and all the aggregate time dimension tables.

To set up the load strategy of the time dimension table

1. In DAC, go to Setup -> Physical Data Sources and click on the connection DataWarehouse.
2. Go the RefreshDates tab in the bottom panel.

   The Refresh Date of all the tables will be given there. Double click on the RefreshDate of W_DAY_D and make it Null. Do the same for all the aggregate time dimension tables W_WEEK_D,W_QTR_D,W_MONTH_D,W_YEAR_D,W_FSCL_WEEK_D,W_FSCL_QTR_D,W_FSCL_MONTH_D and W_FSCL_YEAR_D.

3. Go to the TaskDefinition and open the task SIL_DayDimension in DataWarehouse container.
4. Set the $$Start_date and $$End_Date parameters to the appropriate start date and end date of the new date range.
5. Save the task and push the changes to references.

   NOTE:  Make sure that the new date range defined by the parameters $$Start_date and $$End_date encompasses the old date range entirely. Otherwise records can be lost.

   NOTE:  Make sure you have provided the fiscal information for the new date range also in the appropriate fiscal input file. The next time you run your ETL execution plan, the calendar dimension will be truncated and reloaded with the new date range that you have specified. Since the calendar dimensions use smart keys, none of the fact tables that refer to these tables need to be reloaded.

Notes

• 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 irrespective of whether the month actually has 31 days or not. If the month actually has less 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 RPD, 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 RPD. Therefore, before creating any new attribute in the RPD, 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.