One of the most important things in dimensional modeling is the surrogate key, bust what is the advantages of it?
- It can improve the query performance, because they are numeric value and take less memory than business keys that usually made of string. Although this property limits the fact table width.
- It makes it possible to implement the Slowly Changing Dimension Type 2- because it allows you to have multiple business key in the dimension table with different surrogate key.
- It makes the design normal and the reporting simpler.
- It makes it easier to manage than having multi-value business key, because when you have a dimension that has multi-value business key as primary key, you will forced to add multi fields into fact tables and managing such as this situation would be difficult.
You can use [dimension name]AlternateKey name for business keys. It helps you to normalize the structure.