ETL for Analytics

ETL (Extraction, Transformation and Loading) processes are the most critical – and value added – components of a Business Intelligence infrastructure. While mostly invisible to users of the BI platform, 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 BI platform rely indeed on the ETL processes.

What is ETL?

Extraction, Transformation and Loading processes comprise multiple steps, aimed at transferring data from production applications to the 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 into the various BI applications: Data Warehouse or Enterprise Data Warehouse, Data Marts, Online Analytical Processing (OLAP) applications or “cubes”, etc.

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

Challenges of ETL

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

• Data volumes are growing exponentially, and the ETL processes have to process large amounts of granular data (products sold, phone calls, banking transactions…). Some 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. 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 ETL processes can be highly complex. Data needs to be aggregated, parsed, computed, statistically processed, etc. BI-specific transformations are also required, such as Slowly Changing Dimensions.
• As 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 data integration solutions are optimized for enterprise-grade ETL. The following features are especially critical to the design, development, execution and maintenance of 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 platform that leverages a grid of commodity hardware, and the only solution to support the dual ETL + ELT architecture.
• Broadest 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.