When working on a data warehouse, a suitable analogy to this problem is that we have Health Practices. Health practitioners have a number of professional attributes and work in an open number of teams and in an open number of clinical areas.
For example, you may have a nurse who works in child care in a number of teams as an assistance officer / contractor / bank employee. Or you may have a recently appointed doctor who works with general medicine, who spends time in a special field, awaiting qualifications as a consultant in this special field.
Thus, we have an open number of areas of work and an open number of teams, we cannot have team 1, team 2, etc. in our measurements. Other attributes can change over time as well as the base location (where they work), the main team, and the area in which they work.
So, after Kimble I went to outriggers:
DimHealthProfessionals table:
Key (primary key, identity)
Name
Main Team
Main Area of Work
Base Location
Other Attribute 1
Other Attribute 2
Start Date
End Date
OutriggerHealthProfessionalTeam Table:
HPKey (foreign key to DimHealthPRofessionals.Key)
Team Name
Team Type
Other Team Attribute 1
Other Team Attribute 2
OutriggerHealthProfessionalAreaOfWork table:
HPKey (as above)
Area of Work
Other AoW attribute 1
If any HP attribute changes, or a combination of teams or areas of work in which they work, we need to create a new entry in SCD and its outrigger tables to encapsulate it.
And we do it in SSIS.
HP , , HP.
, HCP, HCP .
, , , HP , , DIM .
- ? ?