The Problem
Archer collects data from its clearinghouses and stores the data in a local database which was maintained onsite. This database would then be used to populate Excel files which ran complex macros and calculations. The process took over 4 hours and consistently crashed and kept staff in the office well past 9:00 pm.
The Goals
1.
Reduce the runtime by 2 hours
2.
Increase reliability
3.
Use a more secure method of backup and maintenance for their onsite server
4.
Increase the functionality of the reports
5.
Maintain the connection between the Excel files and the database
The Solution
We automated the process and reduced the run time from 4 hours to under 5 minutes. We also added functionality to:
- Keep a complete history of all data (better compliance and reporting)
- Audit connections and changes to the database (better governance)
- Create a web portal where they could log in to monitor the processes and turn on or off access to the data and define the level of access (better governance)
We updated the Excel files so they could pull data directly from the database, which allowed staff to work from home instead of the office. Our system averaged about one issue per year versus once per week with the original system. Lastly, we made the reporting updates that could not be performed by Excel and then allowed the data to be exported into Excel for use.