Forum Discussion
Star Schema with multiple bridge tables
Hi dirk74 - Although you mentioned that merging the two bridge tables is not ideal, it is the most straightforward solution in Power BI. A combined bridge table avoids ambiguity and ensures relationships are clear. If the current design doesn't allow this due to size or design constraints.
you can ensure that the relationships between your tables are clearly defined in Power BI:
1.The fact table connects to each bridge table through their respective keys.
2.Each bridge table connects to its corresponding dimension.
3.All relationships should have the correct cardinality (Many-to-One or One-to-Many) and cross-filter directions.
Approach 2. Define a measure in the fact table that calculates the required aggregation based on filters from both bridge tables and dimensions.
Start with Solution 2 (using a measure) as it allows you to keep your current design and solve the ambiguity for visualizations. If performance or usability becomes an issue, consider Solution 1 (combined bridge table) for simplicity and reliability.
rajendraongole1 wrote:you can ensure that the relationships between your tables are clearly defined in Power BI:
1.The fact table connects to each bridge table through their respective keys.
2.Each bridge table connects to its corresponding dimension.
3.All relationships should have the correct cardinality (Many-to-One or One-to-Many) and cross-filter directions.
Approach 2. Define a measure in the fact table that calculates the required aggregation based on filters from both bridge tables and dimensions.
Start with Solution 2 (using a measure) as it allows you to keep your current design and solve the ambiguity for visualizations. If performance or usability becomes an issue, consider Solution 1 (combined bridge table) for simplicity and reliability.
I dont have any measures in my fact table. I am just bringing master data and properties of a contact together.
Like a contact (a person) can have multiple functions within a company. He can also have multiple positions and areas. It is a classic many-to-many problem and I thought I could solve it by using 2 bridge tables. Because that is how you do it in propper star schema design. Mergin the bridge table into one bigger bridge table might be a solution but I consider that very poor design and I fear it will cause problems once I add more fact tables to my data model.
I simplified my data model, I created 2 new bridge tables, I double checked that there are no empty keys or duplicates or anything in the tables. The problem does not go away...
At this point it is clear to me that it is impossible to have 2 bridge tables in Power BI and I will consider using another product because this is unacceptable and I am not willing to waste my time any further with such an inferior product...