Integration von Ingres in das Informationssystem, ein Open Source Ansatz

Integrating Ingres in the Information System: An Open Source Approach WHITE PAPER Talend White Paper Integrating Ingres in the Information System: An Open Source Approach Table of Contents Ingres, a Business Open Source Database that needs Integration .... 3 Scenario 1: Data Migration................................................... 4 Scenario 2: e-Business Application Deployment .......................... 7 Scenario 3: ETL for Data Warehousing .................................... 10 Open Source Model and Advanced Capabilities Provide for Optimized Cost of Ownership .............................................. 13 Conclusion & Downloads .................................................... 14 Page 2 of 15 Talend White Paper Integrating Ingres in the Information System: An Open Source Approach Ingres, a Business Open Source Database that needs Integration Open source database solutions that provide enterprise-level functional- ity, services and support are revolutionizing today's marketplace. Com- panies no longer need to pay excessive licensing fees for the right to use a highly scalable, highly available, and high-performance database plat- form. Open source allows companies to deploy flexible, less complex so- lutions that are not bloated with excess functionality allowing database engineers and administrators to be more productive and able to focus on more mission-critical projects. An open source platform gives enterprise customers and developers the freedom to choose the right database so- lution for their specific business need. Ingres 2006 provides enterprise customers with this level of functionality and performance on an open source platform with world-class support and services. By leveraging both the affordability and flexibility of open source and the functionality and support of enterprise solutions, Ingres 2006 provides a customizable open source database solution that enter- prises can trust with mission-critical data and business applications. Today, many organizations are deploying Ingres for extremely varied uses – from e-Business to Customer Relationship Management, from Data Warehousing to Accounting, from Technical Databases to Enterprise Re- source Planning. These systems are likely to be deployed in a heteroge- neous environment made up of both traditional and Open Source solu- tions, and where internally hosted packages coexist with systems hosted under the Software as a Service (SaaS) model. As they add new systems, very few organizations can afford the luxury of re-designing their entire information system. Any new technology de- ployment 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 essen- Page 3 of 15 Talend White Paper Integrating Ingres in the Information System: An Open Source Approach tially based on the exchange of data between systems (termed data in- tegration), guarantees the consistency of data in the overall information system. Consider, for example, an organization that owns a commercial ERP, and deploys an e-Business portal based on Ingres 2006. This business also implements a new SaaS CRM application such as SugarCRM and a data warehouse that runs on Ingres 2006. 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 scenarios for Ingres in- teroperability with applications and other databases and explains how Talend Open Studio, a powerful, open source data integration solution, addresses various data integration requirements related to Ingres de- ployments. 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 leg- acy systems, enrich it with additional information, and transform the data because their legacy and new data schemas 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 Software as a Service mode. We used an Ingres 2006 database as a staging area to prepare all the data before the final load of the online CRM. Data trans- formations (to match the Salesforce.com data structures) oc- curred prior to loading data in the staging area. We also en- Page 4 of 15 Talend White Paper Integrating Ingres in the Information System: An Open Source Approach riched this data with user information (extracted from an inter- nal 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 lever- age a number of advanced components and connectors of Talend Open Studio: The tDB2Input component (DB2 connector), which provides native connectivity to DB2. This connector includes ad- vanced 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 direc- tory and use it as an input to the mappings. The tWebServiceInput component (Web Services connec- tor), which enabled the connection to a provider of geocod- ing services – passing the customer’s address as parameters and returning the geocoded address. The tMap component, which allowed job developers to eas- ily map data from DB2 to MySQL, add enrichments from LDAP and the geocoding Web Services, and graphically de- sign 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. Page 5 of 15 Talend White Paper Integrating Ingres in the Information System: An Open Source Approach The tIngreslOutput component (Ingres connector) which provides native connectivity to Ingres in which the Sales- force.