BRIDGE TABLES

Hi

In my data warehouse I have the following two dimensions

Customers and Accounts, a customer can have 1 or more accounts, both are SCD Type 2 dimensions. To show the relationship between the dimensions I am considering creating a bridge table.

Do I need start and end attributes (as well as indicator flag) on the bridge table rows when one or both of the dimensions change ?

Regards

Tim

Technically, what you are referring to is a factless fact table, not a bridge.

A bridge table sits between a fact and a dimension table. In this case, you wish to associate two dimensions. Dimension associations are maintained by fact tables. Same effect, different terms.

Because both are type 2's, maintaining such a fact table can be a bit annoying. As anything changes in either dimension you would need to regenerate all associations for the changed customer and/or account, with appropriate adjustments to the expiration dates of the old fact rows. This is certainly doable and is necessary if you need to track every relationship with every version of the dimension.

However, if all you need are current state relationships, you may consider adding a type 1 key to each dimension and using the type 1 key in the association table. With this approach you only need to maintain the fact when a new relationship exists or an old one expires. It is a much smaller and simpler to maintain but does not provide an easy way to see historical attributes between the associations.