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.


CategoryKey int

CategoryName varchar(50)

And build an M2M relationship between DimPerson and DimCategory


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.)


Does DimCategory have any Type 2 attributes that you are tracking?

If it is not, then you would only need to add a new record to your bridge table based on whenever a Type 2 attribute in DimPerson changes since you will be inserting a new record in DimPerson with a new surrogate key. You would then need to add or update your bridge table with this new surrogate key for whichever categories are assigned.

However, if an attribute on DimCategory changes, you would just update that record and there would be no change in the surrogate key so the bridge table would be unchanged.

Now if you want to track things like when a Category assignment to a Person changes as a Type 2 attribute, then you would need to build the Effective Start and End Dates in your bridge table to be able to tell at what point in time a Person's Categories change.

  • Thank you, I think your comment is in line with the (very rare) answer I've found here - community.altiusconsulting.com/… – Scott Herbert Nov 3 '13 at 22:10
  • I'm interested though in your idea for effective dates in the bridge table. For mine this introduces complexity to querying; the issue I'm attempting to avoid through building the DW. If we add a new row to the Person dimension each time new Person:category information is discovered, the SCD model should resolve itself (at the expense of the possible explosion of seemingly redundant person records). I'm also wondering how (if at all) SSAS and PowerPivot would deal with such a complex logic as start/end dates on a bridge. – Scott Herbert Nov 3 '13 at 22:31
  • 1
    Yeah, there would definitely be added complexity however having a Type 2 dimensions and throwing a bridge table in the mix each by themselves. I think it would be in your best interest to build a proof of concept with this to see how SSAS and PowerPivot would handle this. However, if SSAS handles it correctly, then your end user tools should connect through that and everything downstream should be fine. – njkroes Nov 4 '13 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.