WIXLIB

Journal of Theoretical and Applied Information Technology20th August 2013. Vol. 54 No.2 2005 - 2013 JATIT & LLS. All rights reserved.ISSN: 1992-8645 www.jatit.orgThe richness of offered features.The parallelism or the partitioning usefulfor the treatment of massive data.In summary, ten open source ETLs arepresented and this study concludes that the mostnotables are Talend and Kettle because of theirlarge users’ community, their literature, theirfeatures and their inclusion in BI suites. Finally, letnote that another version of this study is availablein [12].In the following we suggest to look closelyat one of these tools. More precisely, we will limitto Talend tool.Talend is an open source, offering a widerange of middleware solutions that meet the needsof data management and application integration[13]. Among these solutions there are two solutionsfor data integration: Talend Open Studio for DataIntegrationand Talend Enterprise DataIntegration. Respectively, they are a freedevelopment open source tool and non freedevelopment tool that comes with advanceddeployment and management features. Talend dataintegration solution is based on the main followingmodules:1.2.3.Modeler, a tool for creating diagrams. Tothis end, it offers a range of componentsindependent of any technology.Metadata Manager that is a repository forstoring and managing metadata.Job Designer, graphical developmentenvironment for ETL jobs creation. Thistool in turn provides a rich palette ofcomponentsfordataextraction,transformation and loading.From the point of view of a developer, theimplementation of a ETL process with Talend OpenStudio Data Integration consists on the insertion ofcomponents from the palette offered by JobDesigner (more than 400 components). From thepoint of view of a designer, the use of Talendconsists on modeling with Modeler module. Butthe relationship between these two levels is notavailable. In other words, transition from ETLdiagrams built with Modeler to ETL job writtenwith Job Designer is not offered.In next section, we see another type ofETL tools, those coming from research world.E-ISSN: 1817-31952.3 Framework and Tools from ResearchIn this section, we will see some projects,from academic world, dealing with the problem ofETL. These projects are: SIRIUS, ARKTOS,DWPP and PYGRAMETL.SIRIUS (Supporting the IncrementalRefreshment of Information Warehouses) is aproject developed at information technologydepartment in Zurich University. SIRIUS, [14]looks at the refreshment of data warehouses. Itdevelops an approach metadata oriented that allowsthe modeling and execution of ETL processes. It isbased on SIRIUS Meta model component thatrepresents metadata describing the necessaryoperators or features for implementing ETLprocesses. In other words, SIRIUS providesfunctions to describe the sources, targetsdescription and the mapping between these twoparts. At the end, this description leads to thegeneration of global schema. At execution level,SIRIUS generates Java code ready for executionafter a successful compilation. Finally, SIRIUSincludes techniques for detecting changes in theoperational sources (databases exactly) besidessupervision support, for the execution of definedETL processes.ARKTOS is another framework thatfocuses on the modeling and execution of ETLprocesses. Indeed, ARKTOS provides primitives tocapture ETL tasks frequently used [15]. Moreexactly, to describe a certain ETL process, thisframework offers three ways that are GUI and twolanguages XADL (XML variant) and SADL (SQLlike language). An extension of this work isavailable in [16] where authors discuss the metamodel of their prototype. The basic element inARKTOS is called an activity whose logic isdescribed by a SQL query. At execution level,ARKTOS manages the execution of each activityand associate it with the action to take when anerror occurs. Six types of errors are taken intoaccount. Firstly, they are 1) violation of theprimary key, 2) violation of the uniqueness and 3)violation of reference. These three types are specialcases of integrity constraints violation. The othertype of error is 4) NULL EXISTENCE for theelimination of missing values. Finally, the tworemaining errors types are 5) FIELD MISMATCHand 6) FORMAT MISMATCH related respectivelyto domain errors and data format errors.PYGRAMETL(Pythonbasedframework) is a programming framework for ETL222

Journal of Theoretical and Applied Information Technology20th August 2013. Vol. 54 No.2 2005 - 2013 JATIT & LLS. All rights reserved.ISSN: 1992-8645www.jatit.orgprocesses [17]. In this work, the authors attempt toprove that ETL development based on a graphicaltool are less efficient than conventional ETLdevelopments (code editing). In other words,instead of using a graphical palette of components,and then proceed by inserting and personalize theelements of this palette, it is more appropriate tomake codes and create scripts via a programminglanguage. In this perspective, PYGRAMETLsuggests coding with Python language. Also itoffers a set of functions most commonly used in thecontext of populating data warehouses such asfiltering data and feeding slowly changingdimensions. Finally, the authors of PYGRAMETLpresent, as illustration, a comparison betweenPYGRAMETL (their tool) and PENTAHOO, anopen source ETL tool.Another interesting work that touches theETL is available in [18]. This work which focuseson ETL performance that is a fundamental aspectamong others of ETL, presents DWPP (DataWarehouse Population Platform). DWPP is a set ofmodules designed to solve the typical problems thatoccur in any ETL project. DWPP is not a tool but itis a platform. Exactly, it is C functions libraryshared under UNIX operating system for theimplementation of ETL processes. Consequently,DWPP provides a set of useful features for datamanipulation. These features are structured in twolevels. Indeed, the first level is based on thefeatures of operating system and those of the OracleDBMS (the chosen target for DWPP) while thesecond level regroups developed features which arespecific and useful in ETL treatment. Finally, asthis work comes from a feedback on thedevelopment and deployment of DWPP basedlarge-scale ETL projects, it does not address thevariety of sources nor targets. Indeed, DWPP islimited to flat files as source and Oracle DBMS astarget. Another work associated to DWPP isavailable in [6] where authors give more details onthe functional and technical architecture of theirsolutions. In addition, the authors discuss some ofthe factors impacting the performance of ETLprocesses. Thus, the authors address the physicalimplementation of the database with focus on datapartitioning as well as pipelining and parallelism.These aspects are addresses using UNIX operatingsystem functionalities.During this section we have presentedopen source ETL tools as commercial tools, alongwith some prototype from academic world. In nextE-ISSN: 1817-3195section, we deal with ETL design and modeling.We present related works to these issues.3.ETL MODELING AND DESIGNResearch in data warehouse field isdominated by the DW design and DW modeling.ETL field is not an exception to this rule. Indeed, inthe literature one can note several research effortsthat treat DW population performed by ETLprocesses. (ETL) are areas with high added valuelabeled costly and risky. In addition, softwareengineering requires that any project is doomed toswitch to maintenance mode. For these reasons, it isessential to overcome the ETL modeling phase withelegance in order to produce simple models andunderstandable. Finally, as noted by Booch et al in[19], we model a system in order to: Express its structure and behavior.Understand the system.View and control the system.Manage the risk.In the following we go through researchworks associated with ETL design.3.1 Meta-models based Works on ETL DesignDuring DOLAP 2OO2 conference,Vassiliadis et al [20] present an attempt toconceptual modeling of ETL processes. The authorsbased their argument on the following two points.At the beginning of a BI project, the designer needsto:1.2.Analyze the structure and the content ofsources.Define mapping rules between sources andtargets.The proposed model, based on metamodel, provides a graphical notation to meet thisneed. Also, a range of activities commonly used bythe designer is introduced.The model in question continues bydefining two concepts: the Concept andRelationship-Provider. Respectively, it is a sourceor a target with their attributes and relationshipsbetween attributes (fields of source or target).Transformations are dissociated with mapping rules(Relationship-Provider). Indeed, in order totransform attributes such as removing spaces orconcatenating attributes, Provider Relationship uses223

Journal of Theoretical and Applied Information Technology20th August 2013. Vol. 54 No.2 2005 - 2013 JATIT & LLS. All rights reserved.ISSN: 1992-8645www.jatit.organ extra node (element of the model). Ignoringwhich source to prioritize to extract data, the modelintroduces candidate relationship to designate allsources likely to participate in DW population. Theselected source is denoted active relationship.The authors complement their model viaan extensive paper [21] by proposing a method forthe design of ETL processes. Indeed, in this work,the authors expose a method to establish a mappingbetween the sources and targets of ETL process.This method is spread over four steps:1.2.3.4.Identification of sourcesDistinction between candidates’ sourcesand active sources.Attributes mapping.Annotation of diagram (conceptual model)with execution constraints.Works around this model are reinforced by anattempt to transition from conceptual model tological model. This is the subject of [22] paperwhere authors define a list of steps to follow toinsure this transition. In addition, this workproposes an algorithm that groups transformationsand controls (at conceptual level) into stages thatare logical activities. Finally, a semi-automaticmethod determining the order execution of logicalactivities is defined too.Another work around ETL presentsKANTARA, a framework for modeling andmanaging ETL processes [4]. This work exposesdifferent participants that an ETL project involvesparticularly designer and developer. After theanalysis of interaction between main participants,authors conclude that designer needs helpful tool,which will makes easy the design and maintenanceof ETL processes. In response, authors introducenew graphical notation based on a meta-model. Itconsists mainly on three core components whichare, Extract, Transform and Load components.These components are chosen according to ETLsteps. Besides, each component manages a set ofspecific meta-data close to its associate step. Thiswork is consolidated with another work presentinga method for modeling and organizing ETLprocesses [23]. Authors start by showing functionalmodules that should be distinguished in each ETLprocess. This leads to distinguish several modules,especially Reject Management module for which aset of metadata to manage are defined. Theproposed approach takes five inputs (namelymapping rules, conforming rules, cleaning rules,and specific rules) and produces a conceptualE-ISSN: 1817-3195model of an ETL processes using a graphicalnotation presented previously.3.2 UML Based WorksIn 2003 Trujillo [24] proposes a newapproach, UML based for the design of ETLprocesses. Finding that the model of Vassiliadis[20] is based on ad-hoc method, the authors attemptto simplify their model and to base it on a standardtool. In this model, an ETL process is considered asclass diagram. More precisely, a basic ETL activitywhich can be seen as a component is associatedwith a UML class and the interconnection betweenclasses is defined by UML dependencies. Finally,the authors have decided to restrict their model toten types of popular ETL activities such asAggregation, Conversion, Filter and Join, which inturn are associated to graphical icons.In 2009 (DOLAP 2009), Munoz et almodernize this model through a new publication[25] dealing with the automatic generation of codefor ETL processes from their conceptual models. Infact, the authors have presented a solution orientedMDA. It is structured as follows: For PIM (Platform Independent Model)layer, which corresponds to conceptuallevel, ETL models are designed usingUML formalism, more precisely the resultof the previous effort.For PSM (Platform Specific Model) layerwhich corresponds to the logical level, theplatform chosen is Oracle platform.For the Transformation layer that allowsthe transition from PIM model to PSMmodel is made with QVT (Query ViewTransformation)language.Thesetransformations can bind PIM meta-modelelements to PSM meta-model elements.Another research team presents in [26]another research effort about ETL and UML based.But this work is restricted to extraction phaseomitting transformations and loading phases. Thus,this work presents an approach object-oriented formodeling extraction phase of an ETL process usingUML 2. To this end, authors present and illustratethe mechanism of this phase as three diagrams.These diagrams are class diagram, sequencediagram and use case diagram of extraction phase.Finally, six classes which are data staging area,data sources, wrappers, monitors, integrator andsource identifier are shown. These classes are usedand in above diagrams.224

Journal of Theoretical and Applied Information Technology20th August 2013. Vol. 54 No.2 2005 - 2013 JATIT & LLS. All rights reserved.ISSN: 1992-8645www.jatit.org3.3 Web Technologies Based WorksThe advent of the web has disruptedinformation systems. ETL in turn are affected bythis wave. Indeed, semantic web technologies areused in the development of data warehousesrefreshment processes. Thus, in 2006 Skoutas et alpresent an ontology based approach for the designof ETL processes [27]. Applied to a given domainwhose concepts are identified, OWL (web ontologylanguage) is used to construct the domain ontologyin question. Then the OWL ontology constructed isexploited to annotate and mark schemas of bothsources and DW (target). Finally, a reasoningtechnique is put into action to induce connectionsand conflicts between the two extremes (betweensource and DW). This approach is semi-automaticand allows getting a part of necessary elements forthe design of ETL. Namely, they are mapping rulesand transformations between sources and targetattributes. However, this work suffers from twofollowing drawbacks:1.2.The scope of the data is limited torelational sources.Ontology construction is not efficient.Therefore, the same authors enhance theirproposal in 2007 via an extension of their work[28]. The scope of data handled is expanded toinclude structured data and unstructured data.Finally, a prototype of the solution is implementedin Java.It is clear that the contribution of thisapproach is relevant, but it is still insufficient.Indeed, a conceptual model is not limited to themapping rules, but there is also (among other) theData Flow to manage which consists on activitiesorchestration and arrangement. Finally, let note thatresults accuracy of applying this approach is closelyrelated to the degree of matching between ontologyand schemas of sources and DW. Furthermore, inreality, the schemas of sources are not expressive ornon-existent [3].Another approach based on webtechnologies, more precisely on marriage betweenBPMN (Business Process Modeling Notation) andBPEL (Business Process Execution Language) ispresented in [29]. This work aims to align to MDAstandard. Indeed, it proposes to use BPMN for thePIM layer and to use BPEL for PSM layer.Nevertheless transformation layer is not welldeveloped in spite of the transition from BPMN toE-ISSN: 1817-3195BPEL is mentioned. In fact, this work considersthat the mapping between BPMN and BPEL isacquired and provided by multiple tools. Finally,this effort provides an extensible palette of usefulfeatures for the ETL design using BPMN notation.Another interesting work related to the design ofETL based on RDF (Resource DescriptionFramework) and OWL, two web technologies, isdetailed in [30]. This work has the advantage ofpresenting a method over several steps rangingfrom the creation of models for ROLAP cubes untilthe creation of the ETL layer. The basic idea behindthis work is to convert the data source to a RDF fileformat that is consistent with OLAP ontologywhich is constructed in turn. Then target tables arepopulated with data extracted via queries generatedover OLAP ontology.The scientific community has enriched the field ofmodeling ETL with different approaches presentedabove. These proposals differ in the formalism andthe technology used. But they have the samedrawback that is the lack of support andapproaches to change management in ETL. In nextsection, we cover this issue.4.MAINTENANCE OF ETL PROCESSESETL process can be subject of changes forseveral reasons. For instance, data sources changes,new requirements, fixing bugs etc. When changeshappen, analyzing the impact of change ismandatory to avoid errors and mitigate the risk ofbreaking existent treatments.Generally, change is neglected although it is afundamental aspect of information systems anddatabase [31]. Often, the focus is on building andrunning systems. Less attention is paid to the wayof making easy the management of change insystems [32]. As a consequence, without a helpfultool and an effective approach for changemanagement, the cost of maintenance task will behigh. Particularly for ETL processes, previouslyjudged expensive and costly.Research community catches this need andsupplies, in response, few solutions. The one canstarts with a general entry point to change issue in[31] that is a report on evolutions and data changesmanagement. Indeed, authors summarize theproblems associated with this issue as well as acategorization of these problems. Finally, theauthors discuss the change issue according to six225

Journal of Theoretical and Applied Information Technology20th August 2013. Vol. 54 No.2 2005 - 2013 JATIT & LLS. All rights reserved.ISSN: 1992-8645www.jatit.orgaspects which are: What, Why, Where, When, Whoand How.Regarding data warehouse layer, change canoccur at two levels, either in schemas or in datastored from the first load of data warehouse.Managing data evolution, contrarily to schemaevolution, over time is a

In Section 2, we review ETL tools with examples of commercial tools as open source tools. 2.1 Commercial ETL Tools A variety of commercial tools overwhelms the ETL market which is a promising market. A study [7] conducted by TDWI, identifies a list of indicators and criteria for their comparison and evaluation. Each ETLcommercial tool adopts its