Deduping data at higher levels

Hi Stachu,

Thanks for your reply

I have my dimension tables (Contact and Geo) and fact table Sales as below..

Dim_Geo              Fact_Sales                      Dim_Contact

Geo_Key              Geo_Key                 Contact_Key

Region                 Sales                       ContactName

District

Territory

Output should be like this..

Query1:

ContactName         Region        District            Territory        Sales

Contact1                   R1               D1                   T1                 100

Contact1                   R1               D1                   T2                  100

Total                                                                                          $200

Query2:

ContactName      Region     District       Sales

Contact1                 R1            D1            100

Total                                                       $100

Having said that, Total should be automatically deduped at the higher levels... Sometimes we have duplicate data at District level as well, in that case data should be deduped at Next higher level at Region Level...

How this can be solved with single fact attribute (Sales). By the way, I'm providing Self Service BI, so there is no cushion to play around with the canned report.

Sreeni