Actual Date vs Date Key in Fact table

View previous topic View next topic Go down

Actual Date vs Date Key in Fact table

Post  KKumar on Fri Jun 29, 2012 12:09 am

Hi,
I have a dilemma designing this fact table regarding the dates
Fact_Job
--------
fact_job_key - SK
job_key - NK
contractor_key
job_type_key
job_estimated_start_date
job_start_date_key
job_end_date_key
job_amount

The business user wants to slice and dice by job_start_date and job_end_date but not by job_estimated_start_date though they would like to see the estimated start date when creating a report. I was wondering if I should keep the job_estimated_start_date as an actual date instead of creating a key which will then link to dim_job_estimated_start_date (a view of dim_date) just to avoid unnecessary join.

Does this violate any fundamentals regarding the fact table?

Thanks

KKumar

Posts : 22
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Actual Date vs Date Key in Fact table

Post  ngalemmo on Fri Jun 29, 2012 2:07 am

Yes, it violates the basic principle that, when given a choice, users will eventually do all of the above. Use an FK and sleep well at night.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Actual Date vs Date Key in Fact table

Post  BoxesAndLines on Fri Jun 29, 2012 9:22 am

Joins are not bad. Joins are good. Repeat 10 times and then return to modeling.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Actual Date vs Date Key in Fact table

Post  KKumar on Fri Jun 29, 2012 10:29 am

ngalemmo - Thanks so much. I really appreciate your efforts in advising and educating people regarding dimensional modelling/DW.

"Joins are good". I agree with this statement but not completely true. Isn't it true that one of the reasons we don't encourage snowflakes is because of the joins which can impact performance.

Thanks

KKumar

Posts : 22
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Actual Date vs Date Key in Fact table

Post  BoxesAndLines on Fri Jun 29, 2012 2:26 pm

KKumar wrote:ngalemmo - Thanks so much. I really appreciate your efforts in advising and educating people regarding dimensional modelling/DW.

"Joins are good". I agree with this statement but not completely true. Isn't it true that one of the reasons we don't encourage snowflakes is because of the joins which can impact performance.

Thanks

You are correct. Fact to dimension joins are good. Snowflake joins are bad. It's not so much the performance aspect, as any relational database can perform joins very efficiently today. It goes more to keeping the model simple and easily navigatable.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Actual Date vs Date Key in Fact table

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