Many to Many relationship with Multiple Fact tables

[markmccoid]

I have a data model where multiple sales reps can own a single order and each rep can own multiple orders. 

I have a SaleRep table which has the orderID in it and an Order table. This forms the many to many link.

Now, I would like to bring another fact table into the mix.  This would be a Goals table.

I’m trying to figure out how to bring these together.  I’ve looked at generic keys, but either I don’t understand or it doesn’t work well with many to many relationships.

Has anyone ever doen something similar to this?  I have tried a link table with a key of:

     Month-Year | Rep_ID | Order_ID

I was able to get a link table to work and show Actuals to Goals when selections were made on Month-Year or SaleRep, but if a selection was made outside of this (like on CalendarYear…the main data table is attached to the Order table), then the budget would disappear.

Any thoughts?

[Oleg_Troyansky]

Hmm, this is a tough one... I'd try and eliminate the Many-to-Many relationship, if possible. The key question is how do you treat the "outcomes" when multiple reps share one order? Are they sharing the Order amount equally? or in a certain proportion? Or, do they all get credit for the same amount?

If the amounts are shared one way or another, I'd go as far as duplicating the Orders data and dividing the Amounts between the corresponding reps. This way, you eliminate the Many-to-Many relationship, the field Rep_ID can move into the fact table, and then you can combine the two fact tables either using a Link Table model or a Concatenated Fact model.

I describe Link Tables, Concatenated Facts, and Generic Keys in my book QlikView Your Business, check it out!

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

[markmccoid]

When multiple reps share an order, we have a "RepPercentage" field that determines how much of the order is theirs. This is for commission purposes, so sometimes both reps get 100%.

One of the problems that may also be hindering me is that this data model also has a SoldByRep, which is a traditional one to many relationship.  This is the default way users report on revenue.  I think I may need to break off this commission reporting into its own application.

Thank you for the insight.  I like the idea of creating the fact table based on the users percentage of the order and getting rid of the many-to-many relationship.  If I do this, then I believe that will necessitate a new application.

Do you have any preferred method of setting up the date table and month table to accommodate the different granularity between the Actuals fact table and the Goals fact table?

Will definitely check out your new book.  There is always so much to learn!

Thanks!