Best practice for date attributes of dimension tables

View previous topic View next topic Go down

Best practice for date attributes of dimension tables

Post  petegrant on Thu Dec 08, 2011 9:20 am

We intend to use a date key and a time key for the datetime of sales in our product sales transaction fact table. These will join to a date dimension table and a time dimension table, as per standard practice.

It is a requirement to report on sales datetime offset from the datetime that each product was first released. This datetime of first release is a property of the product rather than of the individual sales transaction so it would appear to belong in the product dimension table.

What's the best practice for storing the product release datetime?
1) Store a product release date key and time key in the product dimension - it's ok to break the "don't join dimensions" rule by joining product_dim to date_dim and time_dim
2) Store a datetime field in the product dimension - it's ok to break the "always use keys for date and time" rule, and to lose ability to summarise by periods defined in the dimension tables
3) Store the product release date key and time key in the fact table - it's necessary to break the "minimise fact table size by storing information in dimensions" rule, even though we have very large data volumes
4) Something else

Thanks in advance,
Pete

petegrant

Posts : 1
Join date : 2011-12-08

View user profile

Back to top Go down

Re: Best practice for date attributes of dimension tables

Post  ngalemmo on Thu Dec 08, 2011 10:43 am

The general approach would be to just store the date in the product dimension. That should work in your situation. There is no need to overcomplicate things.

If, on rare occasions, you may want date attributes for that date, you can always join the date to the dimension. It is not best practice, but neither is snowflaking.
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