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

How to handle a Time component when using a Date Dimension

2 posters

Go down

How to handle a Time component when using a Date Dimension Empty How to handle a Time component when using a Date Dimension

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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

Back to top

- Similar topics

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