many to many relationship in OLAP

Hi, this is a question about many to many relationship in Analysis services cube.

We have an Analysis services OLAP cube for reporting the amount of sold goods. One of the dimensions is customers another dimension is sales person. One sale person takes care of more than one customer and one costumer can be hold by more than one sales person. (many to many relationship, for connecting table customers and sales person I used intermediate table)

The problem is when one customer has, for example, two sales persons (A and B). If I chose just sale person A from sales person dimension everything is O.K. (Row area - customers, Column area - time dimension, Excel XP) but if I want to see how much was sold by both A and B, the data (amount of sold goods) is multiplied twice. (e.g. on 01.01.03 was sold to customer XX just 100 items and not 200 even though two sales person sold them) I am looking for something like distinct sum.

Can you suggest any solution to this problem?

Thanks in advance,

Daviddont summarize based on this data

you can:

a). not summarize based on this data

b). create a percentage of sales-- so that if 9 sales people are assigned to an account, then they each get 11% of the sales.|||So you think there is no other way how to solve my problem. We have more of these cases.|||uh there are a hundred ways to solve this

i would try to solve it on the database side, and not the OLAP side-- it is going to be a lot eaiser.

i deal with this all the time, and i have a cube for employee sales and then a cube for total sales.

let me look into this a little bit better..

im an olap developer and just generally avoid many to many.. but maybe there is a logical way to do this

(to be truthful, when i have a many to many, i shape the data using DTS in order to flatten it into a snowflake)--

isnt this just a snowflake schema?

maybe you could create a table that would assign a bunch of salespeople and then you assign the group to the record..

and allow drilldown to see what people are in a group--

but this seems oversimplified..

cant you just make a list of all of the sales people for each customer, and list it in text?

like you would push into a database field all of the sales reps for a particular order-- IE, 'John Smith, April Johnson, Mark Kay Latorneau' etc

this really wouldnt be that difficult to accomplish...|||thanks for advice