Hi,

After archiving the data into an archive company, would it make sense to just dump the archive company to a text file and remove the data?

The reason why I'm thinking of this is because the data in the archive company still contributes to the size of my database and I'm planning that this way I could shrink it.

In my experience we dont normally go back to archive data and I can always look up the text files if I need to.

Would appreciate any comments or suggestions. TIA.

Hi Benito,

that's a question that you should discuss with your auditors and whoever is responsible for data retention/ record keeping in your company. I'm sure you have a data retention policy. I'd ask around in your company. The usual requirement for archived data in the US is that IT has to be able to produce requested information in a reasonable amount of time. You might need to use Baan sessions for that, since it's difficult to sort data in sequential text files. The question then would be, how fast you could restore the company from dump if you would have to and if that time would be considered "reasonable".

Some companies use data warehouse solutions. You might want to think about going that way. Such companies extract any critical data and put it into a data warehouse. Consequently they don't care too much about the data in their archive companies. I think that's a good strategy since it makes them independent from the ERP system du jour. If they cut over to another system one day they don't have to worry about access to their historic data. (In some countries it's a legal requirement though, to keep data in the original tables for a certain number of years).

Anyhow, you should check what the data retention policy is in your company before dumping the data to text files.

Regards,
Karin

Thank you for your response, Karin. I am familiar with the Data Transformation Service in SQL which is what MS-SQL uses for Data Warehousing.
However I'm leaning more into a database copy with only the archive company present. I guess I can shrink it pretty good to achieve a reasonable size. This way I can use SQL Query Analyzer to query the data.
Any other comments from other Baanboard members? Thanks.

I think by shrinking the database size , u wish to enhance the system performance in general. But this should not be the only objective behind archieving.

Dumping archieve company to text file or copying to separate instance is not a good choice because of inflexibility in accessing past data and comparing the same with current.

While copying to separate to a separate database is good (better than dumping to text file) from SQL query analyzer point of view , u may not be able to reclaim the database space at OS level.

In general following considerations are kept in view when deciding archieving.

1. Management policy
2. Legal requirements
3. Ease , speed, accuracy and flexibility of data retrieval when required. For example u may have sales data of last 1 year in system . Your key users may need half yearly sales summary for last 3 years . In such a case u should be able to present this information asap with least manual actions (advisable is no manual action). Ideal situation is that user does not come to IT and he/she gets the results from the front end itself in one go.

I think it's a good challenge. MS SQL DTS can play significant role in this by converting the bulky transaction data to small summary information and then enabling u to purge bulky transaction data. Involvement of senior key users is highly recommended in this to identify all KRAs (Key results area) related to company's business.

Even we have developed programs which help us achieve this DTS objective using Baan 4GL tools.