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: Some commissions only go to a single manager, and some go to multiple, and this can switch over time..
What I've done instead is hold ValidFrom, and ValidTo dates in the mapping table...
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 very small 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?!
One thing i thought about (but failed to actually implement) was just to hold an effective date, and filter using that and leverage LASTNONBLANK() or something?
Perhaps someone with some fresh eyes can help me out? Pulling my hair out here!
Ty.
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: Some commissions only go to a single manager, and some go to multiple, and this can switch over time..
What I've done instead is hold ValidFrom, and ValidTo dates in the mapping table...
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 very small 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?!
One thing i thought about (but failed to actually implement) was just to hold an effective date, and filter using that and leverage LASTNONBLANK() or something?
Perhaps someone with some fresh eyes can help me out? Pulling my hair out here!
Ty.
Last edited:
