We have a small Contract tracking system that tracks 1 to many funding
sources per 1 to many contractors per a given Contract.



As the contract is making its way to completion - the funding sources can
have many amendments.



For example; a contract can start by having 2 funding sources that total
$60,000 ($40,000 from Fund1 and $20,000 from Fund2), that is effective from
January to March. Then in April; it was decided to increase the total
contract amount to $100,000, and draw from 4 funding sources and reallocated
some of the original fund sources. Fund1=$25,000, Fund2=$40,000,
Fund3=$35,000



My challenge in the data mart model is to answer a question such as "How
much contract dollars were spent in certain areas for a given time span?"
It would seem like it should be an easy thing for a cube to present.



I was thinking the pseudo code would be something like, "look at all
contracts and their amendments (some may never have been amended) and look
at the most recent snap shot of all their funding sources. Only include the
most current dollars, up to the end date in the time filter." However; that
is looking more like a report on a data mart instead of cube analysis.



I have/am reviewiong Kimball's thoughts on SnapShot type fact tables, but
I'm still struggling with the fact table more looking like a slow changing
dimension instead.



Anyone got some input to steer me in right direction?



Thanks