ArcUser Online
Spring 2011 Edition
Integration Project Saves a Million
Utility makes business information accessible through single seamless path
By Toni Jackson, GISP, San Antonio Water System
This article as a PDF.
SAWS system information was patchwork of paper maps, electronic tables, CAD files, mainframe tables, and various other disparate databases.
No matter how talented the landscaper, without consistent care and rich soil, a garden will not flourish. The same is true for corporate data repositories, which must be planned, built, and maintained with care. Isolated databases typically don't provide data-rich business intelligence. It's an analogy that utility company San Antonio Water System (SAWS) of San Antonio, Texas, understands well.
SAWS provides water to the seventh-largest city in the United States and was adept at cultivating and maintaining a significant network infrastructure that ensured clean water and efficient sewer services for its customers. It also accumulated many data layers specific to SAWS network assets and service locations that supplied business intelligence.
However, SAWS had not developed harmonious, integrated information systems. More important, geography had been overlooked. With no seamless connection among mission-critical data plots and no unified spatial asset references, SAWS was nurturing "a subpar bed of overgrown weeds," making it difficult to maintain its high level of service and plan for business growth, according to SAWS GIS manager Cindy Tuttle.
"Our information management systems involved a real patchwork of paper maps, electronic tables, CAD files, mainframe tables, and various other disparate databases," said Tuttle. "All of those isolated pockets of information created a perfect breeding ground for data duplication and inaccuracies and made sharing geographic data and updating it really difficult. Moreover, without an accurate spatial view of our assets and customers, it made it hard to analyze our business, adequately respond to clients, and proactively plan for business development."
Recognizing the need for a better solution to manage and share important business data, SAWS launched an initiative to create an enterprise-wide GIS and an asset management system. This combination would provide an integrated and automated environment to easily update and maintain accurate map, network, operations, and account data across the entire organization. However, Tuttle realized that to be successful, this ambitious project would need to begin by cleaning SAWS data.
So, SAWS GIS professionals began meticulously digging through disparate databases, weeding out data inaccuracies and duplications, seeding data gaps, pruning existing data layers, and integrating and harmonizing dataflows in an effort to clean, purge, and integrate critical business data.
The GIS team converted hundreds of MicroStation-based water/sewer map layers, such as this one, into GIS-based, spatially accurate map files that support the asset management system.
Two years later, Tuttle and her small staff have triumphed over significant data conversion challenges, enabling them to fulfill SAWS' vision of producing business information that is accessible through one seamless path. The web-enabled GIS not only provides a seamless view of SAWS' service area—its networks, assets, service points, and taps—but also provides the core datasets for the asset management system. It allows personnel to better assemble and schedule work assignments, monitor the pipeline network, perform quality and as-built inspections, correct discrepancies in the customer billing system, and analyze its customer base to identify new business opportunities—and business is blooming because of it.
Serving One Million Customers
Though the history of providing water and wastewater services in the San Antonio, Texas, region dates back to the late 1800s, SAWS was born as a single water utility in 1992. Created through a consolidation of three previously separate utility providers, SAWS today provides water and wastewater services to about one million customers and maintains more than 9,000 miles of water and sewer mains buried below its 560-square-mile service area.
True to its conservation mantra, SAWS built and manages the nation's largest recycled water delivery system. The system distributes approximately 29 million gallons of recycled water per day that flows through more than 100 miles of pipeline to golf courses, parks, and commercial and industrial customers throughout the city. In September 2010, SAWS achieved the biowaste-recycling trifecta at its Dos Rios Water Recycling Center. The first plant of its kind in the country, the facility recycles wastewater and waste solids and converts the methane off-gas into natural gas to sell on the open market.
As SAWS operations have expanded and diversified, it has continually accumulated corporate information that records and monitors the company's daily operations. However, efforts to create, maintain, and connect that data have not been as aggressively pursued as SAWS' service expansions. Without a direct and stable hub to internally connect the company's external service points, SAWS was inadvertently washing business opportunities down the drain.
"Carrying out routine, simple business tasks would often send staff on laborious, long hunts—either physically or electronically—to find all the information they needed," said Tuttle. "Then, when they did find data, it often wasn't complete or accurate, particularly asset attributes and location data such as addresses, which are critical to our service." Core datasets, such as assets and customer accounts, were held in different databases that lacked any geographic representation. "That was impacting our ability to comprehensively analyze our networks, service connections, and customers to identify savings or revenue opportunities," said Tuttle.
In 2005, SAWS launched an initiative to correct this data disconnect. A centralized Hansen asset management system and an enterprise-wide ArcGIS platform would provide personnel with a unified, detailed, and spatial view of the organization's most critical business information and automate routine tasks such as generating work orders. Though the move would provide data intelligence to enhance SAWS business operations and services, building the system would only be possible by resolving significant data conversion challenges.
Integration Challenges
One of the maps created after the team reviewed hundreds of MicroStation maps, attribute layers, and account tables to tie asset locations, attributions, and customer service points to 500,000 addresses.
It didn't take long for the small GIS team to identify notable data interoperability challenges. Of particular concern were how best to validate, harmonize, and geographically visualize the account data held in mainframe tables, billing data maintained in separate mainframe tables, operations tabular data in another database, and hundreds of MicroStation-based water/sewer map layers stored in another database.
"All of our datasets had been created and maintained in isolation, with a single-minded purpose for each department," said Tuttle. "So significant challenges arose when we tried to integrate data from different formats, schemas, and databases to serve a unified information management system. These general incompatibility issues then became magnified when we needed to map all that data to bring a precise visual element to every tabular list and numerical value."
However, even more problematic than rectifying missing attributes, inconsistent schemas, or incomplete datasets was the inaccuracy, and often complete absence, of precise spatial representations of customers' addresses. "Addresses have been recorded and stored in myriad ways throughout SAWS, the majority of which have been in tabular form with no tied geography," said Larry Phillips, a planner with SAWS. "The problem with that is accurate location data is fundamental to GIS—it is the basis for which all other elements are referenced. So it was clear that the data conversion process had to start at the root of all other data layers and functionality—the address."
An intense, daily effort began to untangle and weed through hundreds of MicroStation map and attribute layers and account tables to tie asset locations, asset attributions, and customer service points to their correct addresses—all 500,000 of them—one point at a time.
Every tabular address field had to be converted into a geographic location on a map. "Address points had to be on the correct parcel where they belong, and service points had to match the actual service location of their meter or sewer connection," said Phillips. "We had to validate and correct every one of those points, and we were doing this manually, one by one, for half a million points."
After two years of this laborious process, it became clear that the GIS department needed to find a more expeditious but not overly costly way to resolve such data interoperability issues to successfully launch SAWS' integrated information system.
Trolling the web, Phillips discovered Esri's ArcGIS Data Interoperability extension.
This tool uses Safe Software's FME spatial ETL (extract, transform, load) technology that enables users to convert, integrate, transform, and distribute data in more than 100 geospatial formats. It provides more than 200 transformers to help automate functions and perform quality control checks across different data sources. In short, it seemed to be the "do more with less" tool they needed.
From Days to Minutes
Thirty days after implementing the extension, Phillips had successfully transformed a two-day data manipulation task into a five-minute exercise. SAWS road network layers are routinely received from a third-party source, requiring a GIS professional to spend two days manipulating and correcting the road features to properly integrate with the GIS. Using the extension, Phillips created an automated workflow that transforms, corrects, and integrates each new road file with one mouse click. The time savings just for the road tool paid for the first extension license.
For the GIS team, that automation was not only an "aha" moment. Merging road layers, the team suddenly saw the potential for it to become the tool of choice for cleaning up, integrating, manipulating, and distributing more than three million data features and supporting a host of automated workflows and business analysis tools.
Million Dollar Savings
To ensure the central data layers were ready for operational applications, Phillips applied the extension to both validate the initial data cleanup and rectify any discrepancies as well as create the framework to easily update and integrate new customer address accounts and service points. Because MicroStation-based sewer and water features were not directly connected to their attributes, it was difficult to readily identify relationships between sewer lateral lines and sewer connection points or their exact geographic locations.
Using the tool created with the Data Interoperability extension, Larry Phillips, a planner with SAWS, created an automated workflow that transforms, corrects, and integrates each new road file in one quick operation. Previously, the same operation required two days.
Using the extension, Phillips designed an automated point snapper workflow. The software automatically snaps a sewer service point to its correct lateral line, along with all the attribute information relative to each point and line. In three days, the tool corrected and moved 211,000 sewer lateral points, saving the two GIS professionals eight months' work.
Phillips customized a similar process for spatially correcting and linking sewer laterals to manholes—a task that he says would not have even been considered possible with previous software tools. Previously, measurement references for distances from laterals to the downstream manhole were annotated on each MicroStation drawing in several numerical formats. Phillips accurately tied each lateral to its relevant manhole and transformed those annotations into precise and consistent spatial references attributed to each lateral/manhole asset. In two days, the tool populated the GIS with nearly 171,000 sewer lateral measurements.
Now that the extension has readied the GIS, the team continues developing new ways of applying it. For example, Phillips created three customized quality assurance/quality control workflows to routinely analyze SAWS' sewer, water, and address/service point data layers. Set to run weekly, the tool checks for data discrepancies and automatically alerts the GIS team of problem areas. Quality checks are not confined to the GIS; the tool also analyzes the Hansen data to ensure both information management systems are in sync.
With once insurmountable data conversion issues solved, the GIS is now ready to support several Hansen-based operational applications and functions including an automated work-order module that enables personnel to seamlessly integrate service requests and automatically generate corresponding work orders—functionality that allows them to execute 11,000 orders a month. By the end of 2010, the GIS also served the core data for more automated business tasks such as permitting and customer service.
Conclusion
The extension "has truly allowed us to be much more productive in much less time with far fewer people and with much higher-quality results," said Phillips. "We have significantly reduced labor efforts from days into hours for a host of routine data tasks." In addition to the consistent, integrated, accurate, and dynamic GIS, SAWS now has a seamless environment to view and analyze its business.
"We accomplished a massive data conversion project and developed an enterprise-wide GIS with an extremely small team and very little cost," added Tuttle. Without the cost-effective Data Interoperability tool, "we would have needed to double our team to accomplish what we did with a few people's effort. In fact, we estimate the money saved in our first year alone is nearly one million dollars. Moreover, the software has freed our team to initiate projects we otherwise would have never had the time or resources to do."
With more time to dedicate to bettering the GIS datasets and functionality, SAWS' data management will continue to grow organically to support increasing demands and changes in business directions. That is an information landscape worth nurturing.
