For this project we were faced with four large data sources (extracts from operational systems). Each of the data sources had to have complex business logic ran against them before automation was possible.
Due to the complexity of the business logic, and sensitivity of the report output, we had to embed two sets of comprehensive checks:
- 274 automated pre execution data checks were built into the process to ensure the report would not run until all the required data was present, complete, and in the right format.
- 204 automated post execution data checks were added to ensure report output fidelity. As the team adopted a ‘maker-checker’ approval process for the report, these checks focused the checker’s attention to the key aspects of the report.
The automation project was a success. We we keen to help the team understand the automation process, and so documented each of the 9 steps of the automation journey:
Data Input – We identified all of the raw, unedited data sources required from the operational systems.
Confirm Logic – Working closely with the operations team, we identified and agreed upon the definition and meaning of every piece of business logic.
Documentation – We then documented this logic as not only a reference guide for the technical build, but also as a governance point so if any logic needed changing we could easily identify and access the relevant section.
Tool Build – We began the process of building the tool using microsoft excel (as no tech spend was available at this stage to move the build onto a server).
Testing – Once an iteration of the build was complete, we conducted two levels of testing. Level 1 was from a technical user, Level 2 was from a member of the ops team. This ensured the tool operated in a way the user was comfortable with.
Controls – Once the tool was functional, we went through a lengthy process of adding controls at each step of the process. This was key as the operations team members would not be expected to understand how the report worked. From their perspective it had to be ‘click and go’. This level of automation is only possible if controls are embedded at each step of the process.
Contingency Procedures – Often when teams automate processes, no thought is given to the possibility that the automated tool fails. There are numerous reasons for a possible fail, so a workaround or contingency, is vital. In this case, we built a separate tool that had a lower level of user experience, but also a corresponding lower level of complexity meaning if the main tool failed, the back up tool could take over.
Process Documentation – We documented the new process front to back to ensure effective process handover to the operations team. The contingency process was documented in the same way.
Transition to BAU – We handed over the process to the operations team.
We transformed a process that previously took 16+ hours into one that took less than 25 minutes to run, whilst increasing data quality, accuracy, and process fidelity.
- Process heavily reliant on user?
- Simple BAU process?
- Scalable BAU process?
- Embedded data validation?
- Embedded reconciliation checks?
- Business logic applied programmatically?
- Consistent data output?
- Previous Process
- Redesigned EDI Process