Thom
You brought up interesting points I hadn't thought about.
I don't see there will be a time when aggregating all the data is needed. Each client is treated separately. And there's no replication of data between us and the clients. I haven't seen the output that goes to the client yet, but from what I've seen so far it's text files and Excel.

I do agree archiving the data is questionable and was an after thought. The thought behind this is that the end of the contract, maybe 6mo or so, move the data to archive to hold in case they decide to come back. At the end of 2 years, it all gets dumped. The data is stale and can be rebuilt with fresh data. Technically, the data could be dumped once the contract is over. But my company did have clients return after a year or so.

Client retention is a problem. Once their system is tweaked, we're no longer needed. The way we keep the clients is by doing all the custom reporting. I don't see the clients remaining beyond 5 years. That is an interesting point what is the current client retention. As for growth, would like at least 5 new clients per month. The current process allows 2-3 per year. The limiting factor is the PM who works directly with the clients. They can do only so much in a day.

As for the DDL, as little as possible. There will be adjustments because of different ERPs the client uses and adjusting our own processes to make improvements. So far we're working with extracts from 5 different ERPs and they flow into a common table structure. When a new one comes along, adjustments have to be made.