Many-to-Many dimensions

How to implement M-to-M dimensions in SQL2000 Analysis Services?
there is some solutions regarding what are your data and how you want to
aggregate your data.
Classic example:
Car 1 --> n Property n <-- 1 Sales
(1 car can have 1 or more property)
In this type of situation, you don't want to have 10 * sales if your car has
10 properties.
Parent-child dimension with data for non-leaf members is 1 option
2 cubes (1 for the detail and 1 for the aggregate) merged into 1 virtual
could also solve the problem.
but, can you explain what are your expected results?
"ps" <ps@.discussions.microsoft.com> wrote in message
news:17E69934-3EE0-47E7-8B85-77264E64865A@.microsoft.com...
> How to implement M-to-M dimensions in SQL2000 Analysis Services?

Many-to-Many dimensions

How to implement M-to-M dimensions in SQL2000 Analysis Services?there is some solutions regarding what are your data and how you want to
aggregate your data.
Classic example:
Car 1 --> n Property n <-- 1 Sales
(1 car can have 1 or more property)
In this type of situation, you don't want to have 10 * sales if your car has
10 properties.
Parent-child dimension with data for non-leaf members is 1 option
2 cubes (1 for the detail and 1 for the aggregate) merged into 1 virtual
could also solve the problem.
but, can you explain what are your expected results?
"ps" <ps@.discussions.microsoft.com> wrote in message
news:17E69934-3EE0-47E7-8B85-77264E64865A@.microsoft.com...
> How to implement M-to-M dimensions in SQL2000 Analysis Services?

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

manual way to rename instance of analysis server?

is there a way to manually rename my instance of analysis server 2005? i have a 32bit machine, and the 32bit version of sql server 2005 and analysis server 2005 installed, yet i continually get an error. this happens on my servers too, which are all 32 bit. does anyone know of a manual work around ( via regestry maby) to rename an instance?

You should not need to rename an instance.

Can you tell us what error you are getting? There may be another solution to your problem.

|||Yes, there is a way to rename the instance. Search for ASInstanceRename.exe program under %Program Files%\Microsoft SQL Server. But, as Darren has suggested, you might want to not do it right away because it might not fix your problem and make things even worse since it is unknown what is causing your current problem. The utility operates on registry, service control manager, some setup related stuff, performance counters, redirector etc.