Adjustment Periods and Dimensional Modelling
July 16th, 2003 by Mark Rittman
Oracle Financials (and, in all probability, all other accounting packages) has the concept of adjustment periods, where corrections to transactions already posted on the general ledger can be held in a ‘holding area’ until they are properly attributed. This has the effect of creating a financial calender where you’ve got thirteen or fourteen periods in a calender year.
So far, so good. However, these ‘virtual’ periods sometimes do not have a date, or range of dates, that roll up to them, as they are not real periods and are just for accounting purposes only. This means that, when trying to fit these virtual periods into a standard OWB/Oracle time dimension, there’s no lowest-level date_id that you can assign to them.
So what do you do? You can’t create a row in your time dimension without the date_id primary key, as it would break referential integrity. You could assign these periods a fake date_id such as ‘01/01/1900′, but then if you rolled-up all the transactions for a specified year (say, 2003) you’d miss out those currently stamped with a virtual period id.
The answer (although I stand to be corrected :-)) is to create a separate time dimension for accounting periods, that has, at its lowest level, the individual accounting periods (including our two virtual periods per year), and put both the primary key from the standard time dimension, and the primary key from our accounting dimension, within our fact table. This means that you slice and dice your fact table by joining to either your regular time dimension, or your accounting time dimension, depending on whether you want to analyze by a normal calender or the financial calender. The downside, however, is that you can’t drill down from calender year (or month, or quarter) down to individual day, and then back up to financial period (or quarter, or year), as you could do if they were just alternate hierarchies in the same dimension.
Any other thoughts on this?
January 3rd, 2004 at 7:33 pm
The way we set this up at my old place was just to assign the “Adjustment” Period to the last day of the year, and exclude it from the last non-Adjusting Period.
We had an odd calender anyway, as we were using 4/5/4 week periods rather than strict calendar months. So period 12 ran from 2nd March to 30th March, and period 13 ran from 31st March “to” 31st March.
This arguably causes issues with transactions that genuinely happened on 31st March. But for most transactions, the important thing is not which date they are posted to, but which period/year they are posted to - the date of a transaction is almost memorandum only.