Store Business Hours in Time Of Day dimension?
2 posters
Page 1 of 1
Store Business Hours in Time Of Day dimension?
Hello,
I am building my first DataWarehouse and I got stuck on how to handle the time of day and my business hours.
I am trying to evaluate response times for support cases and I have multiple time zones with different business hours. I only need to break the time down to the minutes.
My idea was to store all timestamps in UTC and to create extra boolean columns in the time of day dimension for each locations business hours. So one row might look like this:
Or would the business hour flag be a fact of the support case action? Someone is doing work on a support case and I record the fact that he/she did it inside or outside of his/her business hours?
I would love to hear some input on this!
Axel
I am building my first DataWarehouse and I got stuck on how to handle the time of day and my business hours.
I am trying to evaluate response times for support cases and I have multiple time zones with different business hours. I only need to break the time down to the minutes.
My idea was to store all timestamps in UTC and to create extra boolean columns in the time of day dimension for each locations business hours. So one row might look like this:
- Code:
time_key: 1234, utc_hour: 8, utc_minute: 59, HQ_business_hour: yes, branch1_business_hour: no, branch2_business_hour: no
Or would the business hour flag be a fact of the support case action? Someone is doing work on a support case and I record the fact that he/she did it inside or outside of his/her business hours?
I would love to hear some input on this!
Axel
colorfool- Posts : 3
Join date : 2009-06-01
Location : Nürnberg, Germany
Exclude Branch Business Hour from Time dimension
I would exclude Branch Business Hour from Time Dimension. Including it in the time dimension could get really ugly, especially if they changed the branch's hours of operation or if it were open at different times on different days.
On the fact table recording the service call, include a service call dimension that had a field that indicated that the service call was made on or after business calls or maybe have a measure that contains the percentage of the call that was done during branch hours in case you have service calls that start during branch hours and extend past closing time.
Also, if you may want to created a fact table that lists the branches and their hours of operation.
On the fact table recording the service call, include a service call dimension that had a field that indicated that the service call was made on or after business calls or maybe have a measure that contains the percentage of the call that was done during branch hours in case you have service calls that start during branch hours and extend past closing time.
Also, if you may want to created a fact table that lists the branches and their hours of operation.
Jeff Smith- Posts : 471
Join date : 2009-02-03
I'll go with the percentage
Thank you for your reply, I think you are right.
I guess I will go with storing an overall time and a "within business hours" time in the fact table. So I just have to set up some helper tables for my ETL process to figure out the business hours part while transforming.
I guess I will go with storing an overall time and a "within business hours" time in the fact table. So I just have to set up some helper tables for my ETL process to figure out the business hours part while transforming.
colorfool- Posts : 3
Join date : 2009-06-01
Location : Nürnberg, Germany
Similar topics
» Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
» Time dimension design for cross time zone and custom calendar warehouse, 1min granularity
» Time Dimension, NULLs and Time datatype
» Date/Time Dim - Manufacturing business process
» How to store multiple hierarchies within a dimension
» Time dimension design for cross time zone and custom calendar warehouse, 1min granularity
» Time Dimension, NULLs and Time datatype
» Date/Time Dim - Manufacturing business process
» How to store multiple hierarchies within a dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum