Transaction Hour in Fact table or Separate Time Dimension?

Go down

Transaction Hour in Fact table or Separate Time Dimension?

Post  az_pete on Fri Dec 04, 2015 5:03 pm

Hello All,

I currently have a retail sales transaction fact table as presented here:

TransactionId (DD)
TransactionDate (DD)

The business users would like to now be able to view various facts by transaction hour. They want to view various sales metrics by the hour of the day when they were sold (i.e. Which items sold best during the 8AM hour? The 4pm hour?). Our current FactSales table is at a transaction line item level and at a temporal grain of a day (as indicated by the DateKey). However, we have the full Date/Time stamp of the transaction in the table as a degenerate dimension.

Would be better to simply add column to the FactSales table with the transaction time hour component or create a Time dimension table and have a separate TimeKey added referencing the new Time dim table?

My initial thought is to simply create a new column in the FactSales table that extracts the hour from the TransactionDate column. However, it seems that this would be something that shouldn't be in a Fact table. Namely, it's not a dimension key, degenerate dimension or a measure. Or could it be considered a degenerate dim since it is derived from one?

Going the Time dimension route seems to be a lot of extra overhead to simply materialize one value, especially since we would never need anything more granular than hour.

Any thoughts would be appreciated.


Posts : 2
Join date : 2015-12-04

View user profile

Back to top Go down

Re: Transaction Hour in Fact table or Separate Time Dimension?

Post  BoxesAndLines on Fri Dec 04, 2015 5:39 pm

Time is a dimension just like date. For example, your transaction date should be a FK to the date dimension. If all you will ever need now and in the future is a number between 1 and 24, I would add that column to the fact table as a DD. Otherwise, build a proper time dimension.

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

View user profile

Back to top Go down

Back to top

- Similar topics

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