Gas Service Information at the Desktop
Dennis J Carter GIM/SIMS Project Leader Consumers Energy 530 W Willow Lansing, MI 48920 Telephone (517) 374-2349 Fax (517)374-8419 E-mail: djcarter@cmsenergy.com David S Lucian GIM/SIMS Project Manager Consumers Energy 530 W Willow Lansing, MI 48920 Telephone (517) 374-2306 Fax (517)374-8419 E-mail: dslucian@cmsenergy.com
Introduction
In 1995 Consumers Energy went through a reengineering process that resulted in the placing of the customer contact closer to the customer. Four major headquarters were divided into 40 customer service centers. This presented a problem with providing access to the service records in multiple locations. The solutions identified were either duplicate 2.5 million service records for distribution to the smaller offices and then maintain duplicate sets or convert the paper records into an electronic format and provide electronic access over the company LAN. The chosen solution was electronic access. This paper will present the process used to accomplish this conversion. Data Modeling To determine the relationships of the information on the service record card a group of users was gathered to model the data. This model was then used to design the logical database, which would be the repository of all of the information about the gas service such as address, size, material, vintage, etc. This session also determined that along with the information that would reside in the database a scanned image of the sketch showing the service route would also be required by users to perform their daily activities. Source Preparation Although Consumers Energy has 1.6 million gas services there were approximately 2.5 million record cards because the information for each service could be contained on multiple cards. A process was developed to convert historical information to one record, which would contain all of the information about the active service and also show the location, size, material and vintage of any previous service that had been renewed or retired. A cleanup process was used to ensure the information that needed to be converted was readable and that the sketch on the service record card was complete and dark enough to produce a good image when scanned. The scrubbing team obtained lists of billing addresses from the company record system and used them to produce labels showing the addresses, which were then matched up with the record card when it was scrubbed. This significantly improved the matching process with the customer record system. Conversion After the source preparation process was complete the scrubbed record was scanned at 400 dots per inch into a CIT IV format. This information was then sent on tapes to the conversion vendor. The scanned records were then printed and the information was keyed into a flat file to be imported into the RDBMS database. The scanned record of the whole service card was then cropped to include only the sketch showing the location of the service in relationship to the building, the road right of way, and the gas main. When all of the conversion processes were complete the information was then delivered from the conversion vendor in batches. The small deliveries were then loaded into the database and the scanned images were loaded onto the SIMS servers. QA/QC Process A quality control process was used on each of the deliveries to ensure quality images and matches of the database attributes to the correct scanned image. Also at this time a three step matching process was used to match the SIMS record with the addresses in the customer record system. This process was part electronic processing and part manual interaction. Hardware Configuration Two Windows NT Servers hold the Oracle database and all of the scanned images. These servers are located in Consumers offices located forty-five miles apart. All input of new records and updates of existing records are done on the master server. Replication software is running all of the time to duplicate the database and the images from the master server to the backup server. Therefore there are two exact duplicate servers running 24 hours a day 7 days a week. The interface that users use to view the service information checks at startup to see if the master server is up and accessible. If for any reason the master server is down the application then signs on to the backup server. This provides access to either server without any interaction required by users. Normal maintenance on each server and the relational databases such as backups are performed at twelve hour differences on each server to ensure user access to service information 24 hours a day 7 days a week. User Interfaces LAN/WAN Access The conversion vendor developed a viewing and updating interface using Borland’s Delphi product, which is currently on over 840 desktops throughout the state at Consumers offices. The application was built into a package that is auto distributed to any managed desktop over the company network. The viewing tool provides the capabilities to search on anything in the address. The database is indexed for the most common searches so most searches through the 2.4 million addresses take only a few seconds. The result of the search is displayed on screen for the user to select which record to view. Once a record is selected, users view all of the information about the service facility that resides in the database such as;
WEB Access As the project progressed a need was seen to provide limited access to gas service information to governmental agencies, consulting and design firms and some construction contractors. To satisfy this need a Java based WEB page has been developed to allow access over the Internet. This access is not for the general public but is controlled by a user name and password. A dedicated WEB server outside the company firewall handles requests for service information from the WEB page and processes the request by accessing the database for searches and then sending the results back to the users browser. The information available in this format is currently limited to the size, material and vintage of the service facility along with the scanned image of the sketch showing the location of the gas service. User Acceptance The use of the SIMS application has grown steadily to about 20,000 queries per month using the SIMS Delphi application over the company LAN/WAN. Users are very satisfied with the speed of a query and the wide use of wildcards built into the application. An exact street number and the street name are usually all that is needed to find a record. Users can also use address ranges to identify multiple services on a street as well as search by street name in a certain town and range. Some of the original records were very old and contained only minimal information compared with new records installed recently. A user with a gas business background would interpret the information on an old record automatically without realizing they were doing it. The information in the database for these types of records is minimal because it was missing on the original record. However users do not do the same type of interpretation when the information is presented electronically. Training and mentoring has significantly helped users use the application efficiently to obtain the results required to perform their daily work. Conclusion The successful completion of SIMS project has provided desktop access to gas service information to hundreds of users that would not have had access in the past. The scrubbing process and the matching with the customer record system has significantly improved the records. The process of inputting of new records and having them available to users has been shortened from months to days. The WEB access by consulting firms and governmental agencies has helped reduce damages and also by providing access of service information to the outside staking contractor has improved staking for preconstruction. All of these factors have helped Consumers Energy manage the gas service facilities efficiently and stay competitive. | ||
| © GISdevelopment.net. All rights reserved. |