A Generalized Approach to the Creation of Bridge Records

InfoManagement Direct, September 26, 2008

Jeff Prenevost

As applied to data warehousing, a bridge table relates a fact record to one or more dimension records.  If a given fact only ever relates to one dimension record, you don’t need a bridge table.  Where a fact has a one-to-many relationship with a dimension, or many-to-many, you may choose to handle that with a bridge table.

The bridge table approach described in this article identifies and numbers unique sets of dimensions and then reuses those sets, treating the set identifier as itself a sort of dimension key, which is put directly in the fact table.  It’s elegant and is particularly applicable to situations where you don’t need a weighting factor but, rather, all the analysis is impact analysis (i.e., the full amount of a metric is counted against all the applicable dimensions). However, the extract, transform and load (ETL) is a bit complex.

An alternative approach that is better in most situations is to use a traditional associative table, just like you normally would in a transactional system.  Assuming you have a fact table with a primary key “FK” and a dimension table with key “DK,” you simply create records with the pairs of keys as shown in Figure 1.

If the fact table’s primary key contains multiple columns, put all the columns in there.  You can add a “weighting” column to this, as in Figure 1, to avoid double-counting (unless you always want to double-count); when joining the fact through the bridge to the dimension, duplexing your fact records, you generally multiply the metrics by the weighting factor.

The admittedly minor issues with the associative table approach are:

1. It requires that the primary key of the fact be put in the associative table.  Unless you put a surrogate key on your fact tables, like you do your dimension tables, a fact table primary key may be a large collection of columns, perhaps even the collection of all foreign keys.  If your fact table primary key is large, then your associative table will be large.  Again, this particular issue goes away with surrogate keys on your fact table.
2. The associative table will contain at least one record for each fact.  Specifically, it will hold a number of records equal to the number of fact records times the average number of dimensions per fact.  If you have 10,000 facts each related to both “red” and “orange,” then you’ll have 20,000 associative table entries.  If space is a problem, this might also be a problem.  The approach described in this article reuses sets of dimensions and will result in much smaller bridge tables (at the cost of more complex ETL).
3. The traditional associative table does not easily allow you to predefine acceptable combinations of dimensions.  In a situation when you want to predefine such combinations, the model described in this article may be better.

Another way to handle a one-to-many relationship between a fact and a dimensional attribute is to make a delimited string of all the applicable dimensional attributes (assuming they are, or can be converted to, strings) in one fact table column.  For instance, if one of your dimensional attributes is “color,” then you could make a “Colors” attribute as some longish VARCHAR() data type directly in your fact table, and populate it something like Figure 2:

Page 1 of 10.