Practical Open Source Data Integration - Case Studies & Implementation Examples

Practical Open Source Data Integration Case Studies & Implementation Examples CASE STUDIES Table of Contents ETAI unifies its data production architecture...................................................................................... 4 The City of Brantford manages its municipal infrastructure ............................................................... 6 Eurofins chooses a universal integration solution .............................................................................. 8 UMIT fights cancer with data integration, mining and analysis ........................................................ 10 MediaLab mitigates risks and accelerates data integration ............................................................. 12 Pokolm automates manufacturing processes .................................................................................. 14 Easyssur strengthens its data integration processes. ...................................................................... 16 Page 2 Practical Open Source Data Integration Case Studies & Implementation Examples Over the past few years, open source has established itself as a key component of the overall data integration market. Many organizations have adopted this model for their data integration projects. These organizations span all industries, all continents, and all company sizes. More importantly, their projects range from ETL for data warehousing or business intelligence to operational data integration, data migration, data synchronization, etc. This document presents a few selected case studies, illustrating real-life implementations of open source data integration and its associated benefits. Page 3 Industry: Technical Databases Type of project: Data Production ETAI unifies its data production architecture Talend Open Studio automates the integration of heterogeneous and disparate data and increases significantly the quality of production tasks Complex data architecture A data specialist To build automotive industry applications, ETAI collects ETAI’s advanced expertise and media mix apply to information related to vehicles (over 50,000), spare parts, three industries: automotive, manufacturing and retail. parts equivalence, suitability of parts for vehicles, repair du- The company offers diverse information solutions to rations and techniques, costs, etc. these industries: databases, software, conventions, “This is a vast domain, and there is no standard for data,” ex- trade shows and magazines. With 400 employees, plained Philippe Bobo, Director of Software and Information ETAI generates of 60 million euros ($80 million) in re- System at ETAI. “We are getting disparate data repository venue. formats, but also many paper documents which information ETAI’s business consists of producing and selling needs to be keyed in. We have decided to keep data entry data. For example, for the automotive industry, ETAI applications in the most efficient formats, to increase data collects raw data from suppliers (vehicles and parts entry productivity and reduce the cost of updates.” manufacturers), consolidates and reconciles this As a result, each time a new version of a database product data, and sells applications based on the thus-crea- is released (over a hundred times a year), all these reposi- ted technical database. These products are provided tories need to be processed: data consolidation, reconcilia- as either DVD-ROM or internet portal subscription to tion and cleansing. “This process is very time consuming, automotive dealers, repair shops, and other interes- both in time and resources, and always presents data qua- ted parties. lity challenges,” recalled Philippe Bobo. “We used to run numerous programs in Access, Java, Python, PL/SQL, etc. Also, many steps of the process are manual and require domain expertise.” Conversely, in ETAI’s other businesses (directories, ma- gazines, trade shows, etc.), it made sense to consolidate non-technical repositories, to leverage data models that are similar between verticals. “We looked for a data integration solution that could not only automate the assembly processes for the automotive technical databases, but also merge all data management systems for ETAI’s other businesses,” explained Philip- pe Bobo. “It was critical for us to concentrate our efforts around a single tool that would meet all our needs. Down Page 4 the road, even our internal systems will be using the same data integration environment.” Key facts Advanced market research 100 releases/year of the database product “Our tool research was very methodical, and started by ana- 50,000 information points related to vehicles lyzing the company’s integration processes (both manual 2,000,000 parts equivalence and automated) with the help of consultants from Apsia,” re- Industrialization of assembling processes for tech- called Philippe Bobo. “Subsequently, several products that nical automobile databases would conform to our primary criteria – especially the appli- Consolidation, reconciliation and cleansing of tech- cability to all of our businesses, and the ability to process nical data the high data volumes of the automotive databases – were Consolidation of non technical repositories selected for a pilot project.” MySQL, DB2/400, Access, SQL Server and Oracle Various subjects were presented to the vendors of solutions databases, Excel and XML files that were compatible with ETAI’s budget constraints. “Com- pared to proprietary solutions, Talend Open Studio offers greater flexibility,” confirmed Philippe Bobo. “Not a single step of the prototype was blocking for the tool. This is party due to its use of industry standard languages for which com- ponent libraries are readily available, and which accelerates the ramp up of the teams.” Talend Open Studio was also the fastest for executing a quality, we can now maintain control over an important cau- complex process of the prototype, processing 2 million re- se of errors. When we launch the assembly chain for the cords 30 to 50% faster than the other contenders and than shippable database, we know it will run without error and the existing custom program. we won’t need to edit the consolidated data.” Beyond the complexity of consolidating and reconciling To get the most out of Talend Open Studio, ETAI elected to data, the heterogeneity of data sources also created an use Talend’s services offerings. IT teams first attended ad- important challenge. ETAI’s data sources include MySQL, vanced training sessions. ETAI also subscribed to a Gold DB2/400, Access, SQL Server, Oracle, Excel, XML, etc. technical support contract with guaranteed response times. Manufacturers’ data usually comes in complex flat files. And finally an expert consultant from Talend visits ETAI on The technical database is hosted on Oracle but is deployed a regular basis to provide assistance to the development on MySQL. And the company’s backend systems run on teams. AS/400. A broad connectivity palette was hence a strong “The support Talend provides is excellent, corroborating the requirement. importance we attributed to this factor,” indicated Philippe Choosing the industry’s first open source data integra- Bobo. “The advanced expertise and the professionalism tion solution of the consultants we have worked with are an important asset for the success of our projects.