Datetime fields within a fact table

View previous topic View next topic Go down

Datetime fields within a fact table

Post  SathyJaanu on 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

View user profile

Back to top Go down

Re: Datetime fields within a fact table

Post  ngalemmo on Mon Jan 11, 2016 5:10 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Datetime fields within a fact table

Post  SathyJaanu on 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

View user profile

Back to top Go down

Re: Datetime fields within a fact table

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

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

View user profile http://aginity.com

Back to top Go down

Re: Datetime fields within a fact table

Post  SathyJaanu on 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

View user profile

Back to top Go down

Re: Datetime fields within a fact table

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

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

View user profile http://aginity.com

Back to top Go down

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