I'm trying to wrap my head around creating a slowly changing dimension and joining said dimension to a fact table. I've been reading the Analyzing Data with Microsoft Power BI and Power Pivot for Excel book for the past few hours and its not clicking for me.
So the current situation is that we have a file that contains a list of everyone who works in the department and this is updated as an when it needs to. This is currently used as a dimension in reports and the main (and valid in my opinion) complaint from managers is that should one of their team switch to another team or area then all their historic figures go with them. This is obviously not ideal and I want to improve it.
I have a copy of the Excel file used and modified it to suit. This is an example of what it looks like;
| VPManager | VPName | AreaManager | AreaName | TeamManager | TeamName | Employee | EmployeeNumber | AppID | EmployeeEmailAddress | TeamManagerEmailAddress | AreaManagerEmailAddress | TeamMangerFrom | TeamManagerTo |
| Skye Moss | Customer Service | Will Lloyd | Will Lloyd's Community | Ray Bradley | Accounts 1 - Ray Bradley | Leigh Matthews | 1234567 | 123 | Leigh.Matthews@example.com | Ray.Bradley@example.com | Will.Lloyd@example.com | 01/10/2017 | 30/09/2018 |
| Skye Moss | Customer Service | Jess Watson | Jess Watson's Community | Emerson Barrett | Accounts 2 - Emerson Barrett | Leigh Matthews | 1234567 | 123 | Leigh.Matthews@example.com | Emerson.Barrett@example.com | Jess.Watson@example.com | 01/10/2017 | |
So if the employee were to change team or area then we'd create a new row.
The book explains how to get the correct granularity of both the dimension and the fact and it this that I'm having the trouble with. I know I can just list all the months that each employee was in a team/area and then create a key of EmployeeNumber-Month (e.g. 1234567-10/2018, 1234567-11/2018...) but this would generate lots of rows that essentially are the same bar the month.
Essentially I'm looking for advice on how you all to this efficiently, any suggestions?
