Fact Table should store POINT IN TIME FK or LATEST RECORD FK from SCD-2 Dimension ?

Introduction

Slowly Changing Dimensions (SCD) are dimensions that change over time. SCD type 2 is a method of tracking the changes in the attributes of a dimension over time. It involves creating a new row in the dimension table whenever there is a change in any attribute value and assigning a surrogate key (SK) to each row. The SK is used to link the dimension to the fact table, while the natural key (NK) remains unchanged.

A fact table that implements SCD type 2 can have different types of FKs depending on the business requirements and the level of granularity. Some common types are:

• Point in time FK: This is a FK that references the SK of the dimension row that was valid at the time of the fact occurrence. This type of FK allows us to analyze the facts based on the historical state of the dimension.
• Latest record FK: This is a FK that references the SK of the most recent dimension row for a given NK. This type of FK allows us to analyze the facts based on the current state of the dimension.

A fact table that implements SCD type 2 can have both types of FKs for the same dimension, depending on the analysis needs. For example, a fact table that records sales transactions can have both a point in time FK and a latest record FK for the customer dimension, so that we can compare the sales performance based on the customer’s historical and current attributes.

Consider a fact table that records sales transactions. The table has the following columns:

• Transaction ID: A unique identifier for each transaction.
• Customer ID: A FK that references the customer dimension table.
• Product ID: A FK that references the product dimension table.
• Transaction Date: The date of the transaction.
• Quantity: The quantity of the product sold.
• Price: The price of the product sold.

The customer dimension table has the following columns:

• Customer SK: A surrogate key that uniquely identifies each row.
• Customer NK: The natural key that identifies each customer.
• Customer Name: The name of the customer.
• Address: The address of the customer.
• City: The city where the customer lives.
• State: The state where the customer lives.
• Zip Code: The zip code where the customer lives.
• Effective Date: The date when the row became effective.
• Expiration Date: The date when the row expired.

The fact table can have both a point in time FK and a latest record FK for the customer dimension. The point in time FK would reference the customer SK that was valid at the time of the transaction, while the latest record FK would reference the most recent customer SK for a given customer NK.

Conclusion:

SCD type 2 is a powerful method of tracking the changes in the attributes of a dimension over time. By implementing SCD type 2 in a fact table, we can analyze the facts based on the historical and current state of the dimension. I hope this tutorial helps you understand the role of SCD type 2 in a fact table.