Connecting Fact Tables in Microsoft Fabric: A Bridge Table Guide to Eliminate Duplicated Dimensions

In complex data models, multiple fact tables often share common dimensions (e.g., Date, Customer). Duplicating these dimensions leads to bloated schemas and maintenance headaches. This guide shows you how to solve this problem in Microsoft Fabric using a bridge table—a lightweight, key-only connector that centralizes relationships.

Why a Bridge Table?

• Problem: Traditional star schemas force duplicated dimensions when facts share context.
• Solution: A bridge table sits at the center of your model, storing only keys to link facts and dimensions.

Step-by-Step Implementation

1. Design in Lakehouse:
   • Use Fabric Lakehouse to transform data upstream for efficiency.
   • Create fact/dimension tables and extract keys.
2. Build the Bridge with Dataflow Gen2:
   • Use queries to join keys (e.g., FactSales + FactResellerSales → BridgeTable).
3. Configure Relationships in Power BI:
   • Establish many-to-many links from bridge to facts (bidirectional).
   • Establish one-to-many links from bridge to dimensions (bidirectional).
   • Mitigate performance issues with aggregations.

Best Practices

• Keep the bridge table lean (keys only).
• Document relationships to avoid circular dependencies.

Limitations

• Bidirectional relationships are unavoidable but manageable with careful DAX.

Conclusion
Bridge tables streamline your Fabric data models, reduce redundancy, and improve scalability. Try the bridge table approach today and let me know how it works for you!