Adopt an Archive Policy

Archiving Data Mart tables can improve index rebuilding and query performance simply by reducing the number of rows to be navigated. A logical policy is one that preserves each data frequency (daily and monthly) for the time span that reporting may need it. Many clients preserve month end data indefinitely, but keep daily data for a few months or a year.

Remember that all as-of reporting against Data Mart requires that data be kept for all possible prior dates you might want to report. If you need data for time periods already purged, the lost rows can be rebuilt, but there will likely be restatements of numbers previously reported.

You should purge the Data Mart execution tables and leave them with no more than 1 months’ worth of data. Your specific needs may be different. Not purging can result in system performance issues.