Daily Changing Many To Many Association

[m1nkeh]

Hi,

I am almost at the end of my tether on a problem that i have in a model currently... basically, i have a 'temporal' many to many mapping table which maps commission rates for managers over time... these can change daily, (but do so rarely), so I've tried to avoid just having a huge table with the same values repeated albeit for different specific dates, if i do this anyway i end up with a 200 million record table, crucially though more than one manager can get commission for the sale of a certain product type :s

Note: Most commissions only go to a single manager... but SOME are many..

Every solution i come up with is deathly slow, and i just have no idea if there even IS a solution at this point... here is a link to a sample:- http://1drv.ms/1gOr7uw

The area that i think is the most troublesome is actually getting the correct "rate" for a given Manager, on a given day... the only way i can seem to be able to do this is nested SUMX, but there must be something slicker that I'm missing?!

Perhaps someone with some fresh eyes can help me out? Pulling my hair out here!

Ty.

 

[m1nkeh]

Edit: Someone will probably say why don't i do this in the ETL, what i've not shown here is that I have other measures that don't need to be split by the managers, but instead the full amount should be reported for each... but the total not a sum of all the managers (aka, default M2M behaviour)

Perhaps I need two fact tables? Perhaps someone could model my data in Excel to achieve a 'split' of the Facts, as i see it, whichever way the problem is cut *some* calc is going to need to be done at run-time. I think?!