April 30, 2017 at 8:44 pm
We are currently working on Call Centre data and they have 3 different shifts, "morning", "evening" and "night".
Initially we included this in the "dimTime" dimension as a "CallCentreShift" column (SCD2), but this isn't sufficient as shifts are also determined based on whether it's a weekday, weekend or public holiday.
We're trying to find the best strategy that would support a handful of shift "types" and support other departments in the business which may have different shift hours. I'd love to hear from you on how you'd do this.
We're considering something like:
A "dimBusinessHours" dimension that will have a row for each department shift type, e.g:
SurrogateKey DepartmentKey ShiftKey ShiftDescription
1 CallCentre Morning Calls Received between 6am - 12pm
2 CallCentre Evening Calls Received between 12pm-6pm
3 CallCentre Night Calls Received between 6pm-6am, weekends or public holidays
4 ITSupport Day IT Support Standard Business Hours - 6am-6pm
5 ITSupport Night IT Support Outside Business Hours - 6pm-6am
The shift times are set in a "reference / user data" table and the dimension will be built from this table. We haven't got the table layout figured out yet but probably something like: DepartmentKey, ShiftKey, ShiftStartTime, ShiftEndTime/Duration, IncludeWeekend, IncludePublicHoliday... or maybe it should have the days of the week for further granularity?
Please tell me you have better suggestions?
How is this usually/supposed to be managed in a Kimball data warehouse?
Any way to nicely include this with the existing dimTime and dimDate dimensions?
May 1, 2017 at 11:38 am
I don't deal with shifts, but I deal with different fiscal years across clients. Depending on the client vertical, they may have different ways of rolling up their quarterly data that is not the same for another client within the same vertical. This would be similar to having 3 shifts where the hours of operation is different between departments within said shifts. You would obviously need a way to differentiate those shifts so they relate to the business.
In practice, I normally handle quarters by rolling up every data by day. Week is not an option because even week start and end can be different too, but everyone is using the same daily rollup. The same applies in your case, but hourly. Hour is a hour and they all use hours. Thus, storing the data at the granularity of the hour can go across all business units and shift times in the data warehouse.
Where the true rollup and split happens is in the data mart. You can go from hourly granularity to shift based on the business requirements for that specific data mart. This gives you the greatest flexibility to create custom Time dimensions that can be easily changed if the business changes while not impacting your data warehouse grain.
The question is, have you thought about using data marts and have you thought about custom DimTime tables that enforce the specific shift rollups you need per business unit? I totally understand if hourly is too grainular for you to support, but it's likely one of your best options here to store the data regardless of the shifts and most importantly, shift hours. The data mart will allow you to totally delete and restart with a fresh new set of shift hour definitions that has no impact on your warehouse.
May 1, 2017 at 12:26 pm
On the hourly aspect too, think about how you handle half-hour's. If you have a shift that starts at 6:30 versus 6:00
May 2, 2017 at 2:52 pm
Thanks for the reply xsevensinzx!
Instead of creating a separate date-like dimension, we thought about creating a new dimension that will hold the surrogate key and exposing only the shift "name".
Note, fairly loose planning below, we haven't started building anything yet.
Secondly, apologies about the table layouts below, I couldn't figure out how to compact the columns.
Our current thought is to use a "reference table" where shifts can be added or times changed:
==============================================================================
| DepartmentKey | ShiftKey | ShiftDescription | ShiftStartTime | ShiftEndTime | IncludeWeekend | IncludePublicHoliday |
| CallCentre | Day | Between 8am and 5pm, and not on Weekends or Public Holidays | 08:00:00 | 17:00:00 | N | N |
| CallCentre | Evening | Between 4pm and 11pm, and not on Weekends or Public Holidays | 17:00:00 | 23:00:00 | N | N |
| CallCentre | Night | Between 11pm and 8am, including Weekends and Public Holidays | 23:00:00 | 08:00:00 | Y | Y |
The dimension table (let's call it "dimShifts") will look similar to the reference data table, though we won't expose the "ShiftStartTime", "ShiftendTime", "IncludeWeekend", "IncludePublicHoliday" or any of the other DW specific columns to the users. These are just used by the fact to figure our which surrogate key/shift to pick.
=============================================================================================================================
| SurrogateKey | DepartmentAlternateKey | Shift | ShiftDescription | ShiftStartTime | ShiftEndTime | IncludeWeekend | IncludePublicHoliday | RowIsCurrent |
| 1 | CallCentre | Day | Between 8am and 5pm, and not on Weekends or Public Holidays | 08:00:00 | 17:00:00 | N | N | 1 |
| 2 | CallCentre | Evening | Between 4pm and 11pm, and not on Weekends or Public Holidays | 17:00:00 | 23:00:00 | N | N | 1 |
| 3 | CallCentre | Night | Between 11pm and 8am, including Weekends and Public Holidays | 23:00:00 | 08:00:00 | Y | Y | 1 |
| 4 | AnimalServices | Business Hours | Between 8am and 5pm, and not on Weekends or Public Holidays | 08:00:00 | 17:00:00 | N | N | 1 |
| 5 | AnimalServices | Outside Business Hours | Between 5pm and 8am every day of the week, except Public Holidays | 17:00:00 | 08:00:00 | N | Y | 1 |
With the fact transform/load we'll probably hard code the department key (we haven't done any smarts around department name changes or dimensions yet) and then:
Does this seem reasonable or are we breaking some kind of fundamental data warehousing rule? 😉 Are we missing something that is going to bite us?
We use Microsoft SQL products only and instead of data marts, build SSAS cubes and perspectives. I'm sure they can be used together, but we've been getting away with just DW and cubes.
May 3, 2017 at 7:14 am
I've always considered SSAS as data marts too. One and the same to me, just different pros and cons over using SSAS versus a database or even both.
I think the only issue here is whether or not the business would ever change the shifts because you only have a single hour that can either shift forward or backward that can totally make two of those hours tied to an entirely different shift. For example, what is Day changes from 9AM from 8AM? What happens to all the 6PM records in the Evening shift? What happens to all the 8AM records in the Day shift? If the business requires you to retroactively recalculate those records within the shift rollup (which I assume is not a Fact table by hour or half-hour), then how does that happen across a large Fact table? Then of course, what happens if half-hours are used versus full-hours. That may require a reaggregation as the records used to rollup to the shiftkey from the source systems may tell a different story in the missing half-hour blocks that were not factored in.
This may never happen and or the business may be fine following the change only when it happens versus forcing retroactive updates (i.e.: relying only on the type 2 dimension). But what happens if it does and how does that impact your model? This is why I would likely stick to storing the data in hour or half-hour blocks without a shift key. Then use the SSAS cubes to roll up the data based on some type of dimShifts definition. That way recalculations can happen between the Data Warehouse and the Data Mart versus the Source Data and the Data Warehouse.
I mean, you have to pick a model regardless. No one is saying it's going to be perfect, but it's good to try to lock down as much of the business requirements as possible so you don't have to go backwards versus going forwards. You also want to pick something you can support, has value, and most importantly, scale as the business grows.
May 3, 2017 at 3:52 pm
mythan - Tuesday, May 2, 2017 2:52 PMOur current thought is to use a "reference table" where shifts can be added or times changed:
Really bad thought.
Times of a shift not to be changed ever.
A shift with a different time is a different shift and it must have a separate record in the table.
No records in the "reference" to be deleted or updated. Ever.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply