Decommissioning Teekay Financial System while Maintaining Data Access
The Client
The client is a multinational shipping company that requires on-demand financial reporting.
Reporting is an invaluable competitive advantage to this client as they use insight gained to make better business decisions.
The Challenge
Our client was replacing their invoice management system as part of a major ERP implementation. Their old system was to be decommissioned; however, as a publicly traded corporation they are legally required to keep all financial records for at least seven years.
The client had two options: either maintain the existing system or replace it with an “offline” solution.
Key Challenges
– The invoice management system contained over 180,000 records.
– Each invoice consists of the scanned image, associated meta data, and an audit history.
– The offline solution had to be self-sustained, easy-to-use, and require minimal maintenance.
– The solution had to be SOX compliant.
The Process
- Interview financial team to understand needs and requirements.
- Extract and map images.
- Extract and map audit history.
- Extract meta data.
- Create the offline solution.
How Optimus Helped
Optimus developed an “offline” solution so that the legacy system could be decommissioned and the client could still access their financial records as needed.
The first stage was to work with the financial team to understand their needs. Through a series of workshops we identified what data they would require from the financial system. Then we proposed an “offline” solution that would make that data easily accessibile outside of their existing system. Once the solution was approved, we began development.
In order to develop the “offline” solution, we needed to extract the required data. Since the information consisted of both images (in the form of scanned invoices), HTML files (the ‘audit history’), and meta data (the invoice details such as invoice number, date, approval, etc). These three different datasets had to be extracted differently.
Step 1: Extract the Images
The solution had a built in mechanism to export images from the database onto the server’s harddrive; however, the extracted images were placed in a complicated series of sub-folders. Additionally, the images were in a proprietary format which needed to be unpacked.
The system maintained a second set of folders which contained a mapping of where the image is located and what transaction it is associated with.
Optimus created a custom tool which scanned both of the folders (consisting of multiple sub-folders and a total of over 1 million files), unpacked the images, then mapped each image to its corresponding transaction. This process resulted in images that were accessible by users and linked to a specific transaction.
Step 2: Extract the Audit History
The audit history was an HTML file that contained a log of a transactions flow through the financial system. It contained a record of the day the invoice was scanned, coded, and approved. These audit history files were stored in the database and needed to be extracted.
Optimus worked with the client to first extract these files, then created a custom tool to read each file and map it to the corresponding transaction.
Step 3: Extract the Meta Data
The data about each transaction itself was stored in the database. Optimus created a custom query that extracted all of the required information into a single CSV which could then be loaded into an Excel document.
Step 4: Create the Offline Solution
Once all of the required information and files we gathered, it was time to package it into a simple offline solution.
We analyzed whether MS Access, SSRS, or Excel would be the most suitable solution and determined that with this dataset and user base Excel would be adequate. We then created an Excel document that contained all of the records extracted from the financial system with hyperlinks to the corresponding images and audit history files.
Conclusion
At the end of the project the users received access to a single Excel file that was linked to a database of images. By simply searching and filtering, they are able to quickly lookup any invoice scanned in the decommissioned system.
By simplifying the system, removing the required servers and software licenses, the client saved over $20,000 a year for the seven years they need access to this information.