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

Manipulating Namespace Attributes

Hello All:
I have a fair amount of SQL 2000 experience and recentlty have begun
working with SQL 2005. SPecificall, I am working with the XML data
type in the following scenario:
-A third party vendor provides us with a data source containing a
column of datatype XML. I would like to search through the nodes of
the XML for specific elements and attributes. The problem is that some
of the XML fields contain namespaces (meaning that my xQuery must
declare the namespace as well), and some of the values in this same XML
column do not have namespaces declared. I have no control over the
data sent to me, but what I would like to do is remove the namespace
attribute where it exists. I have tried the modify method, but it does
not work on the xmlns attribute.
I can transform the field to nText and then parse this out, but I would
perfer to avoid any extra steps since it is a large quantity of data
and some of these xml fields are quite large.
I would be grateful for any advice on how to handle this.
Thanks in advance.
Rich FHave you tried to search using a namespace wildcard?
Like /*.foo/*:attr
namespace declarations are not exposed as attribute and cannot easily be
changed. If you need to change arbitrary namespaces to a specific namespace
you should probably write an XSLT transform.
Best regards
Michael
<eastegg_1970@.yahoo.com> wrote in message
news:1164911773.276725.95190@.j72g2000cwa.googlegroups.com...
> Hello All:
> I have a fair amount of SQL 2000 experience and recentlty have begun
> working with SQL 2005. SPecificall, I am working with the XML data
> type in the following scenario:
> -A third party vendor provides us with a data source containing a
> column of datatype XML. I would like to search through the nodes of
> the XML for specific elements and attributes. The problem is that some
> of the XML fields contain namespaces (meaning that my xQuery must
> declare the namespace as well), and some of the values in this same XML
> column do not have namespaces declared. I have no control over the
> data sent to me, but what I would like to do is remove the namespace
> attribute where it exists. I have tried the modify method, but it does
> not work on the xmlns attribute.
> I can transform the field to nText and then parse this out, but I would
> perfer to avoid any extra steps since it is a large quantity of data
> and some of these xml fields are quite large.
> I would be grateful for any advice on how to handle this.
> Thanks in advance.
> Rich F
>

Manipulating Namespace Attributes

Hello All:
I have a fair amount of SQL 2000 experience and recentlty have begun
working with SQL 2005. SPecificall, I am working with the XML data
type in the following scenario:
-A third party vendor provides us with a data source containing a
column of datatype XML. I would like to search through the nodes of
the XML for specific elements and attributes. The problem is that some
of the XML fields contain namespaces (meaning that my xQuery must
declare the namespace as well), and some of the values in this same XML
column do not have namespaces declared. I have no control over the
data sent to me, but what I would like to do is remove the namespace
attribute where it exists. I have tried the modify method, but it does
not work on the xmlns attribute.
I can transform the field to nText and then parse this out, but I would
perfer to avoid any extra steps since it is a large quantity of data
and some of these xml fields are quite large.
I would be grateful for any advice on how to handle this.
Thanks in advance.
Rich F
Have you tried to search using a namespace wildcard?
Like /*.foo/*:attr
namespace declarations are not exposed as attribute and cannot easily be
changed. If you need to change arbitrary namespaces to a specific namespace
you should probably write an XSLT transform.
Best regards
Michael
<eastegg_1970@.yahoo.com> wrote in message
news:1164911773.276725.95190@.j72g2000cwa.googlegro ups.com...
> Hello All:
> I have a fair amount of SQL 2000 experience and recentlty have begun
> working with SQL 2005. SPecificall, I am working with the XML data
> type in the following scenario:
> -A third party vendor provides us with a data source containing a
> column of datatype XML. I would like to search through the nodes of
> the XML for specific elements and attributes. The problem is that some
> of the XML fields contain namespaces (meaning that my xQuery must
> declare the namespace as well), and some of the values in this same XML
> column do not have namespaces declared. I have no control over the
> data sent to me, but what I would like to do is remove the namespace
> attribute where it exists. I have tried the modify method, but it does
> not work on the xmlns attribute.
> I can transform the field to nText and then parse this out, but I would
> perfer to avoid any extra steps since it is a large quantity of data
> and some of these xml fields are quite large.
> I would be grateful for any advice on how to handle this.
> Thanks in advance.
> Rich F
>