Home
Kimball Group Home
Search
Register
Log inGroup & Bridge tables ... and a Surrogate Key on the Fact table?
2 posters
Page 1 of 1
Group & Bridge tables ... and a Surrogate Key on the Fact table?
David Martin 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
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
Re: Group & Bridge tables ... and a Surrogate Key on the Fact table?
ngalemmo 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...
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...
ngalemmo- Posts : 3000
Join date : 2009-05-15
Location : Los Angeles -
Similar topics» Bridge tables versus massive junk dimensions
» Bridge Table - To simply group a dimension in more than one way?
» Type 2 Fact table - Durable surrogate key?
» separate fact table/different grain - do I need a bridge table
» Fact surrogate key as foreign key in another fact table
» Bridge Table - To simply group a dimension in more than one way?
» Type 2 Fact table - Durable surrogate key?
» separate fact table/different grain - do I need a bridge table
» Fact surrogate key as foreign key in another fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum