Create durable key in dimension

Ales Ventus 46 Reputation points

My Customer dimension has columns customer_key (surrogate key int), customer_number (ID from source system) and customer_name. I need to implement SCD 2 to track changes. Business case is that customer_number can change for customer in time. So, I need durable key to track SCD 2 for the correct customer.

I guess, I need this structure of dimension:


How to generate customer_durableID correctly?
Should id be generated in Staging? It is periodic snapshot load.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,234 questions
0 comments No comments
{count} votes

Accepted answer
  1. 21986829 481 Reputation points

    The business will have to decide with your guidance what the 'Business Key' will be for the Customer entity.

    That is not good that the Customer Number changes for a Customer. The question I would ask is 'Does the Customer Name change?' If no, then use Customer Name.

    If yes, then you have to get the business to commit to the Customer Number.

    Another option would be to investigate the Application that manages the Customer data and see if there is another way to track when the Customer Number changes - maybe it has its own surrogate key for the Customer row with a DateChanged/Updated column.

    Then, your ETL can have logic to know the Customer Number changed and your Dimension table can have an 'OriginalCustomerNumber' column to store the original value.

0 additional answers

Sort by: Most helpful