Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Actual Date vs Date Key in Fact table

3 posters

Go down

Actual Date vs Date Key in Fact table Empty Actual Date vs Date Key in Fact table

Post  KKumar 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

Back to top Go down

Actual Date vs Date Key in Fact table Empty Re: Actual Date vs Date Key in Fact table

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Actual Date vs Date Key in Fact table Empty Re: Actual Date vs Date Key in Fact table

Post  BoxesAndLines Fri Jun 29, 2012 9:22 am

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

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

Back to top Go down

Actual Date vs Date Key in Fact table Empty Re: Actual Date vs Date Key in Fact table

Post  KKumar 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

Back to top Go down

Actual Date vs Date Key in Fact table Empty Re: Actual Date vs Date Key in Fact table

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

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

Back to top Go down

Actual Date vs Date Key in Fact table Empty Re: Actual Date vs Date Key in Fact table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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