• Sounds OK.

    I would make the Calendar table a lot longer, like to 2050 or something. The table won't be very big even with 365/366 rows per year that's only about 13,000 rows. Generally with a datawarehouse you are incrementally adding new data daily (or weekly or whatever) and if you get data after 2004 you won't have any way to link your fact table to your date (calendar) dimension. BTW, a table with dates is not really a time dimension, it's a date or calendar dimension. A separate time dimension would be useful if you are recording time of day, if you require that level of granularity.

    Another suggestion is to always use surrogate keys, even for calendar dimensions. I.e. don't use the date value as the key itself. Kimball has a good explanation of this here

    The basic reasons are that integer keys are quick and smart keys (with information encoded into the key) are generally a bad idea because it encourages them to be used in client applications and they lose their robustness. It also makes it awkward to have a calendar table that includes holidays for different geographical regions.

    You also get stuck if your source data has a bad date, it means you can do error handling so you have a way to link it to an "unknown" member of the date dimension.