Yes I have searched QUITE a lot on this forum and I think I have covered most of the posts related to this topic. I saw a lot of posts exlaining how to solve them but haven’t come across any that describe the cause of traps. Here are somethings that I need help with:
Fan Trap:
D1 --< F1 --< F2
Fan trap will not exist if the fact tables above are at the same level of granularity no matter where the dimensions/measures are coming from. Is this a fair statement? If yes, then in real world scenario, there are barely any situations where we would need to join two fact tables…that too at different grains.
Chsam Trap:
D1 ---< F1
|
\|/
F2
Read somewhere on this board that this is because there is no valid relationship between F1 and F2 hence a chasm trap. But why do wee need a valid relationship as its just a conformed dimension that is being shared acorss two facts? What is the reason for a Chasm trap technically speaking? Should the two facts be at different grains for this to happen?
The fan trap is caused by the 1:N cardinality of the join between F1 and F2. If both F1 and F2 were on the same level of granularity the join would not be 1:N.
You do not have always only fact tables Imagine a transaction system where you have for instance these tables:
dates --< invoices --< invoice lines
This is a typical example where a fan trap needs to be solved.
The thing is that if you don’t solve the chasm trap in this case then BO presumes that it’s OK to create 1 single SQL statement if objects from all 3 tables are used. And that’s not correct. You need to have 2 separate queries - one that includes objects from F1 and D1 and one query that includes objects from F2 and D1. That’s what using contexts does in solving a chasm trap.
Thanks you so mcuh for the nice explaination, really appreciate that.
For Fan traps, we most of the time alias the fact table and create contexts right? Let me give you an example:
D1 -< F1 -< F2 - this is a potential fan trap if F1 and F2 are at different grains.
To solve this, we alias one of the fact tables(say F1) and create contexts -
Context1: D1 -< F1 - F1_Alias - This is for measures coming from F1
Context2: D1 -< F1 -< F2 - This is for measure coming from F2
In the above scenario, don’t you think we are still letting BO create a single query(for two fac tables) instead of seprating each fact table for its own query?
In the scenario you’ve described, you should derive your measures from F1_Alias and your dimensions from F1. F1 dimensions are still valid in F2 - each order line belongs to an order number, whereas with your F1 measures, order shipping cost shouldn’t be multiplied across each order line.
In the above scenario, if you want order shipping cost (F1 measure) and order line value (F2 measure) then 2 separate SQL statements will (or at least should!) be generated.
Thank you so much for your help. Think I got it now.
Though it may not be exactly correct, is it fair to say that in the fan trap scenario I just described, F1_Alias is acting like a fact and F1 is acting like a dimension. Please correct me if am wrong.
Imagine a transaction system where you have for instance these tables:
(BOB member since 2003-11-27)
(BOB member since 2006-02-06)