Multiple time-of-day slices

View previous topic View next topic Go down

Multiple time-of-day slices

Post  msoakell on Thu Mar 12, 2015 3:26 pm

Hi,

I've read the info about the Kimball setup for date and time-of-day dimensions, but am not sure how to model multiple time of day slices.

For example, at our 24 hour A and E, we would be interested in looking at fact_consultation (grain is 1 row per consultation) by:


  • clinician_shift (700 - 1500, 1500-2300, 2300-700)


  • a standard set of business defined time of day slices - (0800-1759, 1800-2159, 2200-0759)


  • general practice opening hours (there are 94 general practices each with their own set of opening hours)



The fact_consultation contains details for consultations at the 24 hour A and E and at general practices. This means that we would look at consultation records where the location was 24 hour A and E by clinician shift and/or the standard business defined time of day slices, but the general practice opening hours wouldn't be applicable to that record.

Can anyone help out with a suggestion as to how to model this please?

Cheers,

msoakell

Posts : 8
Join date : 2015-03-12

View user profile

Back to top Go down

Re: Multiple time-of-day slices

Post  ngalemmo on Thu Mar 12, 2015 7:09 pm

There are a few ways to do this. The simplest would be to add attributes to the time dimension to identify the different periods. If times are location dependent, then a bridge table with the location key, time key and some code or dimension identifying the period would also work.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple time-of-day slices

Post  msoakell on Thu Mar 12, 2015 7:12 pm

Thanks for that. What if the time of day slices are business unit or service dependent rather than location dependent?

msoakell

Posts : 8
Join date : 2015-03-12

View user profile

Back to top Go down

Re: Multiple time-of-day slices

Post  ngalemmo on Thu Mar 12, 2015 9:16 pm

Same basic idea, just substitute 'location' for whatever dimension applies.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple time-of-day slices

Post  msoakell on Fri Mar 13, 2015 2:25 pm

Great. Thanks. Given that we do a lot of grouping and filtering on hours when looking at wait times for patients , would you consider a time dimension with just the 24 hr time, minutes etc in addition to a time of day dim?

Or stick with fact-consult > bridge > dim-time-of-day

and apply grouping on hours and time periods using dim-time-of-day for both?

msoakell

Posts : 8
Join date : 2015-03-12

View user profile

Back to top Go down

Re: Multiple time-of-day slices

Post  ngalemmo on Fri Mar 13, 2015 6:17 pm

The time interval being represented in the dimension depends on what the business needs. If hours are good enough, that's fine, but it does box you in if someone requires a smaller interval. You should carry the timestamp as a degenerate dimension on the fact. This will give you the ability to deal with future changes in requirements should they come up.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple time-of-day slices

Post  msoakell on Fri Mar 13, 2015 8:52 pm

DimTime
DimTimeKey, Time, AM/PM, MilitaryTime, Hour, MilitaryHour, Minute
1, 7:30, AM, 07:30, 07, 30

DimTimeOfDay
DimTimeOfDayKey, StartTime, EndTime, DayPeriodDesc, DurationHrs
1, 07:00, 15:00, Morning, 8

DimBusinessUnit
DimBusinessUnitKey, BusinessUnit
1, 24 Hour Surgery
2, General Practice A

TimeOfDayBridge
DimTimeOfDayKey, DimBusinessUnit, RowEffectiveDate, RowExpirationDate, PeriodCode
1, 2, 01-01-1900, NULL, 'Clinician Shift'

FactConsultation
DimTime, DimTimeOfDayKey, DimBusinessUnitKey, ConsultationDateTime
1, 1, 1, 2015-03-15 07:30

So like this?

Do I keep dimTime AND dimTimeOfDay? Do I combine dimTime and DimTimeOfDay? or leave DimTimeOfDay as just the periods with start and end times?
Do I roleplay DimTimeOfDay so in this case it will actually be DimClinicianShiftKey on the fact?

Cheers,

msoakell

Posts : 8
Join date : 2015-03-12

View user profile

Back to top Go down

Re: Multiple time-of-day slices

Post  ngalemmo on Fri Mar 13, 2015 11:55 pm

The fact would not have the DimTimeOfDay key, and the bridge needs to add the DimTime key.

You would use the DimTimeKey in the fact to access the bridge, then use the bridge to access DimTimeOfDay. This would allow you to maintain different time groupings as well as changing the groupings by simply maintaining the bridge. If you store the DimTimeOfDay key in the fact, you would need to re-key all the fact rows if the business decides to redefine the groupings. DimTimeKey however, will never change unless somebody rips a hole in space-time… at which point, there would be more important things to worry about.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple time-of-day slices

Post  msoakell on Sat Mar 14, 2015 1:24 am

Ok, now I'm confused. How would I join the fact table to the bridge on dim_time? Do I need separate dim_time and dim_time_of_day dimensions. I understand the premise, but not the implementation

Dim Business Unit
DimBusinessUnitKey, Business Unit
1, 24 Hour Surgery

DimTime
DimTimeKey, Time, AM/PM, MilitaryTime, MilitaryHour, Hour, Minute
1, 7:30, AM, 7:30, 07, 7, 30

TimeOfDayBridge
DimTimeKey, DimBusinessUnitKey, StartTime, EndTime, DayPeriodDesc, DurationHrs, RowEffectiveDate, RowExpirationDate
1, 1, 7:00, 15:00, Morning, 8, 1/01/1900, NULL

FactConsultation
DimTimeKey, DimBusinessUnitKey, ConsultationDateTime
1, 1, 15/03/2015 7:30

msoakell

Posts : 8
Join date : 2015-03-12

View user profile

Back to top Go down

Re: Multiple time-of-day slices

Post  msoakell on Sat Mar 14, 2015 3:14 pm

Think I've got it. You mean like this:


msoakell

Posts : 8
Join date : 2015-03-12

View user profile

Back to top Go down

Re: Multiple time-of-day slices

Post  ngalemmo on Sat Mar 14, 2015 7:44 pm

No, the group keys do not go on the fact. You use the bridge to get the group.

The bridge contains the time of day and business unit keys. You use that to join the fact to the bridge. Users would then filter on the period code in the bridge to locate the appropriate row for the period they are interested in. The PK of the bridge would be time of day, business unit and period code. This allows the time of day group definitions to change without affecting keys on the fact table.

The size of the bridge will depend on your time of day interval, the number of business units and the number of groupings. Assuming your time of day interval is 1 minute, you have 100 business units and 5 groupings, the table will have 720,000 rows. If you are concerned with size, you could create separate bridge tables for each grouping. You may also consider moving the textual attributes into a dimension and reference them from the bridge.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple time-of-day slices

Post  msoakell on Sun Mar 15, 2015 5:58 pm

So like this?



msoakell

Posts : 8
Join date : 2015-03-12

View user profile

Back to top Go down

Re: Multiple time-of-day slices

Post  ngalemmo on Sun Mar 15, 2015 6:12 pm

Yes, assuming period code in the fact table diagram is a typo.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple time-of-day slices

Post  msoakell on Sun Mar 15, 2015 6:53 pm

So the periodcode doesn't sit on the fact table. You only access the bridge to get the period code when writing the report?

msoakell

Posts : 8
Join date : 2015-03-12

View user profile

Back to top Go down

Re: Multiple time-of-day slices

Post  ngalemmo on Sun Mar 15, 2015 10:40 pm

Yes, assuming there could be multiple periods that apply to the same time.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple time-of-day slices

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