CODEWITHSUNDEEP
sql-serverdatabase-designprimary-keybusiness-intelligencecomposite-key
odew
odew

Reputation: 581

Composite key to surrogate key

I have a composite natural key from the data I'm getting. Using composite_key=ID-PRODUCT_ID-CLIENT_ID-OFFICE_ID , I want to convert this key in one surrogate key.

Example:

composite_key = 55-001-234-01 to surrogate_key = 123; this is the normal scenario, sometimes the office code can change but I want to identify the record as the same Ex: composite_key = 55-001-234-02 to surrogate_key = 123.

Upvotes: 0

Views: 460

Answers (1)

Cédric L. Charlier
Cédric L. Charlier

Reputation: 797

If two members with different OfficeID should map to the same surrogate key then it means OfficeID is simply not part of the composite key and is just a standard attribute with a type 2 (replace behaviour).

If your dimension is not too large, I'd suggest to use the simple Slowly Changing Dimension component avaialble in your ETL tool. If you don't have such a component, simply check with a lookup if the member is in your dimension is existing or not. If existing, apply an update to (eventually) change the OfficeID, if not apply an insert.

If you've a large dimension and performance issues, then it could be useful and improve performance by calculating a checksum for the set of attributes type 2. Your lookup should return this checksum and compare it to the checksum of your current row. If they are identique, the execution of the update statement is not needed.

Upvotes: 4

Related Questions