Time Dimension - Design solution for two different calendars

[sdr]

Hi All,

We are in the process of designing Time Dimension for our Data Warehouse.
We have two different calendars 1. Fiscal calendar (Day level) 2. Promotional calendar (Week level and this has two different groups based on start & end day of the week i.e., Wednesday to Tuesday & Thursday to Wednesday).

Now the report requirement is such that the business wants to look into out of stock data at daily level and forecast data which is at weekly level.

I thought of two options 1. merging both calendars in one table and 2. keeping the fiscal and promotional calendar in separate tables.

As I m new to dimensional modeling I would like to know whether the above options are correct or is there any other better way to handle this scenario.

Cheers
sdr

[Jeff Smith]

I prefer to call it a date dimension because, well, it should not have time in it. I have a seperate Time dimension.

You only need one Date dimension that has Date as it's base. you can define the fiscal year within the Date dimension and you can define Promotional periods within it. But the base level is the date. You might want to create rollups from the base date to the promotional period.

With such a dimension, you can use it for the date the sale was made, but you can also use it in a table listing the employees working that day. This way, you can easily aggregate sales to each promotional period as well as the number of man hours during the promotional period.