Andrew P (7/20/2015)
For some companies, for example those dealing in health, I'd need to know how old a client is on a certain date, to identify that I would need to store the date of birth, and to avoid having to get a separate look-up to the source system I'd need it in the data warehouse.An example: how many clients under the age of five were given a certain medication in the month of August, 2014? I'd need to know which clients were given the medication between 1st August 2014 and 31st August 2015 and were under 5 on 31st August, 2014.
Is my thinking on this out-of-whack?
This is a pretty common requirement in health-related systems. I would recommend that you add a calculated column to your client/patient dimension, which calculates the age dynamically when queried. Assuming that you have the client/patient key in your fact table, having the age attribute in the dimension would allow you to do the above.
Your last statement somewhat contradicts the very first post, in the sense that you probably wouldn't need to use the date of birth in an aggregated form (month, year, etc.). If you do, consider adding the date of birth's key to your fact table(s). That will allow you to aggregate and slice by the date of birth's month, year, etc.
Hope this helps.