FME Beyond Spatial: Automated Enterprise Data Integration at Grant PUD

Grant PUD (Public Utilities District) is a community-owned and governed organization that offers both electric and wholesale broadband services to 40,000 customers in Grant County, WA. It operates 2 dams: the Wanapum Dam and Priest Rapids Dam, producing over 2,000 megawatts of electricity. As a not-for-profit organization striving to keep costs down for the residents of Grant County, efficiency is key.

Gerald Dotson, Senior Data Architect, had been using FME to support a GIS replacement project when it occurred to him that FME had potential in areas beyond spatial analysis.

It began with a “simple” request …

In 2013, the Finance department was implementing Cogsdale’s Work and Asset Management Solution into their Enterprise Resource Planning (ERP) solution: Microsoft Dynamics GP on SQL Server. At the same time, the Engineering department was installing AutoCAD Map 3D on Oracle Spatial as their new GIS. The engineering department makes the materials requests for utilities infrastructure projects, like pole and transformer installation, while the finance department manages accounting and inventory. They needed to establish an API between the two systems that would pass design materials lists to the finance system for billing and accounting. The integration is essential for work order estimates, managing inventory and proper accounting.

The consultants insisted the interface would be no problem. Yet weeks turned into months and still no API. They assumed the transactions between the two systems would be strictly a one-for-one exchange, but it was much more complex than that (as most things are).

One day at a meeting they expressed that the integration would require more time and more money – not something a not-for-profit organization is fond of hearing. It is here where Gerald Dotson proposed, with confidence, that they use FME to create the integration. They gave him two weeks to provide a proof-of-concept.

The reason for Gerald’s confidence? He knew FME already had the code that the consultants were spending hours writing bundled up into handy little tools called transformers. Not to mention support for hundreds of formats including those in question: SQL Server and Oracle Spatial. At the end of the two weeks he demonstrated a proof-of-concept with an FME workspace that worked brilliantly and he was given the green light to build the entire interface with FME.

The result was a linked series of three workspaces that processed materials lists or “Assembly Units” submitted via the GIS using tools that handled searches, substitutions, calculations, validations and final submissions to Dynamics GP tables. Deploying the workspace via FME Server enabled Engineers to login and run the integration at any time, and created easy-to-use dynamic HTML pages for data submission.

And thus began the age of FME-driven interfaces …

This entire scenario had Grant PUD thinking about how many of their interfaces were hand-coded and what a challenge it was going to be maintaining them. In some instances by the time they decided to replace legacy code, either source code had been misplaced or finding someone at Grant PUD still fluent in the outdated language was difficult at best. This would require more consultants and more money. FME provided a flexible, long-term alternative to code that could do away with this problem and potentially save them thousands of dollars.

Flash forward two years and they were upgrading their Customer Information System (CIS), to Oracle Utilities Customer Care and Billing. The CIS is the backbone of their customer-facing operations handling every aspect of the customer lifecycle from start of service, to payment processing, to bill invoice generation. It requires interfaces not only to Engineering’s GIS and Finance’s Dynamics GP system, but also to third party vendor applications.

From the beginning of the new CIS implementation project, FME was the tool of choice to build replacement connections for legacy hand-coded interfaces. They essentially established FME Server as an intermediary integration band around their CIS to handle transactions between it and other systems.

And it didn’t stop with integration …

FME also shaved significant amounts of time off of hourly, daily and weekly routine tasks in other areas of the organization.

Streamlined Data Validation for Accounting

Until FME, the Accounting Department spent 8 hours per week reconciling data between reports received from the bank and the CIS billing system. The reports presented the data from different perspectives that made it difficult to piece together which invoices were included in the report submitted from the bank. Using FME they created a reusable workflow which validated whether entries in the report from the bank matched what was received from the CIS. The time to accomplish this task per week was now only 30 minutes.

Automating Report Generation to meet Federal Licensing Requirements

For the river on which its two hydroelectric power plants, Grant PUD is required to maintain elements like river elevation, temperatures, flow rates and dissolved gases within certain ranges and provide hourly, daily and monthly reports. This used to be a very manual process using a series of spreadsheets to calculate averages and highest values, plus a validation step to catch river probes that had fallen out of calibration.  While not overly complex, this task required an hour each day. Using FME they created workflows that generated these reports in the exact format required by the agency. Deployed using FME Server, the workflows now run automatically at scheduled times. Once a daily task, attention from staff is now only required when the validation step flags a problem with the data.

Preventing Costly Downtime with Batch Process Monitoring

The CIS handled the vital process of batching data collected during business hours overnight. However there was no mechanism that notified them if the process failed resulting in a stall of operations the next business day until the issue was resolved and batching complete. They configured an FME workspace that ran SQL queries against the Oracle database to determine how much time had elapsed against critical segments of the batching process. Excessive run times were flagged and notifications immediately sent. Since implementing FME to monitor the batching process, all issues have been caught and corrected before business hours saving them countless hours of costly downtime.

Grant PUD’s story highlights the benefits of using FME’s capabilities beyond the realm of spatial. By replacing legacy interfaces and manual tasks with FME workflows and automation, Gerald Dotson and Grant PUD have realized significant cost savings in the form of consulting fees, workers’ time, and avoided downtime.