How to handle a Time component when using a Date Dimension

View previous topic View next topic Go down

How to handle a Time component when using a Date Dimension

Post  suchafunkymonkey on Wed Jun 17, 2015 10:39 am

We have a typical Date dimension with all the usual fiscal month, quarter columns etc which is at the day level.

We have a requirement to also start including the time of day in some reports. This time wouldn't be used in any aggregations or summary reports, it would only be used for reports at the lowest level of detail and even then only in a handful of reports.

In the source system, the date column holds both the date and time combined, so we usually extract the date portion and store that as the FK to the Date Dimension.

As we have no requirements to report using the time (other than showing the time in a detail report), would it be a reasonable suggestion to continue to store the date as we are now, but also store the time component in a separate column in the fact table and show that in the detail reports when required? Or should I create a Time Dimension even though we have no requirements to use it and store the FK for the time?

I want to implement the best approach, but not over-engineer a solution.

suchafunkymonkey

Posts : 1
Join date : 2015-06-17

View user profile

Back to top Go down

Re: How to handle a Time component when using a Date Dimension

Post  ngalemmo on Wed Jun 17, 2015 11:31 am

The only reason to create a time dimension is if time of day has attributes the business needs. This is typical for restaurants and sometimes retail. If there is no such need, common practice is to store the timestamp (date & time) as a column. Storing just time is also an option, but your DBMS may not have functions to parse out characteristics of a TIME data type.
avatar
ngalemmo

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

View user profile http://aginity.com

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