Dashboards Turn Data Into Information

After conducting a full GPS inventory of its distribution system in 2008, Union Power Cooperative implemented an advanced metering infrastructure (AMI) system in 2010. However, the utility realized it did not have a good way to sift through millions of records and tie the information together to make sense of it all.

Early Difficulties

As data started to be delivered from the AMI, the geographic information system (GIS) administrator and the engineering and operations (E&O) support manager had their own separate problems. These problems converged. One problem was data manipulation. When E&O queried the AMI system, obtaining a file that contained momentary interruptions for the day, the file had to be delivered to GIS for manipulation so the data could be displayed on a map. This was the process every time an engineer wanted to see momentary interruptions, or high and low voltage, and how they related to the distribution system.

A second problem was trying to display many of the results in a GIS map. Three different viewers, which required software to be installed and the data to be kept up to date on each machine, were being juggled. E&O was trying to display the AMI data in SharePoint, but it was not working out well. The goal was to have one easy-to-use application to configure and tie all this information together.

First Steps

In 2011, GIS and E&O were reorganized into the same group, and there was some experimenting with Esri's ArcGIS Viewer for Flex. The group was impressed by the speed of the application and its ease of use. Brainstorming ensued about the possible functionality that could be implemented.

However, the problem with the data manipulation still existed. But a tool from Geospatial Extensions allowed automation of the process allowing insertion of the data into the GIS database without having to do any programming. Now when the engineers get to work, they have access to the momentary interruptions that occurred in the past 24 hours. This solution freed all involved substantially, thus saving time and money.

Six-Step Process

The process of converting momentary interruptions from a tabular format to be represented in a geospatial view involved several steps:

Brainstorm an idea
Identify what tables and fields were needed
Create SQL views
Configure the Geospatial Extensions tool
Publish the Esri map service
Configure the dashboard configuration file.

The idea was to have any meter that had a momentary interruption on the system in the past 24 hours displayed on the dashboard. Union Power discovered a job could be scheduled to run every day and the data stored in a database table.

Once the data was in the SQL Server, queries were written to calculate the delta of the momentary interruption count. The meter number was then joined with the consumer layer from GIS so the query contained the latitude and longitude.

The next step was to configure the Geospatial Extensions tool to insert the data into an Esri database. The tool could be configured to run on any schedule. When it is run, the tool compares the records in the view to the records in the GIS layer and either inserts or deletes records.

Once the data was in a GIS format, the file could then be placed in a map document and configured. Once a document is created, it can be published as an Esri map service. This allows other Esri technology to consume the map service. When a map service is created, it produces a representational state transfer (REST) endpoint.

The last step was to configure the Esri dashboard template, which consists of copying and pasting the REST endpoint that was created from the map service. This tells the dashboard to display a particular map service when a layer is turned on. Since the configuration file is in HTML, Union Power found it easy to set up and maintain.

This same process was used for every feature in the dashboard.

More Additions

After adding the electric distribution system to the dashboard, the next item of work was adding failed reads, work orders, and high and low voltage to the dashboard. The engineers started checking the dashboard every morning and used this information to make better-informed decisions about the system. During the first week of having the high and low voltage on the dashboard, the system engineer submitted work orders to have several overhead transformers changed out as damage was indicated. Union Power was able to address several power-quality issues prior to any member complaints.

The dashboard spread companywide, with every department using it. Requests started coming in from users for more and more features. When the non-GIS people start submitting ideas for expanded features making use of GIS information, it was more than a hint the dashboard was something special.

OMS Information

One of the next items added to the dashboard was live outages and who was predicted out of power. The dashboard pulls this information from the outage management system and displays it along with other information about the outage. Anyone in the utility can see the outage location and who is affected, all in one easy application.

Since outages were already in the dashboard, SQL queries were used to pull in the historical outages for the last 24 hours, seven days, 30 days, month to date and year to date. Now when someone inquires about historical outages, that information can be easily and accurately provided.

Displaying possible meter tampering on the dashboard has been a huge hit. This feature existed in some vendor software but had to be manually run each day. A method to pull the information from the AMI system each day was devised, and it is compared to the customer information system (CIS). Then someone is notified by e-mail along with the dashboard displaying data. In the first week of release, approximately US$3,000 was recovered using the meter-tampering tool. It is an understatement to say the meter-tampering tool has been successful and beneficial.

Some other features that have been tied into the dashboard are right-of-way maintenance, automatic vehicle location, non-pay cutoffs, key accounts, and the ability to add and delete notes.

Further Outage Information

Up to this point, Union Power has spent little on the dashboard. The GIS technology was already in place, and the only investment was on the Geospatial Extensions tool, which was around the cost of a new desktop. Staff who prepared the dashboards were not really programmers, so a lot of time was spent getting familiar with the configuration files of the dashboard and SQL queries. It was just a couple of co-op guys who, through a little hard work and determination, produced something unique that enables Union Power to make dashboard changes at practically no cost.

Stepping back and evaluating the current state of the dashboard has provided some other ideas that would have required programming expertise. Union Power worked with Esri professional services to add some customized features to the dashboard.

One added feature was the ability to see outage information at a higher level and sorted in several different manners, including number of members out of power, outages by county, outages by district and number of key accounts out of power by category.

Having this information available to all staff allows them to access this information in real time instead of having to go into the dispatch center where tensions are high during large outages.

A second feature added was the ability for engineers to see the current reliability index in real time. The engineers can keep track of the current values of the system average interruption duration index (SAIDI), system average interruption frequency index (SAIFI) and customer average interruption duration index (CAIDI) compared to the annual goals for each index. Each index is configured to display by month, year to date, the past 30 days and the past seven days.

The third feature added was the ability to graph the momentary interruption and voltage data for the past 10 days. When a user clicks a point, the pop-up window has a button available to display the graph. The momentary interruption data was configured to show the data in a column graph, and the voltage data was configured to display the results in a line graph. This provides a quick look at the historical data for that particular meter without having to access a different system.

Next Steps

The dashboard is not only used inside the office, but it also can be accessed from home with a user name and password. Union Power has even taken the dashboard and put it in a mobile environment. Currently, seven users have the dashboard in their vehicles through a wireless connection on a laptop. The results have been positive.

Realizing the value of the dashboards has led to considering their use on mobile devices such as iPads and Android tablets. Linemen are currently testing these devices and a decision will be made on which solution will best suit the needs of Union Power Cooperative.

Todd Harrington ([email protected]) is the GIS administrator at Union Power Cooperative, which he joined in 2007. He has more than 11 years of experience in GIS and databases, ranging from law enforcement to real estate. He graduated from the University of North Carolina at Charlotte with a bachelor's degree in geography.

David Gross ([email protected]) has worked with electric cooperatives either directly or through consulting since 1994 and has been with Union Power since 2008. He holds a BSEE degree from North Carolina State University and is a professional engineer in North Carolina.