Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Why Surrogate Keys are Needed for a SQL Server Data Warehouse


By:   |   Last Updated: 2018-07-24   |   Comments   |   Related Tips: More > Database Design

Problem

A SQL Server data warehouse generally receives data from multiple sources. For dimension tables, typically an additional surrogate key is included with the other attributes. This tip explains advantages and necessities of having surrogate keys in SQL Server dimension tables.

Solution

In a typical data warehouse design, a surrogate key is included in Dimensions tables. The following covers the reasons to have special surrogate keys in Dimension tables.

Protect the Data Warehouse from Unexpected Administrative Changes

If you are not using a special key as a primary key in a dimension, your only option is to include the current business key as a primary key of the dimension table.  In the case of a data warehouse, we are dealing with millions of records which can span decades.  In the case where we have included a natural business key as the primary key of the dimension table, the same key will be included in the fact table. Since data warehouses do not have control over the source systems, it is important to know that the business key can be changed over time due to administrative and operational needs.  When this change occurs, the entire fact table needs to be updated. Mind you, we are taking about millions of records and updates to that many records will have other implications such as the table not being available until the update completes.

In the case where we include a surrogate key to the dimension table, it is only a matter of changing the dimension records which won’t be in the order of millions, as fact tables are linked with the surrogate key. Therefore, updating dimension records won’t have a major impact to the data warehouse system.

Let's look at the below example which is taken from the AdventureWorksDW sample database. In this example, the CutomerKey is the surrogate key in the DimCustomer Table.

dimcustomer and factsales tables

In case CustomerAlternateKey is modified, which is the business key of the Customer dimension, you only have to change the dimension table not the fact tables. Both data changes and data type changes can be handled with this method.

Integrate Same Data

As said previously, a data warehouse receives data from multiple sources. Sometimes, the same dimension will receive data from multiple sources. For example, for an organization who has employees at multiple sites, in the data warehouse you need to include all of them in one dimension for analysis purposes. In the case of OLTP, these data sets are maintained in isolation and they are unique in isolation. When these data are integrated into the data warehouse, there can be cases where the same primary key is used for multiple records in different locations. Since primary keys cannot be duplicated, loading data into the data warehouse will fail if you try to extract and load both records to the data warehouse.

Since we are including a surrogate key, duplicate issues will be fixed as the surrogate key is the primary key in the dimension table.

Add Rows to Dimensions that Do not Exist in the Source System

A dimension table can have records which are not in the source systems.  When fact tables are updated, there can be multiple surrogate keys for one fact table.

Let's look at the FactSales fact table.

factsales fact table

In this fact table, for a given record, the ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey and CurrencyKey are surrogate keys which should be updated. Since data is being received from multiple sources, there can be cases where one or two dimensions are not available. If these are not available, you have to ignore the entire record and foreign key violations will occur. To avoid this, there will be need to be manually entered records in the dimension. Typically, 0 or -1 will be used as a surrogate key which will be tagged in the fact table without violating foreign key constraints.

Provide the Means for Tracking Changes in Dimension Attributes Over Time

This is something where you can’t avoid surrogate keys in dimension tables.  As we know, keeping historical data is essential in a data warehouse. Therefore, dimension changes need to be tracked in the data warehouse.  To facilitate historical tracking in a dimension, type two slowly changing dimensions (SCD) are used. Details of this is item are discussed here.

In type 2 SCDs, a new row is added. If you don’t have the surrogate keys, there is difficulty of adopting the historical tracking in the data warehouse. With surrogate keys, a new record will have a different surrogate key to the previous records.

Maintaining Junk Dimensions

As we are aware, there are many statuses and flags in OLTP systems. Ideally, these need to be mapped to a dimension table in the data warehouse which will end up with a large number of dimension tables. However, to ease operations, multiple statuses are combined to one junk dimension table. Since this dimension table does not exist, you need to add a primary key which is a surrogate key.

Performance Compared to Larger Character or GUID Keys

As said multiple times in this article, large volumes of data are used in a data warehouse. Fact tables and dimension tables mainly join via surrogate keys. If those are integer columns, it will be better performing than using character columns.

Recommendations

Typically, an auto increment integer column is used as the surrogate key in a dimension table. Normally, surrogate keys do not have any meaning except for a surrogate key in the date dimension. In a date dimension, YYYYMMDD format is used mainly to enhance the data partitioning.

Next Steps


Last Updated: 2018-07-24


next webcast button


next tip button



About the author
MSSQLTips author Dinesh Asanka Dinesh Asanka is a 10 time Data Platform MVP and frequent speaker at local and international conferences with more than 12 years of database experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools