Q: Payments can be made after the end of the fiscal year in which the original commitments were made. So you could have some payments in February that apply to the prior fiscal year as well as payments made in February that apply to commitments made in the current current fiscal year.
How would you handle this in the time dimension? Do we need multiple rows for February 2010 -- one for funds committed in 2010 and another for funds committed in 2009?
J. in New York
A: You don't need to tinker with the time dimension. The key to solving this problem is to recognize that financial transactions have multiple dates associated with them.
For example, a payment may have an effective date (the date on the check) and a date of entry into the system, which may lag behind a bit. I've covered this concept in a previous post.
This reader wants to link the payment to a commitment, such as a purchase order. The purchase order may have been issued in the previous year, as in his example. We can acknowledge this in the design by adding an additional key in the fact table to reference the date of the commitment.
Hence, the payment fact table may have the following foreign key references to a DAY dimension:
- day_key_effective (the date of the check or payment)
- day_key_entered (the date it was entered into the system)
- day_key_committed (the date of the corresponding commitment)
If you wish to do this, it will be important to determine if/how the source system links each expenditure with a commitment. You will also find that for each payment, you can probably capture two transaction identifiers: one for the payment itself, and one for the original commitment. These may also factor into your payments model as dimensions.
- Chris
Image is from Public Domain Photos