Multiple time-of-day slices

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,

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.

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

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

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?

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.

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,

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.

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

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

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.

So like this?

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

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

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