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

View previous topic View next topic Go down

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

Post  Scoop on Wed Jul 01, 2009 10:27 am

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

Scoop

Posts : 18
Join date : 2009-02-10

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Jul 01, 2009 11:51 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum