Is this many to many?

I have a factSales table and a dimSalesRep table.

factSales:

DateKey

CustomerKey

SalesRepKey

AmountOfSale

CostOfSale

dimSalesRep:

SalesRepKey

SalesRepName

ManagerName

A single order may be split between more than one Sales Rep. Does this require a many to many relationship between factSales & dimSalesRep? If so, how do I go about structuring that?

I would say: it depends

If you want the same order with the full amount to be shown for both sales reps, then yes.

So if you want something like:

Order 1; Sales Rep A; 2000$

Order 1; Sales Rep B; 2000$

--

Total; 2000$

you can use the many to many by adding a bridge table. your new structure would be

factSales:

OrderKey

Customer Key

...

brdRepsToOrders (this is your new bridge table):

OrderKey

SalesRepKey

this bridge table you can now use to connect the SalesRep dimension to the fact table.

if you want to split the orders to the sales reps with different percentages i would not use many to many.

So if you want something like:

Order 1; Sales Rep A; 800$

Order 1; Sales Rep B; 1200$

--

Total; 2000$

you could double the fact rows in ETL or you could add a percentage column to the bridge table and use a view to double your fact rows. then i would use a regular relationship between dimSalesRep and factSales.

I guess thats not the only possibility but thats how i would do it.

kind regards

markus

|||

Thanks Markus!!!!

I actually simplified my situation a bit to make the question easier to understand. In fact what I have is a sales taking place that might have an "inside" sales rep and an "outside" sales rep along with a slight possibility of a third type of rep. So it looks like it's many to many for me.

Would the OrderKey and SalesRepKey be a combined primary key for the table? Then the SalesRepKey part points into the dimSalesRep table?

John

|||

JohnSLG wrote:

Would the OrderKey and SalesRepKey be a combined primary key for the table? Then the SalesRepKey part points into the dimSalesRep table?

exactly

|||

I'm having a Sr. moment here.

If I have a single int RepBridge key field in my fact table, how do I establish the foreign key relationship with the combined key in the RepBridge table? I have one field in the fact table and two in the bridge dimension. I get an error message about a UNIQUE constraint.

|||

you have to ignore your RepBridgeKey. What you need is the following:

dimReps brdRepToSales factSales

- -

RepKey <-> RepKey ...

... OrderKey <-> OrderKey

... ...

the primary key (or at least unique) for the bidge table is the combination of RepKey AND OrderKey.

then you have to use the bridge table as intermediate measure group for the many to many relation between Reps and Sales.

|||That's what I tried to do in SQL Server Mgmt Studio......it fails when I try to create factSales to brdRepToSales FK ....do not match existing primary key or unique contraint.....

|||

sounds like you are trying to add the foreign key in the wrong direction or you have records in your bridge table that do not match this constraint.

|||

dimReps brdRepToSales factSales

- -

RepKey <-> RepKey <-> RepKey

... OrderKey <-> OrderKey

If RepKey & OrderKey are part of a compoiste PK on BrdRepToSales then factSales has to have both keys in order to create a foreign key relationship. The only way I've been able to establish a FK link is to include both fields on both sides of the link.

I'm not sure I want to do this but that seems to be the only way it will allow me to link them.

|||

I am pretty sure that you don't want to do this because you don't want to have the sales rep key in your fact table or you don't need a bridge table at all.

you don't have to add foreign keys in your relational database. you just have to add this relations in your olap cube and you have to be sure that your etl takes care of it as well.

kind regards

markus

|||

I'm probably getting hung up at the relational end. For each sale I have the possibility of an inside rep and outside rep and an order rep. I may have any combination of the three or none; the same person can even be all three. For example Smith might be inside, outside, AND order rep or Smith might be only the inside rep with no help or Smith might be inside and Jones outside and Peters order rep.

I figured I needed a key field in the fact table to point to an intermediate table which had that key along with a rep key. There might be one, two, or three intermediate tables, each of which would point to the dim table that actually had the rep's name.

Perhaps I don't need the third table, as I could put the name and manager (the only other attribute) in the bridge table. It would still have a Bridge key that the sales table pointed to along with a rep key that let's it reference multiple reps for each sale.

factSales:

TypeKey PK, int

DateKey PK, int

CustKey PK, int dimRep:

RepBridgeKey PK, int <-> RepBridgeKey PK, identity

RepKey PK

RepName

ManagerID

ManagerName

I still couldn't make this an FK link in the OLAP, but I could link them in the cube like you said?

Thanks again,

John

|||That's not right....I think I still need the bridge table to point to a rep table, otherwise I'll be adding a lot of redundant information........AHHHHHHHHHHHHHHHHHHHH

|||

JohnSLG wrote:

For each sale I have the possibility of an inside rep and outside rep and an order rep. I may have any combination of the three or none; the same person can even be all three.

i guess you want to be able to know which role each rep had? then forget about the many to many aproach and just use the sales rep dimension 3 times in your cube (inside rep, outside rep, order rep). add a "no rep" member to this dimension and add the keys of all 3 reps to your fact table. so your fact table will look like the following

InsideRepKey outsideRepKey orderRepKey SalesAmount

-

0815 4711 42 800$

0815 0815 -1 (your no rep key) 1000$

If you want to use a bridge table you dont have to create a RepBridgeKey that is PK in the bridge table but you need a SalesKey that is PK in your fact table (like the order number or something like this). This PK of your fact table you have to use in the bridge table to build a bridge frome one line in your fact table to many lines in your rep table.

|||

This certainly seems like the most straight forward approach.....I guess I'm still suffering from Codd overdose....it's much simpler to put the three rep id's in the fact table.......

Which, unfortunately raises another question: most books I've read say to use surrogate keys instead of business keys in the fact table (which is probably why your solution didn't occur to me). What are the costs to using the business key for the sales rep(s) in the fact table (I'm using surrogate everywhere else).

Thanks again! I think this is the way I'm definitely going to go....this project is only going to get bigger, no sense introducing additional intricacies early on....

Johnsql