UDC is implementing an Extract Transform Load (ETL) solution at a Midwest utility to help bridge the gap between the capabilities of their Geographic Information System (GIS) and the capabilities of the spatial asset visualization field application used by their field personnel. The solution will enable field personnel to trace upstream or downstream from their mobile field devices. This capability empowers technicians and field personnel to make informed decisions giving them the ability to operate independently in the field.
Prior to the solution, mobile crews relied on the in-house GIS team to provide them with critical information for trouble shooting issues in the field and lacked a robust mobile application and devices that could adapt and scale well to current needs. The ETL solution addresses the data needs of the utility’s new field application and provides new functionality to their field crew.
The UDC ETL solution supports the utility’s electric, gas, and landbase with data changes automatically synced nightly for timely update. One of the challenges for UDC was to replicate the dual view of the company’s existing system. Changes are pushed from the GIS including the geometric network and cartographic representations.
Key Features of the ETL Mobile Application Solution
Following are the primary process features of the UDC ETL mobile application solution and related efficiencies that will be realized by our client when effectively feeding complete and incremental GIS data to their new field application.
1. Extract Connectivity Information for Gas and Electric Networks
UDC customized the ETL solution to extract complete as well as incremental changes to the network connectivity from the GIS and make it available for the field application. The extract includes geometries of the network features. This enables the field personnel using the mobile application to run upstream and downstream traces to quickly identify and address devices at the root of an issue without additional help from in-house support teams. This capability enabled by the solution is expected to significantly improve the efficiency of the utility’s field crew.
2. Extract Alternate Geometry Representations for Electric Networks
The complete and incremental changes of the dual geometries (actual and representations) for certain feature classes were successfully extracted using scripts to create file geodatabases with the alternate geometries. These were then processed by FME workspaces to provide the actual and representational geometries to the field application. This preserved the representations defined in the GIS and provided a consistent view across applications.
3. Extract both Complete and Incremental Changes from GIS
UDC’s innovative ETL extract uses a combination of custom code and workspaces developed using Safe Software’s FME application to extract GIS data changes vs. data sets. Custom code extracts and identifies incremental changes of the network connectivity and domain information stored in custom formats in the GIS. This ensures the synching of GIS data with the field devices by consuming only the minimum bandwidth required which enables field crew in poor bandwidth areas to still be able to sync their field devices effectively.
4. Automate using FME Server
The ETL solution is designed to ease maintenance overhead by automating the process using FME Server to chain workspaces so any errors in processing are not propagated to the downstream field application. This also helps in an efficient rollback and recovery process. Self-monitoring and self-healing FME workspaces are also put in place to reconcile issues in the extract process. This further reduces the manual maintenance required.
5. Provide a Lean and Scalable Solution
The UDC ETL solution is lean and dynamic. It is designed to scale well with minimal changes required when the data model is edited in the GIS. This lightens the manual maintenance required.
UDC is an Associate Partner with Safe Software maker of FME and leader in spatial data transformation technology. Contact us to learn more about UDC’s ETL solution.