Multiple counts in Query studio

[cognos_users]

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!!

[blom0344]

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..