Aggregating Many to Many relationships

View previous topic View next topic Go down

Aggregating Many to Many relationships

Post  infinity3007 on Sat Nov 17, 2012 8:23 am

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?


infinity3007

Posts : 1
Join date : 2012-11-17

View user profile

Back to top Go down

Re: Aggregating Many to Many relationships

Post  Mike Honey on Mon Nov 19, 2012 11:07 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum