Here is my issue :
Dimension:
---------
Viewer Unit Dept
------ ---- ----
V1 ABC 1
V1 ABC 2
v1 DEF 1
V2 ABC 1
V2 DEF 2
V3 ABC 2
V3 DEF 1
Fact Table
-----------
SeqNumber Viewer Amount($)
---------- ---- -------
1 V1 10
2 V2 20
3 v1 8
4 v3 50
The dimension and fact are joined by viewer. When these tables are rolled out for query studio users, they drag and drop viewer and $$amount and this results in following:
Viewer Unit Dept Amount
----- -- --- ------
V1 ABC 1 18 (10+8)
V1 ABC 2 18
V1 DEF 1 18
V2 ABC 1 20
V2 DEF 2 20
V3 ABC 2 50
V3 DEF 1 50
or
Viewer Amount
----- ------
V1 54
V2 40
v3 100
Amounts are counted multiple times as viewers are in different units and different depts. Is there any way to first get unique viewers and then get the amounts??? or Any way to model in framework manager to fire nested sqls?
Please help!!
I think that cardinality in your model is wrong it should be n:1 , but in your case it is n:m since viewer is not unique in the dimension. You need to build a dimension where viewer is unique or add a field to the existing dimension that adds an index on the viewer. In the last scenario you can add a condition 'index = 1' to stop the overcounting..