Aggregating Many to Many relationships

Hi,

We have a many to many relationship between an incident and a Business Service.
The Business Service Dimension hierarchy is defined as

Business Service --> Country --> Domain


We have created a bridge table for the multiple relationships between Incident and Business Service.

But in the reports, when we are aggregating the counts at Country or Domain level, we are counting the duplicates.

For example - An incident can belong to multiple Business Services, say Incident I belongs to BS1, BS2 and BS3.
Now, a Country contains multiple Business Services, say Country C1 contains BS1 and BS2 and Country C2 contains BS3.

When the report aggregates at the Country level for Incident I,
for Country C1 , it shows a count of 2 and for C2 , it shows a count of 1.

Issue - For Country C1, it should show a count of 1 only since it is the same Incident I that belongs to BS1 and BS2 within the Country C1.

Please suggest, how should this be handled in the design?

Hi infinity,

You probably need a distinct count of your Incident key. This is normally best handled downstream from your DW e.g. in your OLAP/BI/Reporting layer.

Good luck!
Mike