Meridian Awards 2010

Synopsis

To effectively realise efficiencies, Northern Ireland Water (NIW) recognised the need to adopt an asset data-centric approach to asset management with unique asset referencing across the organisation. This resulted in the recent investment in a Corporate Asset Register (CAR), a geospatially enabled single asset inventory for infrastructure and non-infrastructure assets.

There was a requirement for the NIW GIS to be at the heart of the CAR for below ground assets. The existing NIW GIS, a bespoke Network Information Management System (NIMS) using MapInfo Professional with a Visual Basic 6 interface, for managing the water and wastewater network infrastructure information, would not be able to fulfil these requirements.

Restrictions of the existing solution:

• Operated on a flat MapInfo file structure rather than an RDBMS
• Spatial data had to be stored in separate geographic units to allow multi-user editing
• Maintaining unique references for individual infrastructure elements (essential for integrating with other corporate systems) could not be easily done
• A substantial amount of manual intervention was required to manage the data

The solution identified was for an Oracle 10g Spatial database to provide a single source for below ground asset data. The asset-centric approach was cost-justified and approved based on its ability to support the streamlining of business processes by facilitating improved data collection, enabling the delivery of asset data to all areas of the business and providing the data platform for business system integration.

It was decided to explore the apparently cost effective option of upgrading the existing GIS solution. WRc were approached to investigate the feasibility of migrating the current NIMS solution and data from flat MapInfo files to a single, shared Oracle 10g Spatial data warehouse. The upgraded project was given the name of AssetMapper.

The business objectives for AssetMapper were to:

• Prove the viability of the approach
• Improve data transfer across NIW systems (Figure 1)
• Support Mobile Work Management (MWM), Customer Contact Centre, economic regulatory requirements and Asset Management Planning
• Provide a low cost Oracle-based GIS information warehouse
• Secure the existing data in a managed database environment
• Support the CAR initiative by providing a corporate source of below ground assets
• Not limit future options in developing a new strategy for corporate GIS

The technical objectives of the AssetMapper project were to:

• Migrate all existing infrastructure data into an Oracle 10g Spatial data warehouse
• Maintain the GUI and functionality of existing system
• Address the issue of unique referencing at entity level
• Provide a multi-user environment, removing the need for splitting infrastructure data by regions
• Provide the mastering of site and facility assets and integration to the Ellipse non-infrastructure database

Proof of concept

To progress with the AssetMapper approach it was necessary to prove that the original GIS solution could be upgraded to work viably within an Oracle environment.

In order to do this WRc, who were maintaining the existing GIS, were asked to develop a Proof of Concept.

The Proof of Concept had to utilise the following technologies:

1. Oracle 10g Spatial R2
2. MapInfo Professional V9
3. Original NIMS harness

A key aspect of the existing solution is a controlling database (Properties Database) which allows users to determine all aspects of the network model including connectivity rules, feature types, symbology and validation rules.

The Properties Database allows NIW users to:

1. Define the network objects such as pipes (mains, sewers), nodes (valves, junctions), fittings (hydrants) and points (bursts)
2. Define object datasets by linking predefined attributes to network objects. Attributes are given a unique name are of an attribute type e.g. text, number data, code, identifier
3. Define control rules. This allows NIW to identify validation rules for attributes (min and max values), connectivity rules (what features can connect to others) and symbology representation based on attributes.

The Proof of Concept had to prove that it was possible to develop a model and structure within an Oracle environment that was compatible with the existing GIS harness, but which would not restrict future migration to other GIS platforms.

WRc successfully developed a Proof of Concept demonstrating to NIW that the existing application and technology could be updated to maintain the infrastructure records held within a corporate database. The application developed as part of the Proof of Concept had similar performance and identical functions to that provided by the existing system.

It was also shown that the Properties Database was capable of providing a robust rules engine for all network functions created to maintain the network.

Full Redevelopment

Once the Proof of Concept was accepted, full redevelopment of the system began. Redevelopment was essentially an extension of the Proof of Concept ensuring that previous development undertaken was not wasted.

As individual functions were redeveloped the outputs were informally tested at WRc and then provided to NIW. NIW users of the existing system then tested the output on the NIW network to ensure adequate functionality and performance.

Under the terms of the project contract NIW had the right to terminate the project, without financial penalty, if it was felt the redeveloped application had inferior performance compared to the existing application or that it would not meet the business needs. Progress to the next set of functions was therefore dependent on the successful performance of the previous set of functions.

This approach meant that very few changes were identified during the final system acceptance test and that a rapid full roll-out could be achieved.

A web interface was developed to provide access to the Properties Database. The interface enables an approved user to define and maintain the network rules held within. It also provides access to a series of Oracle stored procedures to automate the process of creating tables, validating data and populating the desired metadata tables required by Oracle and MapInfo Professional. The interface also provides access to other procedures which were developed to enable NIW staff to efficiently update symbology and modify the data available to the users.

Implementation

Implementation was made straight forward by the continual involvement of NIW staff during the redevelopment stage. Since the application was being continually tested by NIW staff on aspects of performance and functionality the final implementation was just an extra small step.

NIW were fully involved in the entire data migration stage. The Properties Database was used to ensure the integrity of the data during migration. As a result NIW staff are now better able to understand the systems and how AssetMapper, including MapInfo Professional, and Oracle interacted with each other.

The final implementation of the software required 5 days of user downtime in which network maintenance operations were suspended. This allowed the data to be migrated to the database model held within Oracle.

Once installed the AssetMapper project was extended to provide the control and synchronisation for the creation of new assets in the Corporate Asset Register (CAR).

The two systems (AssetMapper and Ellipse) are synchronised via an NIW-configurable, XML-based automatic information exchange schema. The creation or modification of a site or facility within AssetMapper causes a number of database triggers to fire which populate an audit table, create an XML file and then transfer XML files to the external Ellipse server. Once the XML file is received the information is imported into the Ellipse system ensuring data integrity across both database elements of the CAR.

Training

Rather than provide training at the end of the project NIW key personnel continually engaged with WRc on how the system would work and how NIW could make the best use of the Oracle 10g Spatial database. This allowed users to fully understand how the system works and provided them with a foundation of knowledge they can use to maintain the system with minimal input from WRc.

As part of the continual development of the skills within the Asset Management directorate NIW personnel are being actively encouraged to take more responsibility for the administration of the AssetMapper system and Oracle 10g Spatial data warehouse.

Project Outcome

The implementation of AssetMapper has been hugely successful with the project being completed on time and to budget. All of the original project objectives were met and since go-live in October 2008 the system has been running without failure. The support and maintenance contract has allowed NIW to implement additionally time-saving functionality at a fixed price.

Now that NIW have separated their spatial data from any proprietary GIS application other systems and initiatives such as the Leakage Management System, Telemetry and DIAMOND (corporate data warehousing) can gain direct access to asset data enabling NIW’s “single version of the truth” philosophy to be realised.

AssetMapper along with the DIAMOND now resides at the heart of the CAR with many other business critical applications relying on the information maintained by the system.

AssetMapper has become a critical integral part of the business. The migration of the data into an 10g Spatial data warehouse has opened up the data to more areas of the business. Two key initiatives; the Data Quality project and the Business Improvement Programme have seen the benefit of this approach with it proving an improvement in data quality and business processes. This has provided a major benefit at a time when budgets within the water industry are being cut by the regulator.

A significant deliverable from the project is that NIW own the IPR to their asset database model and GIS software. Being a completely open system access to the asset data is not restricted by licence unlike some systems, which prohibit direct access to the data through native Oracle applications or other GIS. NIW can use any number of systems to view the data without the need to seek permission or additional data access expenditure.

Support

NIW have continued to use WRc to provide routine support and maintenance of AssetMapper as part of yearly renewable contract. This service also includes the administration of the Oracle 10g Spatial data warehouse.

Since the system has been in place a number of additional functions have been added. NIW continue to maintain a ‘wish list’ of functions that are then authorised for development as required.

NIW are currently in the process of purchasing and implementing a new GIS viewer with WRc providing expertise in the use of spatial internet technologies and the impact on the overall Oracle-based spatial data strategy.

NIW are also working with WRc on a spatial data integration initiative focussing on automating currently disparate processes for assigning and recording work orders and providing spatial associations with customer records. This will achieve a significant saving of both time and resources over the current manual methods, enable more informed decision making, provide faster Customer response times and ultimately further improve the quality of data within NIW.