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.
2 comments:
Do you recommend surrogate key for time dimension as well?
Dear BlackFish,
The main goal of the surrogatekey is to store slowly changing Dimensions Type 2. For other words, is when you need to store historical data for your dimensions and you need to add a new row in your dimension. Example
COD01 Peter SomeValue
COD02 BlackFish SomeValue
If you need to know whats is the value of your "somevalue" in the last year, how can you know that?
So, using surrogate keys you will transform your dimension on:
SK BusKey Name
1 COD01 Peter SomeValue
2 COD02 BlackFish Somevalue
3 COD02 BlackFish SomeValue2
And for each row you new to add a begin and end date to catch the values for each period of time...
in time dimension you should use an integer as sequencial ID to improve performance!!
I hope this helped...
kind regards!!
Post a Comment