• Votes for this article 2 people voted for this
  • Dashboard Insight Newsletter Sign Up

Open Source Data Integration at Work in the Public Sector
Talend Open Studio Provides Real-Time Data Integration to the City of Brantford

by Maroushka Kanywani, Editor, Dashboard InsightWednesday, January 14, 2009

A Vibrant Community Life 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 I.T. 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 service 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, I.T. 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 – ensure 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.”

Key Facts

Industry: Public Sector
Type of Project: Real-Time Data Synchronization

Management of Municipal Infrastructure: Water distribution, sanitary and storm sewer networks, work orders integration

Oracle, DB2/400, XML, Web Services, Custom APIs

Packaged and custom applications: Avantis, Daffron, Municipal Infrastructure Assets Management, Real-time Data Synchronization, Data migration


Robust and Versatile Data Integration

After discarding several process-oriented solutions, I.T. 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 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 kept in sync, all the time, 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 I.T. Services is the native support for Web Services, built into Talend Open Studio. “The Talend processes extract data from the Oracle source systems in real-time, every time a record is created or updated,” detailed 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.”

"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. Open source is clearly the model of the future for software development, and Talend as a vendor has fully embraced it."

Muzammil Rajpurkar - Database Administrator


In addition to the Avantis data integration project, I.T. Services have initiated other projects using Talend Open Studio. One of them consists of extracting data from the Daffron utilities management software that manages 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, with its native DB2/400 connectors, allowed us to develop this process very quickly.”

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



Tweet article    Stumble article    Digg article    Buzz article    Delicious bookmark      Dashboard Insight RSS Feed
Other articles by this author


No comments have been posted yet.

Site Map | Contribute | Privacy Policy | Contact Us | Dashboard Insight © 2018