After reading the F.A.Q here on the process of how to solve Fan Traps. I find my self asking more questions after reading it and giving it a shot on trying to resolve our issue with Fan Traps.
The questions i have are as follows.
What is the advantage to creating an aliased table as opposed to a derived Table?
Why link the alias/derived table (summary) back to the table that has the issue (detail)?
When it says create a context. Does this mean recreating the joins and the context for the aliased/derived table (summary)? or just a specific context for the join between the aliased table (summary) and issue table (detail) in question?
If i need to explain further please let me know!
Thanks
Adam
Here is some sample data and what we want to achieve…
Im not sure what other information I can provide in regards to table structure other than the example i have given
Our universe is a star / snowflake schema and the table in question is our fact table
When you say “Are you going to be doing calculations off this table” well i guess yes i am, i want the quantity summed correctly rather than duplicating and summing for each transaction in our fact table… Essentially i want the alias table to be able to show us a summary quantity for a given transaction.
I am using the information provided in the following link
Eg: it says at step 4 that we need to define a context for the original table and a context for the alias, does this mean replicating all the joins for the aliased table?
Can I order one chekcen burger and coke meal please?
the fan trap is created by your one - to many- to many relationships. isolate which tables have this relationship and break these relationships by using alias and if need be a context
I understand that is how the fan trap is created however, I cannot find any 1 > n relationships in my universe that link directly with our fact table.
I’m now wondering if i should break my fact table into a smaller table.
Our (Fact Table) has N, 1 relationships with our dimension tables.
This seems correct to me.
the quantity field is in our fact table. as per my original post the information is as that is shown…but i cannot make it effectively aggregate. it does work if i turn it into a measure but that is obviously useless.
Alias your table and join it to your original table, join on Tid. Create your Qty measure from the new alias table.
Create a context between the original table and the new alias table.
Create a context for this join. Whenever you have a context in your universe then (most of the time) you will need to include all other tables in at least one context, therefore whatever other tables the original table is joined to you should place in a different context.
Now select the multiple SQL statements for each measure.