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!
Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!
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!