This question is about business intelligence with sql server 2008 R2.

If you have a person named Jim Brown with his birthdate and social security number (SSN) being located in three different source system.

Do you use surrogate key that should contain Jim's birthday and SSN after you have merged three data row into single one/row in the ETL phase?

  • 1
    You'll need to provide more information about the context here. The answer will depend on a lot of other factors, including what you are using the data for, what other data you have (additional fields), what your queries will look like, etc. I would probably avoid using that data as a PK myself if for no other reason than it's an identity theft issue and you may need to obfuscate either the SSN, the birthdate, or both. – JNK May 27 '12 at 17:39

A surrogate key is a system assigned unique value to identify an entity occurrence. A natural key is what the business uses to identify an entity occurrence. The source systems, as well as your BI/Data Integration database, can use either type to identify the entity occurrence - such as Jim Brown in your example. In the source system we call what the source system uses to identify the entity occurrence a source key. So if you can have 3 different source systems each of which contain Jim Brown, each will have a different source key in addition to the natural key - which you have identified as the SSN + birthdate. The BI staging environment, which the ETL uses, will include a key map table which will map each source key to the assigned surrogate key for the BI database. So for example:

Person  Table
Surrogate Key   Name            Birthday     SSN          Other fields
1                   Jim Brown   9-15-1988    123456789  

Person Key Map          
Surrogate Key   Source Key  Source System   
1                   12345           System A    
1                   230383          System B    
1                   294829          System C

When a transaction against Jim Brown is to be processed, it will come from the source using the source key. Say its system A with source key 12345 with an update to one of the other fields. The ETL looks up the source key in the key map and finds it, and knows to apply that transaction to the row on Person with a surrogate key of 1.

If you only had 1 source system life would be easy. But you have 3. This is where the natural key comes in as you know you can identify Jim in any system by using these 2 characteristics that never change and are true about him. Now, say you integrate System D. The first transaction comes in for Jim Brown in system D, and system D's source key is AB2945. Now when the ETL looks in the key map, it doesn't find it. But since you know you have multiple systems that may contain a person, the ETL also does a lookup on the Person table for the natural key and voila - a match. Now the ETL inserts a new row to the key map for System D also for Jim Brown.

So in summary you always want to use a surrogate key as the PK in the BI database table when doing data integration of the same logical entity among many sources. You'll map it to the source key using a key map table, and prevent duplicates by looking up on the natural key in the BI table (along with an alternate unique key on the natural key to ensure no duplicates). Now there are many other details to work out - like is your natural key really unique, do you want to store it in your key map to prevent look ups on Person, do you have to handle composite source keys, do you keep a history of source key changes, do you track a system of record and systems of reference, how do you handle source key reuse (a potentially very sticky issue in its own right), etc. But this short summary gives you and idea of the differences between natural, surrogate, and source keys and where to use each in the BI scenario. I hope this helps...

  • I don't understand. The sentence "A natural key is what the business uses to identify an entity occurrence.", i dont get it. Do you have an example? What does Person Key Map take place? Is it in Stage phase? – What'sUP May 28 '12 at 10:07
  • The Birthdate + SSN is the example of natural key. Natural is "having or constituting a classification based on features existing in nature". (merriam-webster.com/dictionary/natural). These fields exist outside of the computer system and can be used to identify a person - thus they are "natural" keys. Surrogate is defined as "to appoint as successor, deputy, or substitute for oneself" (merriam-webster.com/dictionary/surrogate). The computer system assigns the surrogate key in place of the natural key to speed join, lookup, and ensure the ability to integrate. – Todd Everett May 28 '12 at 14:18
  • The key map table is used in the staging process - correct. The ETL that is extracting the person data from the source and loading it to the BI database will consult the key map in the staging phase to find the assigned surrogate key for that source key coming from the source system. – Todd Everett May 28 '12 at 14:19
  • If I correctly understand, you also have table person (that has a surrogate key) in the stage? – What'sUP May 28 '12 at 16:50
  • Basically yes. You have a table called say "Person_Key" in the stage area that maps the surrogate key to the source keys from the source systems. Then you just have a "Person" table in the BI database that uses that surrogate key to identify the Person. In my original example above, the table "Person Key Map" would be in stage, and the table "Person" would be in the BI database. Here is an excellent article series that explores these concepts in depth: blog.kejser.org/2011/08/04/…. I hope this helps. – Todd Everett May 28 '12 at 17:46

You are mixing up surrogate key and natural key. If you are using current data (i.e. "application data") as a key, it'd be a natural (composite, in this case) key. But a GUID or Identity field would be surrogate.

There are a few things to consider in this situation. First and foremost, is the combination of a birthday and SSN always going to be unique? Only you and your business domain can answer that question, unfortunately not us.

The other consideration is that this will probably not be sequential. The beauty of using an Identity column as a key is that it'll be sequential (as long as you don't do any identity inserts for whatever reason). When inserts into a clustered index aren't sequential, you will run into page splits and possibly severe fragmentation.

I hope these points help you out, and whereas they aren't a definitive answer like most things, "it depends".

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.