Best practice for date attributes of dimension tables

[petegrant]

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

[ngalemmo]

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.