A visual language-based system for extraction-transformation-loading development

Abstract

SUMMARYData warehouse loading and refreshment is typically performed by means of complex software processes called extraction-transformation-loading (ETL). In this paper, we propose a system based on a suite of visual languages for mastering several aspects of the ETL development process, turning it into a visual programming task. The approach can be easily generalized and applied to other data integration contexts beyond data warehouses. It introduces two new visual languages that are used to specify the ETL process, which can also be represented by means of UML activity diagrams. In particular, the first visual language supports data manipulation activities, whereas the second one provides traceability information of attributes to highlight the impact of potential transformations on integrated schemas depending on them. Once the whole ETL process has been visually specified, the designer might invoke the automatic generation of an activity diagram representing a possible orchestration of it based on its dependencies. The designer can edit such a diagram to modify the proposed orchestration provided that changes do not alter data dependencies. The final specification can be translated into code that is executable on the data sources. Finally, the effectiveness of the proposed approach has been validated through a user study in which we have compared the effort needed to design an ETL process in our approach with respect to the one required with main visual approaches described in the literature.

Extracting-Transforming-Loading Modeling Approach for Big Data Analytics

“…In the early 2000's, the ETL has attracted significant interest of the DSS community. For the modeling purpose, precisely, we cite the following examples : Vassiliadis, Simitsis and Skiadopoulos (2002), Trujillo and Luján-Mora (2003), (Vassiliadis, Simitsis, Georgantas & Terrovitis, 2003;Vassiliadis, Simitsis, Georgantas, Terrovitis & Skiadopoulos, 2005;, El Akkaoui & Zimányi (2009), and Deufemia et al (2014). Some works such as (Simitsis, 2005), (Simitsis & Vassiliadis, 2008), (Skoutas & Simitsis, 2006;Skoutas & Simitsis, 2007a), and (Skoutas & Simitsis, 2007b) were interested in the semantic of the ETL process.…”

Visually integrating databases at conceptual level

“…To master the complexity of big data integration projects researchers are advocating conceptual-level approaches [7] [8], on the top of current logic-level tools [3][4] [6].…”