Fan Trap Identification

[Former Member]

Hello Experts,

As per my understanding of Fan Trap, if 3 tables are joined by 1:M relationship (1:M fan out in 1:M) and if we are taking measures from any 2 tables, then there is a potential Fan trap scenario.

Consider if there are 3 dimension tables with 1:M relationship or if there are 2 dimension tables and 1 fact table and measure is taken from any 1 table, then does Fan trap scenario comes in to picture?

Any guidance in above scenario is appreciated.

Regards,

Chinmay

[Former Member]

Combinations of dimensions and measures cause fan traps. I have a somewhat detailed blog post here that describes some different situations:

http://www.dagira.com/2008/03/03/do-i-have-a-fan-trap/

Suppose you have three tables, A, B, and C. Suppose that the relationships are:

A --< B --< C

If you have measures only from C you have no issues, no matter where your dimensions come from.

If you have dimensions from A and B and measures from B and C then you have a fan trap that can be solved using the "Multiple SQL statements for each Measure" option in the universe paramenters

If you have dimensions from A and B and C and measures from B and C then you have a fan trap that needs to be resolved with aliases and contexts.

Basically any time you have measures at two different levels, you have a potential fan trap. With a basic star schema where you have one level of dimension tables and one fact table you will never have a fan because measures only come from the lowest level table.

And yes, you can have a fan trap with only two tables. It does not require 3.

[Former Member]

Hi Dave / Ravi,

Thank You very much for your reply.

Dave, I am great fan of your blog and I always go through your articles. The way you explain any topic is just fabulousu2026..

After reading through your reply, my doubts about Fan Trap are crystal clear. Just one more additional question:

Just like Fan Trap, do we have the same logic/selection criterion to identify chasm trap as well? Because 3 tables with M:1:M need not always result in Chasm Trap.

Appreciate your time.

Regards,

Chinmay

[Former Member]

In my experience, many-one-many is always a chasm trap, whether you're looking at dimensions or measures. I would be interested to hear an example where it is not.

[Former Member]

Hi,

Trap does't come to picture if there are Dimension tables involved

Ex :

Say i have Region,Country and city tables

Join for this would be Region 1->M Country 1--> M City This is a perfect join

If in the above case in place of country and city if have fact tables then it would be perfect FAN trap.

As you could easily make out your fact table will have your transaction records and will have values for the same id's many times. If you join this to other fact table with 1:M join them your records will get multiplied and you will get possible more then expected records.

I hope i made my point,Let me know if you need further information on this.

Thanks,

Ravichandra K