CODEWITHSUNDEEP
ssisetl
SamFenton
SamFenton

Reputation: 33

Issues with Slowly Changing Dimension Table with Primary Key

I have a package designed by another developer who used to work for the company. The package takes data from the source and inserts it into the destination. The slowly changing dimension task has 4 columns, set as historic attributes. Meaning it will insert a new row when any of the value changes. The business key is called PropertyID.

In destionation table, PropertyID is the primary key. When the package runs, we get primary key violation error. Which is understandable, because the destination table cannot insert a duplicate value when there is a change in the historic attribute. It is may be not the best design.

I want to correct this but i am not sure of the right approcah. I tried to add a new INT IDENTITY column (to use as a business key in SCD wizard) to the destination table and make the current PropertyID column as not primary key. But the INT IDENTITY column does not show up in the SCD wizard.

If someone can show me right approach to it, I would be much grateful.

Thanks.

Upvotes: 0

Views: 5857

Answers (2)

Aby Sheffer
Aby Sheffer

Reputation: 513

It looks like your are trying to design a Type 2 SCD as the changed records are getting inserted. In this case there should be a date field to track when a particular record was changed as well as to identify the current record. The primary key in the destination table should also be in property_id and a date field. You can refer the below link to check how is type 2 SCD Designed. http://datawarehouse4u.info/SCD-Slowly-Changing-Dimensions.html

Upvotes: 1

Jordi Prat
Jordi Prat

Reputation: 141

In a slowly changing dimension, the destination table will have two types of keys, the surrogate key which will tie out to the fact table, and the business key, which identifies the record from the source.

You do not want the business key as the primary key on the destination in a slowly changing dimension. That is the point of the SCD, you will have multiple rows per business key since you are tracking changes. If you are not wanting to do this, and your table is all type one changes (overwrites with current value), then the SCD transform is not what you want.

See this link ... https://en.wikipedia.org/wiki/Surrogate_key

Upvotes: 2

Related Questions