Thank you! Just what I was telling myself while reading this (CTE/Scalability).

Also, when you design a warehouse for a customer that is not necessarily very versed in T-SQL, CTEs etc. telling them that you can have a dimension that describes the specifics of a date and use that as a FK can be very helpful. Seasonal activity, weekday/weekend activity etc. is much easier I think with such a concept. I try to have the best of both worlds: I keep the full datetime in my fact table and I also link it to a date dimension and to a time dimension. This way it's easy to query either way 🙂