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

Datetime fields within a fact table

2 posters

Go down

Datetime fields within a fact table Empty Datetime fields within a fact table

Post  SathyJaanu Mon Jan 11, 2016 4:09 pm

can we have a datetime column in a fact table?in which scenario's is this correct to have a datetime in a fact table.
In my Facttable i stored date as int.They are just the milestone dates reported against each activity.I show those dates in reports.
So i dont see a purpose in storing them as int and joining with DIM tables to get the dates.So wanted to make sure if i can have them stored as datetime.

SathyJaanu

Posts : 20
Join date : 2015-10-07

Back to top Go down

Datetime fields within a fact table Empty Re: Datetime fields within a fact table

Post  ngalemmo Mon Jan 11, 2016 5:10 pm

Yes, that is fine. They would be degenerate dimensions.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Datetime fields within a fact table Empty Re: Datetime fields within a fact table

Post  SathyJaanu Mon Jan 11, 2016 5:24 pm

Is there any particular rule as in a date field to be used as int? or to use as Datetime?

SathyJaanu

Posts : 20
Join date : 2015-10-07

Back to top Go down

Datetime fields within a fact table Empty Re: Datetime fields within a fact table

Post  ngalemmo Mon Jan 11, 2016 6:11 pm

If you mean by 'as int' as a foreign key to the date dimension, it is when you need the attributes in the date dimension.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Datetime fields within a fact table Empty Re: Datetime fields within a fact table

Post  SathyJaanu Tue Jan 12, 2016 9:12 am

Got it
But my question is why would someone use a datetime or int in fact .If there is any filtering condition to be done in reports we go for int and
when we just have to show in a report we go as datetime?

SathyJaanu

Posts : 20
Join date : 2015-10-07

Back to top Go down

Datetime fields within a fact table Empty Re: Datetime fields within a fact table

Post  ngalemmo Tue Jan 12, 2016 3:15 pm

Why would you convert a DATE column to an INTEGER column to support filtering? Filtering a DATE data type works, and you have more functionality to compare and manipulate values.

However, the point of having a date dimension is to provide this functionality without end-users needing to know date functions, such as knowing the date is a Tuesday or belongs in a particular fiscal period. But, for uses such as effective/expiration timestamps, simply storing the value in the fact is usually good enough.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Datetime fields within a fact table Empty Re: Datetime fields within a 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