Business Objects : Fan Trap Explained

Fan Trap typically arrives in a situation when there One to many join in between two tables where Table at many ends again join to another table in One to many way.
To make it more clear this consider three table X,Y,Z  joined in a way
                     X-->Y-->Z 
where

• Table X-->Y joined in one to many way
• and again table Y-->Z joined in one to many way.

so such type of situation can "possibly" cause a FAN trap.yes Possibly.

since to cause a Fan Trap in database few more conditions need to be satisfied:-

1. when atleast One aggregate measure is taken from Table Y
2. and when atleast one aggregate measure is taken from table Z
3. and most important table Y holds aggregate values of Table Z.

Now lets take an example to understand it better :-

Consider three table :- Customer, Sale, SaleDetail

Customer joined with one to many to Sale table and Sale in turn joined to one to many with SaleDetail table.

MSSQL syntax:-

CREATE TABLE [dbo].[Customer]

    ( [CustomerId] [int] NULL,

[CustomerName] [varchar](50) NULL

   )

CREATE TABLE [dbo].[Sale]

   (

[SaleId] [int] NULL,

[CustomerId] [int] NULL,

[SaleAmountTotal] [int] NULL

   )

CREATE TABLE [dbo].[SaleDetail]

   (

[SaleDetailId] [int] NULL,

[SaleId] [int] NULL,

[SaleModelname] [varchar](50) NULL,

[SaleQty] [int] NULL

   )

lets Insert values to these three table :-

insert into Customer values(1,'John'); 

insert into Sale values(1,1,150);

insert into SaleDetail values(1,1,'Porche',2);

insert into SaleDetail values(1,1,'Mercedes',3);

Here hows the data look like in all the three table:-

Note:- Right now to keep the example simple I am considering single customer and single sale.In actual real world scenario its possible that a customer John can have multiple sales record and those sales record could have further multiple Sale detail.

So now lets focus what can cause a Fan Trap to occur.

if we query something like: For customer findout what is SaleAmountTotal with Indiavidual SaleModelName and SaleQty purchased.

query would be:-

Select Customer.CustomerName,sum(Sale.SaleAmountTotal) SaleAmountTotal,saledetail.SaleModelname, sum(SaleDetail.SaleQty)SaleQty
from Customer,Sale,SaleDetail
where Customer.CustomerId=Sale.CustomerId
and Sale.SaleId=SaleDetail.SaleId
group by Customer.CustomerName,saledetail.SaleModelname

you can see for Individual SaleModelName and SaleQty information is correct for customer John. But Did Jonh Pay 150$ for each Salemodel? NO . He total payment done is of 150 for all 5 total Saleqty .

SO what if someone query like that :-

Select Customer.CustomerName,sum(Sale.SaleAmountTotal) SaleAmountTotal, sum(SaleDetail.SaleQty)SaleQty
from Customer,Sale,SaleDetail
where Customer.CustomerId=Sale.CustomerId
and Sale.SaleId=SaleDetail.SaleId
group by Customer.CustomerName

Output :-

So in the above query just by removing SaleModelName dimension . We can see how the output changes.
So customer who does query something above,will be in impression that John has paid 300$ for 5 SaleQty. Which is not the case.

So what is the solution.