Forum Discussion
Fact and dim join in Semantic Model
hello Anonymous
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
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?
Hi Anonymous ,
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.