- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Fact and dim join in Semantic Model
We are trying to join a fact table to multiple dimension tables. Issue we are facing is semantic model does not allow multiple join between these fact and dim tables. It allows only one key column join. so we are creating conactenated key to join among these tables. Is there better approach to create semantic model?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hello @sadhikari_24
Instead of creating a concatenated key directly in the semantic model, incorporate a surrogate key in your ETL process. This key is built by combining the multiple natural key columns you need and then stored as a single column in both the fact and related dimension tables. This simplifies join definitions and maintains performance.
If this is helpful please accept the answer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @sadhikari_24
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @sadhikari_24
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you @nilendraFabric for response. Currently we are following the same approach. but every time we add surrogate key we will need to reload the dataset again. do you agree on creating hypothetical surrogate key for joining?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @sadhikari_24 ,
When adding a surrogate key, I understand your concern about having to refresh the dataset. Instead of creating a hypothetical surrogate key, try to use a bridge table. This table captures multiple key combinations required for joining, eliminating the need for reloading while maintaining flexibility and performance. may be This way, you don't have to modify the fact or dimension tables each time a new key is introduced.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hello @sadhikari_24
Instead of creating a concatenated key directly in the semantic model, incorporate a surrogate key in your ETL process. This key is built by combining the multiple natural key columns you need and then stored as a single column in both the fact and related dimension tables. This simplifies join definitions and maintains performance.
If this is helpful please accept the answer

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
03-26-2025 11:58 AM | |||
03-11-2025 02:22 PM | |||
02-19-2025 03:34 AM | |||
10-18-2023 08:56 AM | |||
03-26-2024 04:56 PM |
User | Count |
---|---|
12 | |
4 | |
4 | |
2 | |
2 |
User | Count |
---|---|
9 | |
9 | |
4 | |
4 | |
4 |