What are Natural Key and Surrogate Key in SQL Server Analysis Services?

A Natural Key is a type of key in a table which uniquely identifies each record and has a business meaning attached to it.

Example: Incident/Ticket Number, SSN, Employee Email Alias, etc.

Here are some highlights of a Natural Key:

This can be numeric, string, a combination of both etc. depending on the type of data you are dealing with. In case of non-numeric data, JOINs are slower and occupies more space than an integer.

History cannot be maintained if used as a Primary Key.

Merging data from different source systems can be difficult when there is a conflict in the type of value coming from each source system.

A Surrogate Key is a type of key in a table which uniquely identifies each record, but has no business meaning attached to it. It is merely a value used to uniquely identify a record in a table.

Example: Identity Columns, GUID (Unique identifier), etc.

Here are some highlights of a Surrogate Key:

Most of the times this is a numeric value (like INT, BIGINT, etc.) and hence the JOINs are faster and occupies less space than character based data.

Allows for maintenance of history data (SCD Type 2) when used as a Primary Key.

Data coming from multiple source systems can be easily integrated (especially in the scenarios like Mergers, Acquisitions etc. in the industry).