Linking Expenditures to Commitments [Q&A]

A reader working on a financial data mart has a question about linking commitments (such as purchase orders) to payments:

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)

Now cash flow can be understood properly in time (via day_key_effective) and the expenditures can be rolled up according to the date the funds were committed (using day_key_committed.)

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