Date attribute in Dimension

View previous topic View next topic Go down

Date attribute in Dimension

Post  KKumar on Tue Jun 19, 2012 11:36 pm

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

KKumar

Posts : 22
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Date attribute in Dimension

Post  John Simon on Wed Jun 20, 2012 3:01 am

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

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Date attribute in Dimension

Post  Jeff Smith on Wed Jun 20, 2012 8:42 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Date attribute in Dimension

Post  KKumar on Thu Jun 21, 2012 8:53 am

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?

KKumar

Posts : 22
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Date attribute in Dimension

Post  Jeff Smith on Thu Jun 21, 2012 9:30 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Date attribute in Dimension

Post  BoxesAndLines on Thu Jun 21, 2012 12:44 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Date attribute in Dimension

Post  hang on Fri Jun 22, 2012 2:08 am

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Date attribute in Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum