system
December 5, 2012, 11:58am
#1
Hi,
I’ve got a join between two tables. One table has duplicated ID’s and when I join to that table (1,1) the measures are increased by how many times the ID is in the table. How do I resolve this?
sel (BOB member since 2008-04-23)
system
December 5, 2012, 12:04pm
#2
it is called a fan trap .
Your options:
Do not do the join or
Remove the duplicate IDs physically from the one table or
Create a view or derived table stripping out the duplicate IDs and then join it to your fact table. Or crate a bridge table.
Andreas (BOB member since 2002-06-20)
system
December 5, 2012, 12:39pm
#3
Hi!
Thanks!I know it’s a fan trap but what’s the best way to resolve this many to many relationship. I can’t remove the duplicates and I actually need this table to stay as it is becuase others tables join it the use this field.
sel (BOB member since 2008-04-23)
system
December 5, 2012, 2:12pm
#4
Hi,
There is a Designer’s FAQ for a fan trap:
What is a fan trap, why is it a problem, and how do I resolve it?
Dave Rathbun describes it well in his presentation, ZEN and the ART of Universe Design. Which is located here: http://www.dagira.com/conference-presentations/
Here is a quick synopsis of Fan Trap from his presentation:
A Fan Trap is a one - many - many relationship. This would happen if you are using a Summary and a Detail table in one query. Fan Traps can cause numbers to multiply. Business Objects resolves this t…
In case you would need it, there is also a Designer’s FAQ for a chasp trap:
What is a chasm trap, why is it a problem, and how do I resolve it?
Business Objects describes it best at this web site: http://tips.businessobjects.com/tips/unvdes/tip/ut001/ut001.htm
The Chasm trap occurs when two “many to one” joins converge on a single table. It is a one - many - one relationship. You will get incorrect results when the following circumstances exist:
A “many to one to many relationship” exists among three tables in the universe structure. The query includes obje…
Also search the forum for many many many discussions that talk about these two topics.
Marek Chladny (BOB member since 2003-11-27)