Building a financial data mart? Don't let the multiple dates associated with accounting transactions confuse you.
A standard feature of financial systems often befuddles star schema designers. When someone inputs a transaction into a financial system, the transaction has at least two associated dates. One represents the date that the event happened, and the second represents the date the transaction was entered into the system. The first is usually called the transaction date, while the second is called the date posted. Sometimes, there are more dates as well.
For example, if a check is written, the record in the accounting system will have two dates. The transaction date will be the date on the check. The date posted will be the day that someone in the accounting department actually entered the transaction into the system. (Some financial systems may have other dates as well, such as an audit date or an edited date that applies to comments.)
Don’t let these dates confuse you. If you are designing a financial data mart, you do not need to choose between them; both dates will be useful, and both should appear in your model. A fact table that logs general ledger activity, for example, will have one row per transaction. That row can contain two references to a day dimension; one represents the transaction date and one represents the posting date. Name the keys appropriately. For example, you can call them day_key_transaction, day_key_posted.
The transaction date will be the one that is used to drive most reporting. This is the date that properly associates the transaction with an accounting period. You would use it to summarize transactions by day, month or fiscal period, for example.
The date posted will be of lesser use, but may also occasionally serve analytic purposes--especially when compared to the transaction date. For example, auditors may want to look for transactions posted one or more months after they actually took place. Managers might want to improve their efficiency by studying lags between transaction date and date posted.
If you are also going to build a monthly snapshot by account, then the star for account transactions may serve as its source. In that case, the date posted will help the ETL developers maintaining the snapshot. During each processing window, they can simply look for any transactions that were posted prior to the last time the snapshot was updated, and process only those rows.
- Chris
