Date attribute in Dimension

Is it ok to have a date attribute in a dimension such as contractor_registered_date_key in the contractor dimension

Dim_contractor
---------------
contractor_key
contractor_name
address 1
address 2
city
state
contractor_registered_date_key

Thanks

Yes.
And I wouldn't bother making it a key, I'd simply leave it as a date.

Date key or just plain date is OK. If you plan to to query the date information by Year, Month, quarter, want to include the day, etc then use the Date_key. But if you just want it and don't foresee any heavy lifting on the date, the stick with the date.

That means if I plan to query the date info I have to join the contractor_dim table to dim_date but you always join dimension and fact and not dimension to dimension. So what is the right approach in this scenario?

The right approach depends on how you plan to use the data.

It's not unusual to model data that is frequently used in a query differently than you would data rarely queried. And while not recommended, you can on occassionally snow flake your design.

Just put the date. There's an index on the date column in the date dimension. If you occasionally want to join to the date dimension using the date you're simply doing the same thing the ETL does on a lookup. If I always want to join, I normally put the date key in the fact table.

I think B&L is spot on. By default, don't snowflake any dimension attribute unless you can justify it reasonably, and I don't see any point why date dimension should be treated differently, especially given that you could also derive many other calendar attribute values through RDBMS/report system functions.

Along B&L's line, you could also add a relevant date key in the fact table, if you really need to, as well as keeping the date attribute in the dimension table. It would be far more flexible than snowflaking the dimension and only to find out you don't need any other calendar attributes but the date attribute. Being inflexible, you may have to join to date dimension many times in order to get the date values. I have seen dimensions with more than 10 role playing date keys and only found out the dimension should really be modeled as fact table, mostly accumulating snapshot.

Location outrigger is one of few Justifiable dimension snowflakings as you most likely need the whole location hierarchy whenever a location is relevant. Without location FK, there is no other efficient way to make available other higher level location attributes which are shared in many dimensions.