Sample Header Ad - 728x90

Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships

1 vote
2 answers
2365 views
As an example, let's say I have a fact table with two dimensions and one measure **FactMoney table** ---------- ProjectKey int PersonKey int CashAmount money ---------- The two dimensions are defined like this: **DimProject (a type 0 dimension - i.e. static)** ---------- ProjectKey int ProjectName varchar(50) ---------- **DimPerson (a type 2 slowly changing dimension)** ---------- PersonKey int PersonNaturalKey int PersonName varchar(50) EffectiveStartDate datetime EffectiveEndDate datetime IsCurrent bit ---------- Pretty straightforward so far. Now I'll introduce a Person Category concept. **DimCategory** ---------- CategoryKey int CategoryName varchar(50) ---------- And build an M2M relationship between DimPerson and DimCategory **BridgePersonCategory** ---------- PersonKey int CategoryKey int ---------- So - people can have 1..n categories. My problem is - as Person is a slowly changing dimension, when a person's name changes, we add a new person row and update our effective dates and is current flags, no big deal. But what do we do with the person's categories? Do we need to add more rows to the bridge table every time a new person version pops up? And as a corollary, if a person's categories change, does that mean we need to create a new row in the person table? (please note I've also asked this question at stack-O and as directed by the first commenter, now posted this question here.)
Asked by Scott Herbert (193 rep)
Nov 1, 2013, 12:53 AM
Last activity: Mar 12, 2021, 11:04 PM