"INT413 : Replication versus ETL versus EAI"
INT413 : Replication versus ETL versus EAI Nick Warren Integration Product Manager, Sybase South Africa email@example.com / +27 (11) 804-3740 5 August 2003 Opportunity … Choosing the right integration infrastructure that works for my business. … concepts and alternatives Data Replication … Data Replication… Database to database Table based Old style of integration… - disaster recovery - site mirroring Unidirectional, sometimes bi-directional Simplified form of ETL Works well in a Client/Server model Can be queue-enabled … ‘first generation’ integration REP Extract, Transform, Load (ETL)… Extract, Transform, Load (ETL)… Originally… populate data warehouses Unidirectional database to database integration Sophisticated formatting and transformation rules High volume batch processing Job control and scheduling Complex projects… - metadata and transformations - poor data quality …ideal for data warehouse environment ETL Enterprise Application Integration (EAI)… Enterprise Application Integration (EAI)… Application to application integration Based on a queuing system… message oriented Real-time, bi-directional Application adapters Hub-and-spoke architecture Message transformation and content based routing Used for message switching or supporting business process management (BPM) …most advanced integration Msg Msg Queue EAI Queue API Benefits of Replication… Low complexity Low cost Real-time / near real-time No impact on applications Improve system performance Cross platform (Unix/NT) … does simple things, simple to do REP Benefits of ETL… Very efficient and effective loading into data warehouse Good means to migrate or merge data Reduces exposure to custom code (C++) Easier to ‘write’ than scripts / code / stored procedures Low administrative overhead Strong transformation capabilities Good job control and exception handling … versatile database to warehouse tool ETL Benefits of EAI… Non invasive integration Data (message) content is irrelevant Eliminate “spaghetti” point to point interfaces Ideal for high message volume switching Facilitates business process management (BPM) Can generally access many endpoints … very functional, high-powered software Msg Msg Queue EAI Queue API Yes, but … ETL now doing much of what EAI can… ETL tools now queue-enabled and Web Service-enabled. Certain application adapters Push for ETL to increase throughput with shrinking batch windows. ETL becoming “always awake” Growing number of enterprises looking at ETL to support AI in the next 18 months Web Services enabled …drive from ETL towards EAI ETL Yes, but … ETL now doing much of what EAI can… Databases Meta data & files import/ export Target adapters Administration & operations Load Runtime service meta data Transform services Transport services Extract Design manager Source adapters Databases Legacy & files applications …ETL before ETL Yes, but … ETL now doing much of what EAI can… Application Databases integration Meta data & files software XML import/ export Target adapters Administration & operations Load Update Runtime service meta data Transform Clean services Transport services Extract Capture Profile Adapter Design manager development kit Source adapters Databases Legacy Application Application Web Event & files applications packages integration & XML data software data …ETL now ETL Yes, but … process (BPM) ETL tools do not currently cater for business processes ETL not inherently designed for low volume, high throughput Store and forward This is where EAI performs well … ETL weak when it comes to process Yes, but … project complexity ETL and EAI similar in complexity ETL takes 60 – 80% of an entire BI project Difficult to: Understand source data Ensure adequate data quality EAI - important to solidify foundations … EAI and ETL equally challenging Yes, but … where do Web Services fit in? Web Services a subset of a Service Oriented Architecture (SOA) ETL and EAI form part of the bigger SOA picture Performance issues Design for purpose (database connections) … Service Architectures Yes, but…which one is right for my business? What is the ultimate goal of your integration initiative? What sort of data volumes are being integrated? What are the underlying technologies? If you try and extend you current ETL infrastructure, will you later have to replace it with an EAI solution and redo a large amount of work? What are your budgetary constraints? … spend a lot of time researching Yes, but… what are the criteria? Question Replication ETL EAI Is integration achievable at a data or database level? Is data / information moved on a: - Batch basis? - Event basis? ? - Scheduled basis? ? Do you have an existing messaging infrastructure? ? Do you have large amounts of data to be moved? Is turnaround time important? Does the information between systems form part of a business process? How important is data scrubbing or cleansing? ? Do you require meta-data management? ? Is message switching your ultimate goal? Are you hoping for a single view of the customer? ? Are you looking at a technical solution for BASEL II Compliance? ? ? Do you have multiple, diverse endpoint technologies? ? …know your requirements Summary… Replication Database to database, business continuity Extract Transform and Load (ETL) Data Warehouse initiatives Enterprise Application Integration (EAI) Business Processes spanning multiple applications. Some grey areas … reasons for each Conclusion and next steps… Integration is an overused term, meaning many things Can you get away by extending your ETL tool? Understand ultimate objective Design for purpose vs. catch-all Do some homework, and … … come and see what Sybase has in terms of Data Replication, ETL and EAI offerings …. … have the goal in mind! Questions … … Thank you