ETL for Analytics

ETL (Extraction, Transformation and Loading) processes are the most critical - and value added - components of a Data Integration for Business Intelligence infrastructure. While mostly invisible to users of the Business Intelligence (BI) platform, open source ETL processes retrieve the data from all operational systems and pre-process it for the analysis and reporting tools. The accuracy and timeliness of the entire Business Intelligence (BI) platform rely indeed on the open source ETL processes.

What is ETL?

Extraction, Transformation and Loading processes comprise multiple steps, aimed at transferring data from production applications to the open source Business Intelligence systems:

• Extraction of the data from production applications and databases (ERP, CRM, RDBMS, files, etc.)
• Transformation of this data to reconcile it across source systems, perform calculations or string parsing, enrich it with external lookup information, and also match the format required by the target system (Third Normal Form, Star Schema, Slowly Changing Dimensions, etc.)
• Loading of the resulting data from open source data integration into the various Business Intelligence (BI) applications: Data Warehouse or Enterprise Data Warehouse, Data Marts, Online Analytical Processing (OLAP) applications or “cubes”, etc.

Latency of open source ETL processes vary from batch (sometimes monthly or weekly, but most often daily), to near-real-time data integration with more frequent refreshes (every hour, every few minutes, etc.).

Challenges of ETL

There are numerous challenges to implementing efficient and reliable open source ETL processes.

• Data volumes are growing exponentially, and the open source ETL processes have to process large amounts of granular data (products sold, phone calls, banking transactions...). Some Business Intelligence (BI) systems merely get incrementally updated, whereas others require a complete reload at each iteration.
• As information systems grow in complexity, the disparity of sources is growing as well. Open source data integration and ETL processes need comprehensive connectivity to packaged applications (ERP, CRM, etc.), databases, mainframes, files, Web Services, etc.
• Business Intelligence structures and applications include data warehouses, data marts, OLAP applications - for analysis, reporting, dashboarding, scorecarding, etc. All these target structures have different data transformation requirements, and different latencies.
• Transformations involved in the open source data integration and ETL processes can be highly complex. Data needs to be aggregated, parsed, computed, statistically processed, etc. Open source BI-specific transformations are also required, such as Slowly Changing Dimensions.
• As Business Intelligence (BI) tends toward real-timeliness, data warehouses and data marts need to be refreshed more often, and the load time windows become always shorter.

Open Source Data Integration Solutions for ETL

Talend's open source data integration solutions are optimized for enterprise-grade ETL. The following features are especially critical to the design, development, execution and maintenance of open source data integration and ETL processes:

• Business-oriented process modeling that involves business stakeholders and ensures proper communication between IT and lines of business.
• Fully graphical development environment that greatly improves productivity and facilitates maintenance.
• Highly scalable and fast execution open source platform that leverages a grid of commodity hardware, and the only solution to support the dual ETL + ELT architecture.
• Broadest data integration connectivity to support all systems and get access to all the production data and easily add new source systems.
• Built-in advanced components for ETL, including string manipulations, Slowly Changing Dimensions, automatic lookup handling, bulk loads support, etc.