Enabling digital transformation with data integration

The City of Coquitlam is one of the fastest growing municipalities in the province of British Columbia, Canada. With over 150,000 residents, the city is dedicated to enhancing its programs and services, business operations, and customer experience through smart city initiatives and technology driven strategies.

For the city’s Information & Communications Technology (ICT) division, data integration initiatives are one of the main pillars in improving operational efficiency and productivity, business process optimization, and connecting data across the city. To date, the ICT division has used FME to integrate over 15 business applications and web services, including IBM Maximo, AMANDA, Tempest, Oracle eBusiness Suite, Amazon S3, and more.

With data existing in silos and disparate systems, this limited teams from using the best-for-fit applications. Time and effort spent on manual and labor-intensive processes such as data cleaning and validation meant that operational efficiency suffered. By automating several data integration and application integration processes and removing these data silos, teams are now freed from repetitive manual work and the risk of human error is eliminated.

Let’s take a look at five of the many city projects where they’ve implemented FME:

Project: Garbage and organics cart collection

The city integrated data between multiple systems to gain greater visibility into their recycling and waste collection program and to improve customer experience.

The city partners with a third-party vendor for the exchange and delivery of garbage and organics carts. They needed to integrate data between IBM Maximo (enterprise asset management platform), Tempest (utility billing system), and spreadsheets sent and received by the vendor, in order to process service requests in a timely manner.

Using FME, the workflow parses Maximo for service requests, such as calls, issues, and new or updated requests, and creates a report containing a work order list sent to the vendor as a spreadsheet. The vendor will update and return this spreadsheet based on the work completed that day, and internal staff will update Maximo based on this information. FME then automatically extracts these updates and loads this information into Tempest to reflect any changes in the resident’s utility bill. As there are different costs associated with the various sizes of carts, ensuring an accurate update in Tempest is crucial to minimizing manual billing errors.

By implementing FME, this resulted in a significant reduction in the work order turnaround time, eliminating potential manual errors, while streamlining the data sharing process between city staff and their external vendor.

Project: Self-serve spatial data catalogue

The city manages a variety of data that’s available to the public, such as parks, transportation, and planning, and they wanted to make this information easily accessible through a self-serve portal.

Coquitlam’s Spatial Data Catalogue is a self-serve portal that allows users to browse and download available data, including property boundaries, roads, utilities and large data sets such as orthophotos and LiDAR. Incoming requests used to come through emails, in-person, or over the phone. Invoicing was sometimes required as staff had to manually extract and package files (which are shared as Shapefiles, Geodatabase, or CAD). A typical data request used to take 2-4 hours to complete; now this is all automated using FME. Users know where to get this data and can request for it themselves with FME automating this delivery process.

This automation extracts spatial data from multiple databases and stores files in Amazon S3. All job failure communication is sent to the GIS administrators group and to date, this particular node has never been executed. Note: EPW stands for Engineering & Public Works and PRC stands for Parks, Recreation & Culture Services.

Locating an individual LiDAR data file is one example of how users can find and download the data they need.

Project: LED streetlights conversion program

This program aims to replace 2,500-3,000 lights per year by 2022 and to reduce energy consumption by 57%. The city needed to maintain high quality and accurate data between its contractor and internal teams.

With a project at this scale, manually keeping up with the number of inspections and work completed by the external contractor proved to be challenging and time consuming.

Previously, staff from Engineering & Public Works and Admin teams received spreadsheets from the contractor and were faced with poor data integrity and labour intensive data processing. It became a demanding task for staff to find and process the data they needed and accurately manage billing and invoicing in Oracle eBusiness Suite.

Using FME, staff can now perform a variety of improved operational tasks, including quality assurance and data cleansing, create workforce assignments, build reports, see data in real-time, and ensure a reliable billing process.

Project: Bulk packaging of as-built drawing requests

Using their own in-house FME Server App, the city created an automated way to bulk process, package, and respond to as-built drawing requests.

The ICT division has built their own FME Server App for internal use that allows efficient and transparent data sharing between staff and contractors. External contractors and internal capital construction crews can make a request through the app, define the desired area and download related as-built drawings.

Requests may involve drawings for rehabilitation projects, construction, new development, or for a variety of road and utility improvement projects. When a request is submitted, internal staff will use FME Server to query their document management system, OpenText, to prepare and package files for download.

Project: Automated License Plate Reader for parking violations and ticketing

The city needed a way to centralize permit information for bylaw officers due to an expansion in public parking areas.

When the local SkyTrain system was extended into city centres, parking permitting and management staff needed to find a more efficient way to enforce parking regulations. The city mounted several bylaw enforcement vehicles with an Automated License Plate Reader (ALPR) to monitor new parking hotspots near stations.

Previously, bylaw officers had to manually enter and look up a license plate from multiple systems. Now, the data consolidation process is automated by FME. An FME workflow pulls parking permit license plate information from AMANDA (permitting platform) and makes it available to the ALPR system. As a result, bylaw officers can now quickly pull information and efficiently manage busy parking areas.

T: An ALPR unit mounted onto a bylaw enforcement vehicle;
B: A bylaw officer reviewing license plate and permit information provided by the ALPR system.

FME for the entire city government

With the adoption of FME as the backbone in bringing systems together, data can now flow freely to get the job done. The ICT division has seen significant improvement in data integrity and accessibility between its internal teams and external vendors.

“FME is enabling digital transformation within the City. We could not achieve our goals without the products and support from Safe Software,” says Sean Watson, Business Systems Analyst. By leveraging the FME platform and scaling this across their entire city government, the value of data is truly maximized and staff can access and share data using the best fit-for-purpose applications. Sean and his colleagues are continuously working on data integration initiatives across the city and new projects are already underway for 2020 to help bring more quality programs and services to staff and citizens.

To learn more about how your local government can benefit from data integration, watch our latest webinar, How Local Governments Improve Operations with Data Integration Workflows.