BRIDGE TABLES

View previous topic View next topic Go down

BRIDGE TABLES

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

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

tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: BRIDGE TABLES

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.
avatar
ngalemmo

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

View user profile http://aginity.com

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