Entity Relational Modeling
vs.
Dimensional Modeling

Surrogate Keys

• A substitution for the natural primary key.
  
  
• It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table.
  
  
• Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Oracle sequence, or SQL Server Identity values for the surrogate key.
  
  
• It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.
  
  
• In a data warehouse, a surrogate key is a necessary generalization of the natural production key and is one of the basic elements of data warehouse design.
  
  
• Every join between dimension tables and fact tables in a data warehouse environment should be based on surrogate keys, not natural keys.
  
  
• It is up to the data extract logic to systematically look up and replace every incoming natural key with a data warehouse surrogate key each time either a dimension record or a fact record is brought into the data warehouse environment.
  
  
• One of the most important use of surrogate Key the need to encode uncertain knowledge (When you have a I donýt know" situation, you may want more than just this one special key for the anonymous customer )

ER to Dimensional modeling

Single Entity Relationship – breaks down into multiple Facts

  1. Convert ER to set of dimensional modeling
  2. Select many-to-many facts
  3. De-normalize remaining tables

Back to Entity Relational Modeling   vs.  Dimensional Modeling Index