View previous topic View next topic Go down


Post  tim_goodsell on Wed Oct 06, 2010 8:07 am


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 ?




Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down


Post  ngalemmo on Wed Oct 06, 2010 12:42 pm

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.

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum