A geographic information system (GIS) is designed for tracking utility location-based spatial information, while an enterprise asset management system (EAM) is used for tracking equipment information and inventory. Maximo, from MRO Software, is the EAM used at Chelan County PUD (Wenatchee, Washington, U.S.), functioning as a work management system (WMS). The EAM contains a detailed hierarchy of equipment locations that often mirrors the GIS but in tabular form. Therefore, to properly track all line equipment, the EAM needs GIS-based location information.

At Chelan PUD, the EAM tracks all detailed equipment information, including specifications for distribution transformers and line apparatus. For GIS users to fully track equipment, they need access to the equipment specifications in the EAM. To avoid dual entry of large volumes of information in both systems, Chelan PUD developed a method for the systems to exchange the information. The following discussion provides the technical details of how Chelan PUD integrated an ESRI-based GIS with its Maximo EAM.

The biggest benefit of integrating the systems was in the work management arena. All work orders developed in the EAM systems were tagged with a GIS location, typically a map page and a location ID. This made it possible to better plan and schedule work by geographic area. It also allowed the district to produce maps for failure analysis and required maintenance work.

Implementing the GIS

Chelan PUD initially implemented GIS in 1995. This GIS was an integration of a flat file graphics/GIS and an Oracle relational database (TELLUS GIS). The vendor of this integration stopped enhancements and support in 2001, thus forcing the need for a new GIS. The district upgraded the GIS in 2003, migrating the data to a geodatabase.

The EAM was first implemented in the generation division in 1998 and subsequently implemented in the T&D division in 2001. In 2001, the old GIS and EAM were integrated, but it was only one way, that being with GIS locations passing to the EAM hierarchy.

Since 2001, significant equipment tracking has been implemented in EAM, as a mainframe transformer tracking database and PC-based tracking databases were moved to the EAM's combined equipment/inventory tracking module.

The upgrade of the GIS in 2003 also required the district to upgrade the integration of the GIS and EAM, because the old integration routines would not work with the new GIS. The district recognized that the new integration should be two-way, with GIS locations continuing to pass to the EAM hierarchy, but now equipment information should be passed back to the GIS. The two-way integration avoids redundant manual entry of equipment information in both EAM and GIS, and inherent problems with duplicate data entries that don't match. The attributes are synchronized between the geodatabase and the EAM database.

Interface Details

Integration was accomplished exclusively with Oracle database triggers and stored procedures. The EAM vendor provides an Oracle-based locations API, which is used to write locations data to the EAM database. The GIS vendor provides Oracle procedures and views to manage the updates of data in versions.

Location objects originate on the GIS side, so the decision was made to make it the controlling system. Location data must be sent from the GIS to EAM to start the cycle.

Database triggers are used in both the GIS and EAM to capture desired transactions that need to be sent to the other system. Selection of desired transactions is done via control information in the database, which the integration interface administrator can change as requirements change. Transactions are stored in each database for later batch processing. It was determined that integration did not have to be real-time — nightly Oracle processes are run automatically to synchronize the databases.

The GIS has versioning, which allows more than one user to make changes to the same object in the database at the same time. Others do not see changes until they are posted to the base GIS version. The district uses three levels of versions: the default or base version that everyone can see, a “working” version where all editors post or save their final changes, and each editor's version. As editors work, they save changes into their version. At the end of the day, they post their changes to the “working” version, which may require them to reconcile differences with other editor versions that have been posted. After all editors have reconciled their postings, the lead editor reviews the additions, and changes and posts them to the base version.

The GIS-to-EAM batch processing is driven by the location transactions recorded. Additional information is obtained from the GIS database for each inserted or updated transaction and then entered into the EAM database via the locations data API table. Either new location records are created, existing location records updated or old location records marked as decommissioned.

To cause equipment data to be sent back to the GIS, an EAM user adds, moves, removes or updates equipment data at locations. These actions will create equipment transactions, which are flagged as inserts, updates or deletes.

The EAM-to-GIS batch processing is driven by the equipment transactions recorded. Equipment specifications, operations and installation data is acquired from the EAM equipment tables for each insert and update transaction. The processing creates a GIS version to hold the changes for equipment updates. Specification data maintenance is handled outside of a GIS version. The GIS lead editor must reconcile and post this version to the base version before new or updated equipment data can be seen in the GIS.

Integration Benefits

The immediate advantages of integration include working with a single asset management database and common infrastructure to locate fieldwork and facilities.

In addition, the common infrastructure supports the development of future applications. The following extended applications are planned:

  • Web-based information portal — a geospatial portal to system information; user-friendly map-based access to GIS, EAM and WMS information.

  • Mobile application — port of integrated data to a mobile map-based application.

  • Work management in GIS — creating and querying on work orders from GIS.

As always, interfaces seem to be more challenging than anticipated. Learning the new ESRI geodatabase infrastructure and resolving live data issues proved to be the two most challenging areas.

Ray Hahne is the asset management systems project manager at Chelan County Public Utility District #1, which he joined in 1988. He is responsible for the project management and technical integration of the PUD's asset management systems. Currently he is involved with integrating the supply chain function between the Maximo EAM and PeopleSoft Financials. He has a BS degree in resource management from Syracuse University and a MBA from the University of Washington in Seattle.

ray@chelanpud.org

Roger Fletcher is a senior GIS and database consultant with The Brodie Group Inc. He is responsible for assisting clients in implementing GIS and developing custom conversion and system interfaces using Visual Basic, ESRI ArcObjects, and Oracle SQL and PL/SQL. His holds BSEE and MSEE degrees from the University of Idaho.

rfletcher@brodiegroup.com

Technical Details

GIS: ESRI ArcGIS 8, ArcSDE 8, ArcFM 8 from Miner & Miner

EAM: Maximo version 4.1.1 from MRO Software

Other: Oracle 8.1.7 database, Citrix Servers