Over the last eight years we have repeatedly run into a common request at electric utilities: improving transformer lifecycle management and how it could relate to GIS. Transformers are different than many other utility assets for a few reasons. First off, transformers are expensive assets (especially the large pad-mount transformers) and they will possibly be installed in multiple locations over the course of their lifecycle. This fact drives a variation in accounting. Most assets (poles, protective devices, conductor, etc) are installed once and are capitalized at the time they are installed. By capitalized, I mean the cost of the asset is added to the financial baseline of the utility and the asset begins to depreciate.
But because transformers can be installed more than once, the issue is complicated. We don’t want to capitalize the same transformer asset more than once. We work with some utilities that capitalize the transformers at the time they are purchased and received into stock (before they are installed). Others capitalize the transformer when it is installed the first time (but don’t capitalize them a second time for additional installations).
Another difference is that transformers contain significant quantities of oil. And because oil is a contaminant, there is plenty of regulation around these assets. Oil testing must often occur on transformers to ensure that their PCB (polychlorinated biphenyl) levels are acceptable. The PCBs are used as coolants and insulating fluids in the transformers but it is known as a pollutant.
There are various types of tests that focus on the parts per million (PPM) of the PCBs within the transformer. These tests must be tracked for the individual assets and if they fail the tests the PCB problem must either be remedied or the transformer must be disposed. And as you might guess, there is plenty of regulation around the disposal/condemnation of the transformers as well. We’re focusing on transformers in this article but a couple of other utility assets are often subject to similar regulation, including voltage regulators and capacitors.
"OK, so what does all this have to do with GIS?" you ask. Potentially a fair bit. We’ve seen utilities track all of this asset data, installation history, oil testing, and condemnation data in all sorts of formats from Access databases to old mainframes to Excel spreadsheets. Historically it has not had much to do with the GIS, though these same transformer assets have been drawn on the maps because they are part of the electric distribution system.
As GIS evolved into more of a graphical asset management system, utilities began modeling more than the locations of the transformers in the field. They began to also model a simple representation of the actual transformer asset records that correspond to the same assets that are tracked in the Access databases, mainframes, and spreadsheets.
The easy example of this is a 3 phase overhead transformer bank. This is often modeled in GIS as a single point on the map representing the location of the transformer on a pole. However, in the field there are literally three transformer cans installed on the pole as shown in the picture to the left. Each one of these assets has to be tracked individually and specifically throughout its lifecycle.
To add these asset details to the GIS, a transformer unit object table (not shown on the map) is typically added to the geodatabase with a relationship to the transformer bank feature which IS shown on the map. The data model looks similar to this where a single Transformer Bank on the map can have one or more related transformer units. This is probably how most utilities are modeling their transformers in GIS and it is a great start.
The transformer unit contains the basic asset nameplate and installation information including the manufacturer, rated KVA, installation date, phase, etc. But it doesn’t go to the level of detail of tracking the multiple installations of the transformer over its lifecycle, the oil tests, the condemnation, and any other detailed lifecycle data. That data is all still maintained in those other systems. This then became the challenge we were presented with:
How can we integrate transformer lifecycle data into GIS, reduce duplicate data entry, increase data integrity, and make the data as useful to all of the consumers within the utility?
I’m sure this could be answered in many different ways. We’ve implemented three answers to this question at different US-based utilities and have heard the need at many others.
The first component we had to come up with was a data model that could contain all of the needed lifecycle information while still meeting the visualization and analysis needs of the GIS. We started with the above standard model of a transformer bank and unit but separated the relationship between them. The transformer unit table would now only contain manufacturer, nameplate, and warranty data. All information that could be entered at the time the transformer asset was purchased by the utility. Because the purchase will occur before the asset is installed, there is no tie to the installation data for the transformer.
We then created a series of tables surrounding the transformer unit to capture one or more lifecycle events. Each of these lifecycle events will have an event date along with various other attribution that is specific to the event.
One of the events is the installation of the transformer. That event will capture the installation date, the phase the transformer was installed on, location details of the installation (address, pole/pad number, etc), work order number, etc. This installation event record can then be correctly related to the transformer bank GIS feature on the map.
By modeling the data in this manner, we still have all of the key asset information available in the GIS via a relationship to the transformer bank. But we now have wealth of other lifecycle information available as well. And because we are modeling individual tables for the events, we can have multiple lifecycle events for a single transformer asset.
The following picture shows an example of the model, the original transformer bank and unit are still in blue whereas the new lifecycle events are in green:
So when the transformer is first purchased, a single record is entered into the Transformer Unit table representing the manufacturer data. If a utility has multiple warehouses/storerooms/storage yards, we would recommend using a Stock table to track the location of the asset while it is not installed. As the new transformer is moved into a storeroom, a new Stock record is created with the location and date. If it is moved multiple times before being installed, each move is logged to the Stock table. Once the transformer is then installed in the field, an Install event record is created to capture the work order, install date, and installation details. This install record can then be related to the Transformer Bank feature that exists on the map.
Down the road when the transformer is removed, the relationship between the Transformer Bank and the Install record will be dissolved/broken and a new Removal record will be entered capturing the date, why the transformer was removed, and the work order it was removed on. The Install event record still exists but it is not the current (most recent) record anymore. The transformer may then be placed back into a storeroom which would create another Stock event record. Oil testing may then be performed to check those all important PCBs which would create a Testing event record. If there are no major issues, the transformer may then be installed in a new location which would create a new Install event record which would then be related to the Transformer Bank at the new location on the map. And the lifecycle continues until the transformer is eventually condemned and disposed of. You guessed it, another lifecycle Condemnation event record is created.
With all of the transformer data modeled in the GIS, we can view the consolidated information in some interesting and useful ways. This screenshot is from the Telvent ArcFM™ Attribute Editor. It shows a transformer bank that has been selected on the map. The transformer bank has been expanded to show the relationship to the transformer install event record. The install event record has then been further expanded to show the transformer unit information (the manufacturer data). And when we expand the transformer unit we can then see ALL of the detailed lifecycle data we’ve been discussing.
From this lifecycle data we can tell that this particular transformer asset was originally brought into the first storeroom on 12/14/1998. It was then transferred to another storeroom on 6/18/1999 before being installed on 8/17/1999. That installation was on phase A (there is a lot more installation data available in the attributes) and it was installed at that location until it was removed on 12/7/2004. It then moved back into a storeroom, was transferred a few more times, and perhaps tested before being reinstalled on 8/14/2006 on phase B. This install event record is the one related to transformer bank that we selected on the map. This view of the data presents so much more information than the flat transformer bank/unit model.
So this works great for the GIS folks, but what about all the other groups that interact with this data throughout the utility? These might include storeroom/yard managers, engineers, field crews, etc. And some of these users may not want to use the GIS to view, enter, or manage this transformer data (not to mention the utility may not want to buy GIS licenses for all those users). We have typically attacked this challenge by creating a standalone web application that can interact with the transformer unit and lifecycle data - basically any data that doesn’t require the map (which is most of it). The architecture for this solution uses Esri Multiversion Views to access and edit the geodatabase:
If you aren’t familiar with Multiversion views, check them out as they are super cool. We published an article last month covering the technical details on what a Multiversion view is, how they work, and how they can be used.
The one other architecture option would be to use Esri ArcGIS Server and potentially Telvent ArcFM Server™ to access the database. This approach will ensure all of your backend GIS rules and logic are enforced (domains, auto-updaters, etc) but can be cost prohibitive if you don’t already have the licensed software:
In either implementation you are then able to create a slick web application to manage your manufacturer and lifecycle data. This web app is made available on your intranet and can be accessed by all of the departments that need to access this data outside of GIS. This picture shows an example of the web app we built to manage the manufacturer an warranty data at one of our customers:
So what about all of those lifecycle events? We can manage those right in the web application as well. All of the events can be captured and interacted with outside of the map. And we can then also flatten out those events to show a really cool view of the asset lifecycle as well:
You can add new events by choosing the lifecycle event that has occurred and then entering the attribution about that event. Hold up a second though... what happened to using this data in the GIS? Well it’s available there as well. But now we have a single, consolidated source for the data but have provided two different tools to interact with the same data.
And if you enter the install event in your web app you can really streamline the GIS editing. Previously you would have created the transformer bank on the map and then created the related transformer unit records. You can still work it that way if you want.
But if the install event record has already been created via your web app, all you have to do is build the relationship in GIS. With Telvent's ArcFM™ we can use the Locate And Relate tool available as a right-click option right on the transformer bank.
This then prompts the user to enter some key data about the transformer asset. If you have the unique asset id from your as-built drawing (which you would), you just enter that in and whalah... the relationship is established and you are done.
And once that relationship has been built you can use GIS for all of the wonderful analysis it does best. We can symbolize our transformer locations on the map for all transformers that have had oil tests with PCB readings above a specified level. We can symbolize on locations with transformers that have been installed more than once. And the ideas go on from there.
In summary, we’ve shown how a utility can merge the detailed transformer lifecycle data into the GIS to remove duplicate data entry, increase data integrity, and exponentially multiply the value of the data to the larger organization. As we’ve implemented similar solutions at different utilities the project details have varied slightly but the overall theme and the end value to the utility have remained consistent. Let us know if you have questions, thoughts, or any other commentary on the concept.