Can a fan trap occur to a conformed dimension table? Or it occurs exclusively for measure objects in Fact table?
I read so many documents on Fan trap but still I couldnt grasp the concept of fan traps
dillagi (BOB member since 2006-01-04)
Can a fan trap occur to a conformed dimension table? Or it occurs exclusively for measure objects in Fact table?
I read so many documents on Fan trap but still I couldnt grasp the concept of fan traps
dillagi (BOB member since 2006-01-04)
A simple summary of fan traps: they only occur when you have measures and dimensions at two different levels. For example, in the chain of tables below:
[A] -< [B] -< [C]
If you have dimensions from A, B, and C, but measures only from C, then you do not have a fan trap.
If you have dimensions only from A, and measures from B and C then you have a potential fan trap that can be resolved easily with the “Multiple SQL Statements for each Measure” option in Designer.
If you have dimensions from A and B and measures from B and C then you are still okay, using the setting mentioned above.
If you have dimensions from A, B, and C and measures from B and C then you have a fan trap that cannot be resolved via the Designer setting and you must take more detailed steps to resolve the issue.
Dave Rathbun (BOB member since 2002-06-06)
Dave,
Very useful and great information.!!
Briefly explained… wonderful points… to look for potential fan traps…
Thanks.
BO_Chief (BOB member since 2004-06-06)
You’re welcome.
Dave Rathbun (BOB member since 2002-06-06)
Excellent Dave. This fan trap had always been a big confusion to me. Your explanation makes much sese. So what should be my approach if I have that last case. Is it aliasing and defining contexts? When i alias the middle table, i will define all measures from this alias table, what about the dimensions from this middle table, should I pick them from alias or original middle table? Thanks for your help.
zenmasterbo (BOB member since 2006-02-15)
I am looking for answer to this question as well.
dillagi (BOB member since 2006-01-04)
What happend if dimensions are from A and C and measure only from B?
What happend if dimensions are from A, B and C and measure only from B? is still Fan Trap?
Philip (BOB member since 2006-03-02)
just to finish of with Philip’s questions so that it might help future viewers…
if you have measures in B and Dimensions in C then the solution will be to create contexts
GandalfTheGrey (BOB member since 2004-10-27)
you can leave them in the original table
GandalfTheGrey (BOB member since 2004-10-27)
When you have dimensions from all three tables(A ->B->C) and measures from B & C, there’s one hitch after applying the standard fix of creating an alias for the middle table (B) and creating two contexts.
The hitch is - when you have a where clause on the dimension object from table C, that will not get applied to the query that takes the measure from alias of middle table (B).
cpmohanraj (BOB member since 2002-09-23)
That’s correct. It’s possible to address that with a sub-query, but you can’t apply a condition to table C along with A and B and avoid the multiplication issue of a fan trap.
Or do you have an alternative suggestion to make?
Dave Rathbun (BOB member since 2002-06-06)
I do not have any other way than using a sub-query but this is little tricky for the business users who want to be able to have an intuitive universe.
cpmohanraj (BOB member since 2002-09-23)