On Handling the Evolution of External Data Sources in a Data Warehouse Architecture

Abstract

A data warehouse architecture (DWA) has been developed for the purpose of integrating data from multiple heterogeneous, distributed, and autonomous external data sources (EDSs) as well as for providing means for advanced analysis of integrated data. The major components of this architecture include: an external data source (EDS) layer, and extraction-transformation-loading (ETL) layer, a data warehouse (DW) layer, and an on-line analytical processing (OLAP) layer. Methods of designing a DWA, research developments, and most of the commercially available DW technologies tacitly assumed that a DWA is static. In practice, however, a DWA requires changes among others as the result of the evolution of EDSs, changes of the real world represented in a DW, and new user requirements. Changes in the structures of EDSs impact the ETL, DW, and OLAP layers. Since such changes are frequent, developing a technology for handling them automatically or semi-automatically in a DWA is of high practical importance. This chapter discusses challenges in designing, building, and managing a DWA that supports the evolution of structures of EDSs, evolution of an ETL layer, and evolution of a DW. The challenges and their solutions presented here are based on an experience of building a prototype Evolving-ETL and a prototype Multiversion Data Warehouse (MVDW). In details, this chapter presents the following issues: the concept of the MVDW, an approach to querying the MVDW, an approach to handling the evolution of an ETL layer, a technique for sharing data between multiple DW versions, and two index structures for the MVDW.

UML-Based Data Warehouse Design Using Temporal Dimensional Modelling

“…Besides,TDWhaveseveralissuesowingtoadditionoftemporalityintraditionalDW,timebased queryingofMDdatabase,impropernessinaggregationofdataandstructuralvariations,temporal viewrealizationfromnon-temporaldimensions,developmentofaMDstructure,implementation propertiesofatemporalschemaandsoon.GolfarelliandRizzi(2009)reviewedthestate-of-art temporaldatawarehousemodellingtechniquesandclassifiedtheissuesunderthreedistinctcategories like managing deviations in DW, managing data variations in data mart and managing schema alterationsindatamart.Theyidentifiedthefollowinglimitations:(a)time-dependentapproaches intendtodescribetheschemaandhandlingthevariationsofTDWbutdoesnotoffercomparative analysiswithexistingtechniques (Wrembel,2011;Manousisetal.,2015),(b)thetechniquesaretoo oldanddonotconsiderthelatesttrendsinthedomain,and(c)donotaffordcomparisonontemporal supportincurrenttechniquesbasedondifferenttimestamps. Wrembel(2009)alsotermedasubsetofmethodscalledmulti-versionedDWsthatsupportsa programminglanguageforquerying,sharingandindexingdatainDW.Inanotherwork, Wrembel (2011)discussedthechallengesthatarefacedinthedesign,modellingandorganizationofgrowing datasourcesaswellasdevelopmentofETLlayerunderUMLschema.Nevertheless,noneofthese techniquesprovideacomparisononTDWwithexistingapproaches. (Lapuraetal.,2018)exposeda financialsupportmultidimensionalDWmodelthatdividestime,financialunit,accounts,temporal dimensions that are regularly updated with transaction data obtained from financial database of auniversity.Forvisualdescription,adatavisualizationtoolisincorporatedinthewebportalof university,andallowedtousabilitytestingonlybytheterminalusers.Thistoolisreportedasauseful onebymostoftherespondents.Moreover,thetoolistestedwithqueryanalysiswherethequery responsetimeisgreatlydecreasedbyanaverageofabout50%.Thoughseveralresearchworkshave beenpresentedbasedonreal-timedataupdation (Jörg&Dessloch,2009,Vassiliadis&Simitsis, 2009,onlyalittleattentionhasbeenpaidtobringforthappropriatedimensionalmodelling.Latest datawarehousesystemsaredesignedtoofferabundantfacilitiestonumberofdatausers.Thatis,users canretrievereportsandsummariesappropriatetothem,orexaminedatawithspecificvisualization tools (Guldenetal.,2019).Moreover,usersneeddataaccessatdifferentintervalslikeinfrequently, frequently,atregulartimeperiods,rapidly,immediatelyandpredictably.Thus,thenecessityoftimedependentbusinessgoalsareincreasingday-by-daytoattaingrowthorienteddevelopmentinthe competitiveinfrastructure (Dinhetal.,2020).…”

E-ETL: Framework for Managing Evolving ETL Workflows

“…Since they are not directly related to the topic of this paper, they will not be described here. An overview of research problems and approaches can be found in [5,6].…”

Temporal and Evolving Data Warehouse Design

“…For instance, Golfarelli and Rizzi [9] discuss the handling of schema and data level changes in data warehouses and data marts, design of TDWs, and querying temporal data. However, they do not include the recent efforts such as [23,24] as well as a comparative analysis of these approaches. Wrembel [22] also focuses on a subset of approaches, called Multiversioned Data Warehouses (MVDWs).…”

“…Additionally, it includes a language for querying a MVDW, as well as the detailed structure for sharing data and indexing data in a MVDW. Wrembel [23] only discusses the challenges that arise in the design, construction, and management of evolving external data sources as well as evolution of an ETL layer. All these approaches do not provide a comparison of the temporal support provided by the existing schemes.…”