OLTP source tables are having surrogate keys (numeric values) and natural keys (alphanumeric values), then can I skip creating surrogate keys in target OLAP DB (Dimensional Model) for dimension tables. I know that I will need surrogate keys for fact tables as unique key for fact table will be a large set and I will need a single columns with numeric values as primary key there. I am joining multiple source tables for populating data into one dimension target then I am wondering to use unique id (numeric values) of driving table (this id is inherited from OLTP source) as primary key , provided that data granularity of resulted record is at driving tables' id level (resulted record is the record after main source driving table is joined with other source tables).
What will be benefit of surrogate key in data warehouse layer?
Thanks, Rajneesh
Basically, surrogate key is an artificial key that is used as a substitute for natural key (NK) defined in data warehouse tables. We can use natural key or business keys as a primary key for tables.
These are some of the benefits of surrogate keys
Surrogate keys allow the Datawarehouse system to integrate the same data.
Surrogate keys enable you to add rows to dimensions that do not exist in the source system