The City of Brantford Manages its Municipal Infrastructure

Talend Open Studio for Data Integration leverages Web Services to provide real-time data integration between systems that manage infrastructure, work orders, utilities, etc.
Beyond the obvious cost savings, the open source model also brings a tremendous advantage: the community that supports it. We have received excellent support from other users and from the Talend team via the forums and bugtracker.
Muzammil Rajpurkar, Database Administrator

A vibrant community in a strategic location

Situated on the picturesque Grand River, the City of Brantford features a vibrant community life and a strategic location in the heart of Southern Ontario. It is just a short drive from Toronto, Hamilton, London, Niagara Falls, and Buffalo, New York.

Acknowledged internationally as "The Telephone City,"€ Brantford marks with pride that dramatic moment in 1874 when Alexander Graham Bell invented the telephone, launching one of the world's greatest communication industries. Three years later, Brantford was incorporated as a City with 10,000 individuals. Today, the City of Brantford continues to grow with a population of 90,192 (source: Canada 2006 Census).

Proud to be the hometown of "€œthe Great One"€, Wayne Gretzky, Brantford is a city where amateur and minor sports are an integral part of community life. Wayne's father, Walter Gretzky, still lives here in the city he's always called home.

A new Enterprise Asset Management system

The IT Services Department of the City of Brantford is responsible for the maintenance, integration, and availability of the off-the-shelf "€œbest of breed"€ systems and internally developed custom applications implemented by the City to serve its citizens. The City has deployed the Avantis Enterprise Asset Management software solution, to manage asset and work order information. As part of this deployment, IT Services needed to integrate to Avantis data contained in disparate systems operated by the City to manage thousands of linear assets within the water distribution, sanitary, and storm sewer networks. Most of this data is contained in a custom system, MIDS (Municipal Infrastructure Data Standard) which, along with Avantis, uses Oracle as the back-end DBMS.

"€œWe had multiple data integration needs related to the Avantis deployment,"€ explained Scott Hall, Manager of Corporate Information for the City of Brantford. "€œInitially, we needed to load the system with data related to our infrastructure. Then, once the system was up and running, we needed to maintain the data in sync between the reference applications and Avantis, ensuring that any updates to the source data would be propagated to Avantis."€

Historically, data integration processes used by the City of Brantford were developed as Java programs, and executed by the Windows Scheduler. However, maintenance of these processes was complex and costly. "€œThe MIDS to Avantis Interface project was a good opportunity for us to study data integration solutions,"€ explained Muzammil Rajpurkar, Database Administrator with the City of Brantford. "We looked at several GUI-based data integration products, focusing specifically on their robustness and the return on investment we could get out of them."

Robust and versatile data integration

After discarding several process-oriented integration solutions, IT Services settled on the data integration approach, more versatile and better suited for performing both batch loads and real-time data synchronization. "We selected Talend Open Studio for Data Integration for several reasons,"€ said Muzammil Rajpurkar. "€œThe breadth of connectors provided by the solution allows us to connect to all our sources and targets. It is also a very robust solution, which guarantees that the synchronization processes are running reliably and that our systems are always in sync without any data loss. And then, the open source model limited the need for an upfront investment, an important factor in a fiscally responsible organization, financed by taxpayers' money."

Of special importance for IT Services is the native support for Web Services, built into Talend Open Studio for Data Integration. "€œThe Talend processes extract data from the Oracle source systems in real-time, every time a record is created or updated,"€ explained Muzammil Rajpurkar. "€œThis data is converted to an XML format, and it is transformed and enriched through XSLT transformations to match the format required by Avantis. It is then inserted or updated in Avantis through their Web Services API. Finally, the return codes from the Web Services are analyzed to confirm the success of the operation. All these operations are performed automatically by Talend."€

In addition to the Avantis data integration project, IT Services have initiated other projects using Talend Open Studio for Data Integration. One of them consists of extracting data from the Daffron utilities management software that manages the electrical and water supply in the City of Brantford. "€œThe Daffron system is based on DB2 on the AS/400 platform, and it was important for us to extract the water meter location and contact information on the utilities networks from this system, so that they are also loaded in the Avantis platform in order to generate work orders,"€ clarified Muzammil Rajpurkar. "€œTalend Open Studio for Data Integration, with its native DB2/400 connectors, allowed us to develop this process very quickly."

Beyond these projects, Talend Open Studio for Data Integration has become the standard integration platform for all data migration and synchronization processes deployed by the City of Brantford.

"€œBeyond the obvious cost savings, the open source model also brings a tremendous advantage - €”the community that supports it,"€ concluded Muzammil Rajpurkar. "€œWe have received excellent support from other users and from the Talend team via the forums and bugtracker. Open source is clearly the model of the future for software development, and Talend as a vendor has fully embraced it."€