Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Aggregating Many to Many relationships

2 posters

Go down

Aggregating Many to Many relationships Empty Aggregating Many to Many relationships

Post  infinity3007 Sat Nov 17, 2012 8:23 am


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?


Posts : 1
Join date : 2012-11-17

Back to top Go down

Aggregating Many to Many relationships Empty Re: Aggregating Many to Many relationships

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

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

Back to top Go down

Back to top

- Similar topics

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