Our client made use of manual Excel processes which enabled the technical team to validate and enrich customer data which was then used to build a dashboard showing the statistics for the data quality improvement over time. Most of the information was collected from external sources by the members of the technical team via email submissions. These sets of information were then validated in Excel according to set criteria and cleaned and enriched as required.

Most of these processes were performed manually and thus, the validation part was also a substantial manual exercise that was time-consuming and created key man dependencies on certain resources.

Solution Implemented:

Given the key man dependency and the time it took to create the required dashboards during each cycle, the decision was made to automate as many processes as possible within a SQL Database. As part of this automation process, certain production standards need to be created in the environment, e.g., what measures define success? These standards were defined and built into the revised dashboards to ensure monitoring of the automated processes can also be done via the dashboards.

The approach taken was to invest in a proper automation system to automate the required features of the end-user dashboards. To achieve this, all parties agreed on a defined Business Requirement specification, which was created as part of the Business Analysis (and documented as the exact requirement for each validation control). This was used as the specification for subsequent enablement during the Design phase.

The project was delivered in 3 phases, due to the complexity of the processes in the environment, it is determined that certain tasks or sub-tasks be prioritised for automation first. These provided quick wins in the environment and alleviate some of the key-man dependencies straight away. By eliminating a large percentage of the iterative cycles involved just with data validation/verification, a fair amount of resource time was saved.

Basic Validations were developed during Phase 1, most of these checks were performed programmatically (via scripts or procedures). A structured system for file storage, database and ETL components to automate the validation processes was developed.

Phases 2 and 3 were focused on data sourcing and the automation of data extraction from the various source systems, SQL Databases, Excel Files and Emails. Once available, these sources were combined with standardised reference data, which allowed for the enrichment of the SQL database and further validation and advanced reconciliation process which was not possible before the implementation of the solution.

The project was successfully developed and implemented, through this project the client’s resources were enabled to analyse the outcomes within the desired timeframes. This was not possible prior to the project as these resources (who were appointed as analysts) spent their time cleaning and preparing dashboards and not analysing the outcomes.