Notes on Configuring Calendars

Here are some notes that you must remember while configuring calendars.

  • The W_MCAL_CONFIG_G table controls how generated calendars are created.

  • If generating the 4-4-5 or 13 period calendars, W_MCAL_CONFIG_G must have at least one row for the 4-4-5 period or 13 period. There is no entry needed in this table for Oracle EBS or PeopleSoft source calendars.

    Note:

    Oracle's JD Edwards EnterpriseOne and World Adapters for Financial Analytics do not support 4-4-5 period calendars.

  • W_MCAL_WEEK_D will be populated only for the generated calendars (for example, 13 period or 4-4-5 type of calendars), and hence the W_DAY_D week Enterprise columns will be null for non-generated calendars (known as OLTP sourced fiscal calendars). W_ENT_WEEK_D will not be populated if a non-generated calendar is chosen as the Enterprise Calendar.

  • For 13 period calendars, there is no concept of Quarter, therefore all Quarter columns in W_MCAL_WEEK_D, W_MCAL_PERIOD_D, W_MCAL_YEAR_D will be null. W_ENT_QTR_D will not be populated if a 13 period calendar is chosen as the Enterprise Calendar.

  • Following table outlines columns in the W_MCAL_CONFIG_G table, which is loaded from the file_mcal_config_g.csv:
    Column Name Column Description

    CALENDAR_ID

    The ID of the calendar that is being configured. This is the primary key for this table.

    CALENDAR_NAME

    The name of the calendar that is being configured.

    CALENDAR_CLASS

    Automatically generated.

    PERIOD_TYPE

    The type of the calendar period that is being configured, such as 4-4-5.

    CAL_ST_DT

    The date from which the calendar generation begins.

    CAL_END_DT

    The date at which the calendar generation ends.

    CAL_OFFSET
    The offset which identifies the start date of the calendar. Valid Start Day and Offset values are:

    • Monday 0

    • Tuesday 1

    • Wednesday 2

    • Thursday 3

    • Friday –3

    • Saturday –2

    • Sunday –1

    WEEK_ALLOCATION_RULE

    This parameter determines how weeks are allocated in the calendar that is being configured. For example, 4-4-5, 5-4-4, 4-5-4, or 13 period.

    REFERENCE_DATE

    The date (in MMDD format) that the fiscal year starts. For example, if an organization's fiscal year is from October to September, then the reference_date value should be 0929. The previous fiscal year must end between three days before and three days after the date specified by REFERENCE_DATE (that is, within the range (REFERENCE_DATE - 3) to (REFERENCE_DATE + 3). In other words, if a REFERENCE_DATE is 0131 for 31st January, the previous fiscal year cannot exceed 3rd February.

    Other standard columns

    Such as W_INSERT_DT, W_UPDATE_DT, TENANT_ID, X_CUSTOM.
  • The following table outlines Task Level DAC Parameters needed for Generated Calendars:
    DAC Parameter Name DAC Parameter Description

    $$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.

  • By default, Oracle BI Applications can generate up to 65536 rows. If you need more than 65536 rows, you can increase the capacity to 262144 rows (718 years) by doing the following:

    1. Duplicate 'SIL_DayDimension_GenerateRows7'.

    2. Rename it 'SIL_DayDimension_GenerateRows8'.

    3. Run this immediately after 'SIL_DayDimension_GenerateRows7'.