Yeah in my case at first I tried to tackle the fact table like I would a Type 2 SCD. Part of my problem was that the data was spread among 6 different tables which I've been told the original solution "architect" (saboteur?) suggested in order to improve performance. There are conflicts between them and a lot of updates that don't align. The system schedules work to employees and there's more than one record per employee per day. Should be pretty simple but it certainly isn't. This architect was also very liberal in his use of dynamic SQL and cursors :pinch:. Thankfully the dev team is doing a rewrite at the moment. The tables will stay pretty much the same but no current stored procedure will continue to be in existence after the rewrite. All cursors and dynamic SQL will be eliminated... I hope.

Anyway, one of the things that was troublesome was when we had changes to the dimension tables without a change to an employee's schedule. Once entered, a record is "current" (like type 2 SCD) and if it doesn't change from the time it's entered until the end of time it will continue to be "current", but if a dimension changes (say the employee is now in a new team) but there were no other changes to his schedule, then there wouldn't be an update to force the update to the fact table. Since the schedule happens in the future, this would be problematic. I also had a fair bit of trouble trying to link together previous records, but that could be related to the data.

Due to the data quality issues, I didn't bother capturing all historical changes and just put in end of day changes, using the appropriate source table. Also, on the day after the employee's scheduled day, I update all the records for that day in the fact table, even if there were no changes. Then at least the keys to the dimensions are current as of that day, which is all that really matters. I could always add forced updates when certain dimension tables are updated but for our purposes I seem to be alright.

I can imagine some people having more complicated temporal scenarios than this one. At least someone out there is thinking 4th dimensionally.