I work for a mid market manufacturing company with around 1700 internal employees and about 7000 external users (employees of our customers). We grew through acquisition which means we have many separate systems that serve that same function for different parts of our business (two ERPs, 3 CRMs, 3 e-commerce applications, dozens of BPM applications, dozens of various mobile apps, dozens of IoT devices, etc).
I am becoming increasingly worried about our data. The product we manufacture has a 30 year product lifecycle and our customers expect us to have data from 1980 accessible at all times. We probably have 50 million orders sitting in maybe 8 different systems on 8 different platforms. We have credit data sitting in maybe 5 different systems on 5 different platforms, etc. The orders/credits/etc all filter through to our ancient AS400 ERP but only the financial information, the rest is sitting in the system that gathered it. Equally as concerning is that we have around 10 million technical design files scattered on various network drives with no type of heirarchy or query capabilities.
We have a data architect that is building a data warehouse for reporting but he is not keen on using that data warehouse as a "legacy lookup" that can be accessible to front line employees.
I'm wondering if anyone an share some strategies for managing this data. My main questions are.
- What are the benefits and risks of leaving the legacy data in it's current state then accregating through some type of SOA / BPEL system vs doing ETL and staging it in a central location
- How often should current data be extracted (if at all) into a centralized DB?
- Is it better to try and get this data into a uniform schema or to just keep it as it is and separate GUIs based on the schemas?
- Are there ant COTs solutions that can assist?
- Any tips / tricks for interfacing with a variety of different databases (DB2, SQL Server, Oracle 11g/12c, MySQL etc)?
- Any networking tips/tricks to prevent the ETL processes from destroying our Network?
- Should the storage and retrieval system of textual data be a different solution than file data?
I'm really just trying to figure out where to start here.