Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Prague
    Posts
    3

    Unanswered: 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,

    David

  2. #2
    Join Date
    Feb 2003
    Posts
    109

    dont summarize based on this data

    dont 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.
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  3. #3
    Join Date
    May 2003
    Location
    Prague
    Posts
    3
    So you think there is no other way how to solve my problem. We have more of these cases.

  4. #4
    Join Date
    Feb 2003
    Posts
    109

    uh there are a hundred ways to solve this

    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...
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  5. #5
    Join Date
    May 2003
    Location
    Prague
    Posts
    3
    thanks for advice

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •