🧠 Second Brain


Search IconIcon to open search

Bridge Tables

Last updated Feb 9, 2024

Bridge tables are crucial in dimensional modeling to represent many-to-many relationships, where a fact table row is linked to multiple-dimension values and vice versa. This contrasts with the standard many-to-one relationship in dimensional models.

Two main types of bridge tables exist. The first type captures a static set of values linked to a single fact row, with no time variance. For instance, an emergency room record might list several disease diagnoses at the time of admission. The second type handles time-variant relationships, like a customer having multiple accounts over time, where the relationship changes.

Creating a static bridge table involves several steps:

  1. Historical Load: First, identify the multi-valued dimension IDs associated with each transaction ID in the source data.
  2. Create Initial List of Groups: In SQL, this means generating a unique list of these groups and assigning a unique key to each. This process often involves grouping, de-duplicating, and using functions like STUFF() and FOR XML PATH in SQL Server to concatenate and filter the groups.
  3. Create the Bridge Table: This step ‘unpivots’ the grouped data to form the actual bridge table, linking each group to its constituent dimension rows. Techniques like XML tagging and parsing (using commands like CROSS APPLY in SQL Server) can be employed.

For incremental updates, similar logic applies to new fact rows, mapping them to the correct group keys in the bridge table and adding new groups as necessary.

Bridge tables are powerful tools in ETL processes, enabling complex relationships to be modeled efficiently. They facilitate querying and analysis across dimensions that otherwise would not be directly relatable, greatly enhancing the versatility of dimensional data models.

See more on Design Tip #142 Building Bridges - Kimball Group.

References: Trialog - Bridge-Tables in SSAS Cubes
Created 2023-12-19