Fixed v Changing v Historical attribute conflicts

We have an issue in a SCD where a number of records may be presented that have changes to their attributes of EVERY type.

Example,

BusinessKey: xxxxxxxx

BuildingTypeId: 7

BusinessUnitHistoryId: 4019

BusinessUnitId: 4019

CurrencyId: 26

DevelopmentTypeId: 14

MarketId: 182

Name: abcdefgh

CurrencyId is a fixed attribute

MarketId & BuildingTypeId and the BusinessUnitId & BusinessUnitHistoryIds are historical attributes

Name is a changing attribute

The behaviour of the ETL seems to suggest that if fixed attribute changes are detected, these rows will error and therefore the changing & historical attributes will NOT be amended during the SCD transformation. Is this correct... as it seems to be what is happening.

Yep, that's an error. A fixed attribute cannot change. How do you propose the SCD handles that scenario? To me, it's bad data and should be redirected.|||

Thanks for the confirmation Phil.

I figure we are going to amend CurrencyId from Fixed to Changing as it seems they just overwrite at source and do not keep the history.

So, fixed attributes aside, what happens if the same row contains both a changing attribute amendment AND a historical attribute amendment - do both get successfully changed or does one type take precedence over the other?

Let's say (in the above example) both the Name (changing) and MarketId (historical) had changed.... do both get done or just one... if it's just one, do you have a suggestion as to how we reflect both changes?

Thanks for your help

Will

|||The historical attribute change will always occur, and I think there's a setting on whether or not to change all historical instances with changing attributes.|||

Yes,

Ah i think i get it...

I'm aware that if a changing attribute is detected, you can elect to change all instances of that record.

What I was unclear about is whether during a single SCD pass, if a record contains a field which is a changing attribute AND a field which is a historical attribute, do BOTH get done ?

From what you have said, the historical attribute will get done AND the record will also pass down the changing attribute route if such a change is detected. ?

If that's that case then hats off the MSFT... I just have a sneaky feeling that only one will get done.... maybe I just need to set up some samle data and test this.

|||It works correctly in my test. The new row is inserted as a result of a historical attribute change with the changed attribute being written to it at the same time. If you tell it to change all attributes, they get changed as well.

Think about it -- it's just input into the historical row -- so when it writes the new row it just reads in the value of whatever's in that column. As for updating all of the "expired" rows, that's similar behavior to a multicast and is executed independent of the historical row change.

If that isn't clear, just know that it will work!|||

No... it's clear enough Phil and I never had cause to question it before..... You know when you start questioning stuff like this you've obviously been working on a problem for too long!!

Thanks alot

Will