Group & Bridge tables ... and a Surrogate Key on the Fact table?

View previous topic View next topic Go down

Group & Bridge tables ... and a Surrogate Key on the Fact table?

Post  David Martin on Wed Mar 25, 2009 12:39 pm

I have a question about a design challenge that I’m facing regarding the use of a bridge table to handle a fact row associated with multiple rows of a dimension.

I have studied The Data Warehouse Toolkit with focus on the Multivalued Diagnosis Dimension section of Chapter 13 (p. 262-265).

I understand the concept and how to build the bridge table and the group dimension. My questions have to do with the following scenario:

The relationship between the group dimension and the fact table turns out to be 1:1

• Wouldn’t the group dim table be doing nothing more than providing a surrogate key for the fact table?
• And if that is so, would it be permissible to use a surrogate key on the fact table (eliminating the need for the group dim table) and allow the bridge table to serve as a standard cross-reference table?
• If that is ok, then what if the relationship (group to fact) is 98% 1:1 and only 2% 1:M? Would it be worth the additional query complexities of having the group dim table for the sake of 2%? (even the query on the load would be odious)

I have a second set of related questions…

• Can MS analytics cubes navigate the group/bridge correctly?
• How about with multiple group/bridge structures?
• How would it handle a surrogate key on the fact table and the XREF/Bridge table (no group dim)?

Thank you for your time.

David E. Martin
Tyson Foods, Inc.
IS Data Modeling

David Martin

Posts : 1
Join date : 2009-03-25
Location : NW Arkansas

View user profile

Back to top Go down

Re: Group & Bridge tables ... and a Surrogate Key on the Fact table?

Post  ngalemmo on Mon May 18, 2009 3:15 pm

It would be helpful if you were more specific as to what the group is supposed to be.

If, as you say, the group has a 1:1 relationship with the fact table, then it doesn't make a lot of sense to have a group. But the only thing I can think of that would fit a situation like that would be a MySpace friends list...
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