Subsetting date/time Dimensions and Role Playing Date/Time Dimensions

[Scoop]

Working at a client site that would like to take a Date dimension using Day as Pk for Day_dim with some day of week, week day, month, year, etc. columns.

Suggestion of making more conformed dimensions from Day_dim with Week_Day_dim and Month_day_dim and have these be subset dimensions and conformed from Day_dim. Looking for comments and suggestions on this approach?

Role Playing Day_dim and creating 2 new materialized views of Day_dim consisting of Week_day_dim_MV with key = WeekDay_Month_YYYY and Month_day_dim_MV with key = Month_YYYY.
Looking for comments and suggestions on this approach?

basically we have one fact that is day level, antother at week and another at month. The Day level key is Day in Day_dim with relationship to day_fact for each given day 03/01/2008 is PK. Now we need to get at Week day data for a given week and Month that business is going to select facts by.

Thank you for your help

[ngalemmo]

Just add additional natural key columns to the existing date dimension for the different subsets. For example, add a month NK column which would be null except for one day a month that you wish to use to represent the month (such as the last day of the month). This would ensure various month based tables are keyed consistantly and avoids having to create additional tables.