How to deal with SCD type 2 dimension and duplicate dimension records?

1.2k views Asked by At

I have recently implemented SCD Type 2 - Customer Dimension for OLAP reporting. We used to have it on Type 1 previously, where customerID was key to built dimension, but since this dimension will get duplicate records, e.g. C_001 has two records, I think building dimension using same CustomerID as key would be impractical. Should I consider Surrogate as primary key, and use 'CustomerID' as an attribute?

Is this best practice with SCD Type 2 dimensions?

Customer Dimension snapshot pic.

1

There are 1 answers

0
Jose Bagatelli On BEST ANSWER

The surrogate key should certainly be used as the primary key, however this will not solve the main issue here.

Your business key - which was previously your customerID - must also be unique in order to assign the correct surrogate keys when building your fact table, otherwise you will create duplicates if you only use the customerID.

Looking at the data sample you've provided your business key must be composed of customerID and Region.

Although, I have to point out that your model looks a bit strange as a customer should always have a unique id assigned to it, and if the case is that a customer can have offices in different regions then a more suitable model would have a Location Dimension attached to the fact which would tell you the exact region (or city, or country and so on) a given fact relates to.