Dealing with slowly changing dimensions

A slow changing dimension is one whose values vary across undefined time periods, that is, it can have different meanings depending on the time period context.

To illustrate the concept, consider the evolution of Joey, a support technician employee in a given company, over a certain period of time. When Joey joined the company, he had the Junior Support Technician position. Then, after one year, he gets promoted to Senior Technician. And now, one year later, has become the Support Manager.

Now, imagine you want to visualize the number of cases resolved by the entire support team over a three-year period and find out how many of those cases were resolved by junior technicians, how many were resolved by senior technicians, and how many were resolved by the support manager. If, for reporting purposes, we take Joey's current status in the company, all cases he has resolved in the last three years will be logged as if they were resolved by the Support Manager...