Project - ERP - Data Cleansing and Analysis

As the company grew to use the ERP system more efficiently it became necessary to review legacy data, as data anomalies were skewing reports and profitability results.

I built custom reports and used the SQL Management Console (directly connecting me to the ERP database) to identify old jobs, improperly coded transactions, dates, etc.. Where possible I used the ERP interface for correction (reversing any entries made), and the SQL Management Console where it was not. After all corrections were made, scripts were created to help ensure the validity of data being entered into the ERP.  As well, one of my team members made a mistake when creating an ERP test environment, which caused some of the journal entries to made in the test environment rather than the production environment. I worked with our ERP provider to reverse out and re-code the erroneous entries, ensuring that they did not impact our financial reports.

Once the data had been cleansed I worked towards implementing an Analytics Module in the ERP. Analytics is an application that builds a data cube from the ERP database and then enables you to use analyze the data in either the Analytics module or Excel using preprogrammed templates that are based on Key Performance Indicators. As this was a new product, the ERP vendor was relying on a provided set of analytic functions available in Excel - but had provided some financial tools, with promise to expand their analytical offering over a one year period. It also served as a better way of archiving older data, as the ERP provider suggested you only keep two years of 'live' data in the database (archiving the rest). The ERP database held well over 10 years of 'live' data, becoming so large that it was not possible to import data directly into MS Access. As well the month end procedure was taking hours to complete, as the reports were 'blowing up' due to the amount of data that needed to be processed.

I worked with a consultant to install the module, made the cube, and proved that it worked, but remained unable to change the opinion of Executive Management on the deletion of the data in the ERP.  So unfortunately this system was not brought fully live prior to my leave, and the month end close continued to take hours to process.