Utility sector: Enhancing data integration processes

Cleared a six-month backlog in 1 month

The background

Consortech recently partnered with a public utility client to improve their data integration process.

This client managed a complex portfolio of 250 datasets from various external partners, each providing regular updates in diverse formats and structures. Previously, this required the creation of custom scripts for each dataset, an approach that was time-intensive and posed significant maintenance challenges due to the frequent changes in incoming data structures.

Consortech was called in to develop a single, generic automation solution that could significantly reduce the number of FME and Python scripts currently in use. This project aimed to enhance the efficiency of the data integration process, reduce backlog, and optimize integration. It also sought to create a system adaptable to various data formats with minimal manual intervention, ensuring high productivity in data management.

The challenges

The client’s data ecosystem presented several challenges due to the diverse formats of their data sources, which varied significantly in structure. This necessitated a flexible solution capable of automatically adapting to changing formats without the need for additional custom scripts.

Complicating matters further, external partners frequently updated their data formats, a situation that had previously required the client to rewrite scripts, significantly slowing down their workflow.

The solution delivered

Consortech went to work building a generic configurable validation, conversion, and loading script capable of handling multiple data structures and formats. This solution replaced over 80% of the existing FME scripts and was configured to automate the conversion of nearly any dataset to align with the client’s ArcGIS database requirements.

We also developed an exception-handling feature to enhance the system’s flexibility. This enables the integration of specific processing scripts as needed, giving the system the ability to adapt to unique situations without overwhelming the core script.

Gains

The implementation of Consortech’s automated solution yielded amazing results for the client. Within just one month of deployment, they were able to clear a six-month backlog, showcasing a significant improvement in workflow efficiency. 

Processing times experienced a remarkable shift as well; tasks that once took several days could now be completed in just 2 to 3 hours, even for the largest datasets. This drastic reduction not only accelerated data availability but also contributed to smoother overall operations. 

Additionally, the solution centralized the management of complex conditions within the source Excel file, eliminating the need for frequent script updates. For datasets that needed further customization, a base script was provided, allowing for targeted modifications.