Store Business Hours in Time Of Day dimension?

View previous topic View next topic Go down

Store Business Hours in Time Of Day dimension?

Post  colorfool on Mon Jun 01, 2009 7:38 am

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:

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

View user profile

Back to top Go down

Exclude Branch Business Hour from Time dimension

Post  Jeff Smith on Mon Jun 01, 2009 9:28 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

I'll go with the percentage

Post  colorfool on Mon Jun 01, 2009 10:55 am

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.

colorfool

Posts : 3
Join date : 2009-06-01
Location : Nürnberg, Germany

View user profile

Back to top Go down

Re: Store Business Hours in Time Of Day dimension?

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