Re: Archive and purge

Hi Louis,Â

On Sat, Dec 29, 2012 at 7:19 PM, <..hidden..> wrote:

I have accounting data that I ported from quickbooks into SQL Ledger then
into LedgerSMB 1.2 then into LedgerSMB 1.3 going back to 1997.

I'm choking on all the old data and would like to archive it and purge it,
or failing that, start a new set of books and transfer over just that
which makes sense (inventory, starting balances, etc.)

Has anyone done something like this? And can you share what you learned?Â

Actually, I haven't, but I was just thinking about this issue today. Legal requirement where I come from is that you're able to go back for 7 years on accounting data. On other types of information there are different retention periods. Most larger organizations elect to purge individual accounting transactions after 7 years, but what I've seen is that some of them elect to retain period or year-ending balances much longer. This is "anonimized" data with very little "volume" or "weight" so that it's not hard to carry around. If you're not interested in these balances from long ago, yea, one should be able to remove even the compressed data like that.

While I don't really know how to do it, I envision that a procedure to do so would involve creating a beginning balance for 2005 (if that's the first year you're interested in) which corresponds with the ending balance from 2004. From there on, you can use all the transactions you already had in place. Any customers linked to the old data - which aren't used in the retained data - can be deleted from the database. Same applies to vendors.

If you don't mind me asking: what's the problem with the old volumes? The fact that there's too much storage taken up by the data? Or does your server get slower due to the amount of data it has to sift through?

In case of the latter, maybe we can alleviate the problem until the older data has been archived? The thing is that if you don't use period closes, then I can imagine the database having become too heavy. However, if you close your books (enter a closed period) as of some rather recent date (e.g. 2011-12-31) then the server can create a "snapshot" to optimize queries for balances after that date.

Bye,

Erik.