Integrating data in the Information System - An Open Source approach

WHITE PAPER Integrating data in the Information System An Open Source approach Talend White Paper Integrating Data in the Information System: An Open Source Approach Table of Contents Most IT Deployments Require Integration ................................. 3 Scenario 1: Data Migration ................................................... 4 Scenario 2: e-Business Application Deployment .......................... 6 Scenario 3: ETL for Data Warehousing .................................... 10 Open Source Model and Advanced Capabilities Provide for Optimized Cost of Ownership ............................................... 12 Conclusion ..................................................................... 14 Page 2 of 14 Talend White Paper Integrating Data in the Information System: An Open Source Approach Most IT Deployments Require Integration One of the requirements for staying competitive today is deploying new systems—RDBMS, business applications such as CRM or ERP, data warehouses, etc. Whether these systems are based on “traditional” commercial software products or open source solutions, they are likely to be deployed in a heterogeneous environment comprising both traditional and open source solutions, and where internally hosted packages coexist with systems hosted under the Software as a Service (SaaS) model. Indeed, as they add new systems, very few organizations can afford the luxury of redesigning their entire information system. Any new technology deployment in an enterprise information system will thus require the newly deployed system to interoperate—in one form or another—with a number of other applications or databases. This interoperability, which is essentially based on the exchange of data between systems (termed data integration), guarantees the consistency of data in the overall information system. Consider, for example, an organization that runs SAP as their ERP, and deploys an e-Business portal based on MySQL. They also implement a new SaaS CRM application such as SugarCRM or Salesforce.com and a data warehouse that runs on ParAccel or Netezza. Such an IT environment would not be at all unusual. Data would need to be replicated between the CRM and the ERP and collected from the e-Business Portal. In addition, data from all systems in the environment would need to be loaded into the data warehouse. This white paper describes a number of real-life interoperability scenarios for applications and databases and explains how Talend Open Studio, a powerful, open source data integration solution, addresses various data integration requirements. Page 3 of 14 Talend White Paper Integrating Data in the Information System: An Open Source Approach Scenario 1: Data Migration This first scenario examines a data migration example. When deploying a new business application, few organizations have the luxury of starting from a clean slate. In most cases, they need to recover data from legacy systems, enrich it with additional information, and transform the data because their legacy and new data structures do not match. Let’s have a closer look at this actual data migration challenge. “We needed to migrate data from our legacy home-grown CRM system running on DB2 UDB to the Salesforce.com CRM, deployed in SaaS mode. We used a MySQL database as a staging area to prepare all the data before the final load of the online CRM. Data transformations (to match the Salesforce.com data structures) occurred prior to loading data in the staging area. We also enriched this data with user information (extracted from an internal LDAP directory) and customer address geocoding, collected from a third-party provider via a set of Web Services. And because we knew that the quality of data in the legacy CRM was sometimes poor, with many duplicate records, we channeled all data through various data quality filters. We finally loaded the enriched and cleansed data in Salesforce.com.” — Mark S., CRM Project Manager at a regional retail chain To accomplish this migration, Mark S. and his team were able to leverage a number of Talend Open Studio’s advanced components and connectors: Page 4 of 14 Talend White Paper Integrating Data in the Information System: An Open Source Approach The tDB2Input component (DB2 connector), which provides native connectivity to DB2. This connector includes advanced metadata introspection capabilities that enabled the project staff to quickly understand the structure of the DB2 CRM database – even though all documentation for this legacy system had long ago been lost. The tFileInputLDIF component (LDAP connector), used to retrieve user information from the company’s LDAP directory and use it as an input to the mappings. The tWebServiceInput component (Web Services connector), which enabled the connection to a provider of geocoding services—passing the customer’s address as parameters and returning the geocoded address. The tMap component, which allowed job developers to easily map data from DB2 to MySQL, add enrichments from LDAP and the geocoding Web Services, and graphically design the data transformations from the DB2 schema to the Salesforce.com data model. The tFuzzyMatch component, which provides advanced data matching capabilities to identify duplicate records— even if the data isn’t a 100% match. The tMysqlOutput component (MySQL connector) which provides native connectivity to MySQL in which the Salesforce.com data model had been duplicated (thanks to Talend Open Studio’s advanced metadata management capabilities). The tSalesforceOutput component (Salesforce.com business connector), which connects natively to Salesforce.com, leveraging its native Web Services APIs and SOQL language. Mark S. concludes: “Talend Open Studio’s intuitive drag-and-drop interface dramatically increased our productivity. We had initially estimated 35 days of work for performing this migration with custom development. Talend Open Studio allowed us to migrate our CRM in just 4 days. Page 5 of 14 Talend White Paper Integrating Data in the Information System: An Open Source Approach And, because Talend Open Studio relies on industry standard languages, our ramp-up on the product was extremely fast, and we were able to leverage existing expertise.” Talend Open Studio’s intuitive Job Designer interface Scenario 2: e-Business Application Deployment This scenario studies the deployment of a new online e-Business application.