Customization of Time Dimension

The first and foremost customization that came up was Time (Day) Dimension. In Contact Center Telephony Analytics, Time Dimension always relates to Half Hour Interval or Hour Interval and Fact tables are populated with a key to Time dimension. But, in OM Analytics, OOB calls Day(Period) Dimension as Time dimension in Presentation. It is understandable given that in OM area, Time is actually measured in days. In a business like the one that I am trying help now, Time is really not even days given that some large products take near about 2 years to manufacture and deliver.

Coming back to Time dimension, first thing that was pointed out was that Fiscal Calendar is not Company specific calendar. I am surprised why Oracle has not made an attempt to lookup GL_PERIODS table in Oracle EBS to populate the Day dimension. I have created two Custom mappings that updates Day dimension for Calendar that is defined in Oracle EBS. There are 2 ETLs and the logic is as below.

Mapping 1 - Lookup GL_PERIODS and extract PERIOD_YEAR, PERIOD_NUM, QUARTER_NUM, YEAR_START_DATE, QUARTER_START_DATE, ADD_MONTHS(GL_PERIODS.YEAR_START_DATE, 12)-1 as YEAR_END_DATE, START_DATE as MTH_START_DATE, END_DATE as MTH_END_DATE.
Lookup BOM_CAL_WEEK_START_DATES for Week Number and Week Start date. Week Number is not captured in BOM_CAL_WEEK_START_DATES; so, an expression is created to calculate the week number for the given date.

Mapping 2 - End Dates are calculated and populated for each date in W_DAY_D.