Data warehouse / data retention strategy - ERP upgrade and consolidation

Planning to implement a global ERP and decommission 3 legacy ERP systems. The ERP systems have Oracle, Pick and SQL Server databases respectively. The systems contain at least 15 years of operational data. We intend to migrate all master data and opening transactions to the new ERP. So we need to find a solution to retain historical data.

The business requires that all data must be retained. At least 5 years of data shall be available for tactical and management reporting via the reporting capability in the new ERP (Dynamics AX). The remaining data shall be readily accessible if and when required for regulatory purposes.

Our data warehouse program is still in its infancy and we have only implemented data marts for 2 business processes so far. I am aware that extending the data warehouse and marts to retain historical data is a massive undertaking and perhaps may end up costing a lot more than the ERP implementation. Or is it better to just move the all old data tables into a database to retain data? However if we do this we may not be able to easily provide this data to the Dynamics AX ERP for tactical and management reporting.

I am sure other companies have been in this situation. Please can you share your thoughts on how to satisfy business requirements in this case?

I'd ask the new ERP vendor how to import legacy data. Whether you import 5 years or 15 years, you still have to do the work to import some data.

BoxesAndLines wrote:I'd ask the new ERP vendor how to import legacy data.  Whether you import 5 years or 15 years, you still have to do the work to import some data.

ERP vendor recommends importing only master data and open transactions. Importing legacy data into ERP is not an option.

Your problem statement seems to imply the ERP vendor is providing a data warehouse platform as well. If that is the case, as B&L suggested you would bring history into that platform. You would not bring history into the ERP system itself. Any decent analytic platform should support this. If the vendor has their analytics system munged in with the ERP system, be very wary.

ngalemmo wrote:Your problem statement seems to imply the ERP vendor is providing a data warehouse platform as well.  If that is the case, as B&L suggested you would bring history into that platform.  You would not bring history into the ERP system itself.  Any decent analytic platform should support this.  If the vendor has their analytics system munged in with the ERP system, be very wary.

The vendor is not providing a data warehouse platform. Sorry for not making it clear. The business expects that they can utilise the reporting capability in new ERP system to view operational and tactical information. It is expected that ERP reporting system will source latest operational data from the internal data source and history will be sourced from an external source i.e. data warehouse or a copy of legacy database.