🧠Second Brain
Search
Bridge Tables
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:
- Historical Load: First, identify the multi-valued dimension IDs associated with each transaction ID in the source data.
- 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()
andFOR XML PATH
in SQL Server to concatenate and filter the groups. - 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.
Origin:
References: Trialog - Bridge-Tables in SSAS Cubes
Created 2023-12-19