Forum Discussion
Power Participantwhy use surrogate key
Hi Jeanxyz,
As I mentioned these get into some pretty advanced data engineering concepts. If you're able to work without them and and you're happy, then continue being happy.
Here's a different example for where I'm using SKs:
I am building a dim_user table for my org, and I'm collecting various things: a user's Azure UUID, UPN, and legacy powershell PUID, and name.
One of the main use cases for this table is to use with Fabric audit data from the REST APIs, which identify users by UPN or PUID, not UUID.
To add a layer of complexity, PUID is not exposed to microsoft customers anymore.
My process is to first extract users from Azure using the Graph API, which gives me a list of UUIDs and UPNs and names.
I am then combing through the audit data to find records where PUID is populated as well as UPN so I can link in PUIDs - this results in not a complete list of PUIDs, but a workable list for active users in Fabric.
In my org, UPNs can change, which creates a challenge as the fabric audit data uses the UPN as the key to identify who caused the action.
I also don't want to need to fully reload my fact tables that depend on this dim_user table every night.
To achieve this, when I build my dim_user table, I am greating SKs on the fly, and saving them to a SK mapping table that maps Azure UUIDs to the generated SK.
Every night when I rebuild my dim_user table, using that maping table I am assigning users to the same SK every run so as to keep my fact table valid. This ensures that if a UPN changes, that user's record gets updated with the new UPN, but it maps to the same SK as their old UPN, and the audit data is still pointing to the correct user.
I am then able to incrementally update my fact tables without a worry that a changed UPN will make my audit data link to invalid users.
I hope this helps,
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution
Power ParticipantHi tayloramy , thanks for the explanation. However, I have been trying my best, but still don't see why it is necessary to create a surrogate key in a dimension table using SCD type 2. The truth is I have worked as a Power BI reporter and SQL Server admin for four years, having created dozen of reports whithout using any surrogate key.
I'm going to give you one example as below:
- Business need: to find out the total compensation cost per employee per day
- two tables available:
- employee-annual compensation table using SCD type2
Name total annual compensation effective from effective to Alice 50000 2024-01-01 2024-12-31 Alice 60000 2025-01-01 2025-12-31 - employee list
Name Alice John
- employee-annual compensation table using SCD type2
- solution: I created a employee-Date table in Power BI (see below). I then fill in the 'Effective annual compensation' of the employee on that specific day using a measure in Power BI. The measure basically goes through each row in the solution table, record the employee name and Date Key, it then goes to employee-compensation table, filter the table by employee name =Alice && 'effectve from' >= the Date Key of current row && 'effective to' <= the Date Key of current row. Once I got the 'Effective anual compensation' data, I can easily calculate daily compensation.
As you can see, I didn't create any surrogate key here and I don't think surrogate key can solve the problem here. The only benefit I can think of creating Surrogate key is that it helps with query planning in Power BI, but even that is suspicious since I have been able to create large data model without using surrogate keys.
Name Date Key Effective annual compensation Effective daily compensation Alice 2024-01-01 50000 137 Alice 2024-01-02 50000 137
Super UserHi Jeanxyz,
As I mentioned these get into some pretty advanced data engineering concepts. If you're able to work without them and and you're happy, then continue being happy.
Here's a different example for where I'm using SKs:
I am building a dim_user table for my org, and I'm collecting various things: a user's Azure UUID, UPN, and legacy powershell PUID, and name.
One of the main use cases for this table is to use with Fabric audit data from the REST APIs, which identify users by UPN or PUID, not UUID.
To add a layer of complexity, PUID is not exposed to microsoft customers anymore.
My process is to first extract users from Azure using the Graph API, which gives me a list of UUIDs and UPNs and names.
I am then combing through the audit data to find records where PUID is populated as well as UPN so I can link in PUIDs - this results in not a complete list of PUIDs, but a workable list for active users in Fabric.
In my org, UPNs can change, which creates a challenge as the fabric audit data uses the UPN as the key to identify who caused the action.
I also don't want to need to fully reload my fact tables that depend on this dim_user table every night.
To achieve this, when I build my dim_user table, I am greating SKs on the fly, and saving them to a SK mapping table that maps Azure UUIDs to the generated SK.
Every night when I rebuild my dim_user table, using that maping table I am assigning users to the same SK every run so as to keep my fact table valid. This ensures that if a UPN changes, that user's record gets updated with the new UPN, but it maps to the same SK as their old UPN, and the audit data is still pointing to the correct user.
I am then able to incrementally update my fact tables without a worry that a changed UPN will make my audit data link to invalid users.
I hope this helps,
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution