Extract-Transform-Load Processes Research Articles

An efficient hybrid optimization of ETL process in data warehouse of cloud architecture

In big data, analysis data is collected from different sources in various formats, transforming into the aspect of cleansing the data, customization, and loading it into a Data Warehouse. Extracting data in other formats and transforming it to the required format requires transformation algorithms. This transformation stage has redundancy issues and is stored across any location in the data warehouse, which increases computation costs. The main issues in big data ETL are handling high-dimensional data and maintaining similar data for effective data warehouse usage. Therefore, Extract, Transform, Load (ETL) plays a vital role in extracting meaningful information from the data warehouse and trying to retain the users. This paper proposes hybrid optimization of Swarm Intelligence with a tabu search algorithm for handling big data in a cloud-based architecture-based ETL process. This proposed work overcomes many issues related to complex data storage and retrieval in the data warehouse. Swarm Intelligence algorithms can overcome problems like high dimensional data, dynamical change of huge data and cost optimization in the transformation stage. In this work for the swarm intelligence algorithm, a Grey-Wolf Optimizer (GWO) is implemented to reduce the high dimensionality of data. Tabu Search (TS) is used for clustering the relevant data as a group. Clustering means the segregation of relevant data accurately from the data warehouse. The cluster size in the ETL process can be optimized by the proposed work of (GWO-TS). Therefore, the huge data in the warehouse can be processed within an expected latency.

A Data Warehouse Architecture Proposal and ETL Analysis, Case study of an Albanian Banking System

The increasing phenomenon of information overload is a direct result of the ongoing trend to reduce the cost of data distribution while the development of data processing platforms is not fast enough. Therefore, sending packets of data is not a big issue, but processing an increasing amount of data is a big challenge.
 Data Warehouse is a concept born in the late ‘80s and has been improved decade after decade with the growth of technology. Despite many application areas, the data warehouse concept is still new and unknown to Albanian businesses and companies with a large amount of data to store and analyze.
 This paper aims to provide an in-depth analysis of the concept of a data warehouse, the different architectures applied, and the Extract, Transform Load (ETL) process. The ETL process as a concept consists of extracting the data from a source, transforming them into different formats or dimensions, and then loading them somewhere to be checked for analysis or other purposes.
 In this paper, we can find different definitions of the data warehouse, where it can be implemented, the different architectures according to the need of the users, and how the ETL process works. The last chapter implements a data warehouse for the banking system seen from a Management Information Systems perspective followed by an application of a step-by-step ETL procedure.

Overcoming traditional ETL systems architectural problems using a service‐oriented approach

AbstractDeveloping analytical systems imposes several challenges related not only to the amount and heterogeneity of the involved data but also to the constant need to readapt and evolve to overcome new business challenges. Data are a determinant factor in the success of analytical and decision‐making applications, being its nature, availability, and quality, crucial aspects for planning and structuring populating analytical systems. Today's users are more demanding, requiring adaptable and flexible analytical applications, which impose serious challenges on extract‐transform‐load (ETL) systems design and development for ensuring flexible and robust data populating services, operating 24/7, and managing and processing large volumes of data. Thus, we should design and implement ETL processes using innovative and up‐to‐date approaches, having real application evidence. In this paper, we present a service‐oriented implementation for ETL design and development. We mapped and implemented some of the most conventional ETL processes in a service‐oriented architecture, to demonstrate the application and benefits that this kind of approach will provide to ETL systems project development.

Context-aware Big Data Quality Assessment: A Scoping Review

The term data quality refers to measuring the fitness of data regarding the intended usage. Poor data quality leads to inadequate, inconsistent, and erroneous decisions that could escalate the computational cost, cause a decline in profits, and cause customer churn. Thus, data quality is crucial for researchers and industry practitioners. Different factors drive the assessment of data quality. Data context is deemed one of the key factors due to the contextual diversity of real-world use cases of various entities such as people and organizations. Data used in a specific context (e.g., an organization policy) may need to be more efficacious for another context. Hence, implementing a data quality assessment solution in different contexts is challenging. Traditional technologies for data quality assessment reached the pinnacle of maturity. Existing solutions can solve most of the quality issues. The data context in these solutions is defined as validation rules applied within the ETL (extract, transform, load) process, i.e., the data warehousing process. In contrast to traditional data quality management, it is impossible to specify all the data semantics beforehand for big data. We need context-aware data quality rules to detect semantic errors in a massive amount of heterogeneous data generated at high speed. While many researchers tackle the quality issues of big data, they define the data context from a specific standpoint. Although data quality is a longstanding research issue in academia and industries, it remains an open issue, especially with the advent of big data, which has fostered the challenge of data quality assessment more than ever. This article provides a scoping review to study the existing context-aware data quality assessment solutions, starting with the existing big data quality solutions in general and then covering context-aware solutions. The strength and weaknesses of such solutions are outlined and discussed. The survey showed that none of the existing data quality assessment solutions could guarantee context awareness with the ability to handle big data. Notably, each solution dealt only with a partial view of the context. We compared the existing quality models and solutions to reach a comprehensive view covering the aspects of context awareness when assessing data quality. This led us to a set of recommendations framed in a methodological framework shaping the design and implementation of any context-aware data quality service for big data. Open challenges are then identified and discussed.

An Extract-Transform-Load Process Design for the Incremental Loading of German Real-World Data Based on FHIR and OMOP CDM: Algorithm Development and Validation.

In the Medical Informatics in Research and Care in University Medicine (MIRACUM) consortium, an IT-based clinical trial recruitment support system was developed based on the Observational Medical Outcomes Partnership (OMOP) Common Data Model (CDM). Currently, OMOP CDM is populated with German Fast Healthcare Interoperability Resources (FHIR) using an Extract-Transform-Load (ETL) process, which was designed as a bulk load. However, the computational effort that comes with an everyday full load is not efficient for daily recruitment. The aim of this study is to extend our existing ETL process with the option of incremental loading to efficiently support daily updated data. Based on our existing bulk ETL process, we performed an analysis to determine the requirements of incremental loading. Furthermore, a literature review was conducted to identify adaptable approaches. Based on this, we implemented three methods to integrate incremental loading into our ETL process. Lastly, a test suite was defined to evaluate the incremental loading for data correctness and performance compared to bulk loading. The resulting ETL process supports bulk and incremental loading. Performance tests show that the incremental load took 87.5% less execution time than the bulk load (2.12 min compared to 17.07 min) related to changes of 1 day, while no data differences occurred in OMOP CDM. Since incremental loading is more efficient than a daily bulk load and both loading options result in the same amount of data, we recommend using bulk load for an initial load and switching to incremental load for daily updates. The resulting incremental ETL logic can be applied internationally since it is not restricted to German FHIR profiles.

Rapid identification of chronic kidney disease in electronic health record database using computable phenotype combining a common data model.

Rapid identification of chronic kidney disease in electronic health record database using computable phenotype combining a common data model.

AN IMPACT ANALYSIS OF EXTRACT TRANSFORM LOAD PROCESS FOR MAINTAINING THE SYSTEM OF DATA WAREHOUSE

The Extract Transform Load (ETL) process involves extracting data from database sources, transforming them into a suitable form for research analysis, and then loading it into a data warehouse (DW) to support effective decision-support implementation. To maintain the target of the data warehouse, several issues are discussed in the DW life cycle, ETL processes, and impact analysis for maintaining the DW. This research focuses on the issue high frequency of data changes makes ETL processes difficult to propagate the data changes and to maintain the changes history in the DW life cycle. Therefore, the focus issues of this research are identifying factors for frequent data changes that occur in data sources and the DW structure that need to be modified by performing impact analysis. The general factors of data changes in DW were identified by questionnaire from 41 respondents, and the factor of impact analysis was evaluated using the statistic test method called Kruskal Wallis H Test to make a comparison between the impact analysis factor and the category of users. The aims of this research are to perform the impact analysis of the DW process in order to maintain the DW system and to achieve DW with the right requirements definition. In addition, this will help users working with the DW to understand the elements of impact analysis in DW, especially on how to ensure the DW process runs efficiently and successfully. Therefore, the database administrators, data analysts, and DW developers can utilize these research findings as a guideline to deal with the data changes in the DW process.

Big Data in Laboratory Medicine—FAIR Quality for AI?

Laboratory medicine is a digital science. Every large hospital produces a wealth of data each day—from simple numerical results from, e.g., sodium measurements to highly complex output of “-omics” analyses, as well as quality control results and metadata. Processing, connecting, storing, and ordering extensive parts of these individual data requires Big Data techniques. Whereas novel technologies such as artificial intelligence and machine learning have exciting application for the augmentation of laboratory medicine, the Big Data concept remains fundamental for any sophisticated data analysis in large databases. To make laboratory medicine data optimally usable for clinical and research purposes, they need to be FAIR: findable, accessible, interoperable, and reusable. This can be achieved, for example, by automated recording, connection of devices, efficient ETL (Extract, Transform, Load) processes, careful data governance, and modern data security solutions. Enriched with clinical data, laboratory medicine data allow a gain in pathophysiological insights, can improve patient care, or can be used to develop reference intervals for diagnostic purposes. Nevertheless, Big Data in laboratory medicine do not come without challenges: the growing number of analyses and data derived from them is a demanding task to be taken care of. Laboratory medicine experts are and will be needed to drive this development, take an active role in the ongoing digitalization, and provide guidance for their clinical colleagues engaging with the laboratory data in research.

An embedding driven approach to automatically detect identifiers and references in document stores

NoSQL stores have become ubiquitous since they offer a new cost-effective and schema-free system. Although NoSQL systems are widely accepted today, Business Intelligence & Analytics (BI&A) wields relational data sources. Exploiting schema-free data for analytical purposes is a challenge since it requires reviewing all the BI&A phases, particularly the Extract-Transform-Load (ETL) process, to fit big data sources as document stores. In the ETL process, the join of several collections, with a lack of explicitly known join fields is a significant dare. Detecting these fields manually is time and effort-consuming and infeasible in large-scale datasets. In this paper, we study the problem of discovering join fields automatically. We introduce an algorithm that aims to automatically detect both identifiers and references on several document stores. The modus operandi of our approach underscores three core stages: (i) global schema extraction; (ii) discovery of candidate identifiers; and (iii) identifying candidate pairs of identifier and reference fields. We use scoring features and pruning rules to discover true candidate identifiers from many initial ones efficiently. To find candidate pairs between several document stores, we put into practice node2vec as a graph embedding technique, which yields significant advantages while using syntactic and semantic similarity measures for pruning pointless candidates. Finally, we report our experimental findings that show encouraging results.

Utilization Extract, Transform, Load For Developing Data Warehouse In Education Using Pentaho Data Integration

The utilization of data warehouses in various fields is an absolute necessity. A data warehouse is a database that contains large amounts of data that aims to help organizations, fields, and institutions specifically for decision making. Data warehouses can produce important information in the future. Loading data from various sources and processed through an ETL (Extract, Transform, Load) process that displays data consistently is the basis for creating a data warehouse architecture. The development of a data warehouse in education will provide significant benefits for the progress of education. Integration of data and processing results stored in the data warehouse can be the basis for evaluating better planning. Development of data warehouse adopt the multidimensional modelling method which consists of four stages: select the business process, declare the grain, select dimensions, and identify facts. This stage produces a data warehouse architecture and influences and contributes to the advanced information technology in education.

Extract transform load process in banking reporting system

Banks must maintain, calculate, and monitor liquidity using the Liquidity Coverage Ratio (LCR) indicator. In Indonesia, they reported daily, monthly, or quarterly online through a paper template prepared by the Financial Services Authority (OJK). This reporting must accurate and on time or be subject to penalties. For banks that are still processing LCR semi-automatically, this reporting system is an obstacle that they continue to face and resolve. This article discusses the automation process method developed using the concept of Extract Transform Load (ETL), with a waterfall software development model, so that daily reports are generated automatically. This article proposed a methodology to anticipate problems in integrating banking with regulators by applying one of the Basel III frameworks, primarily based on Indonesia's case studies. The finding of the research is a method for the LCR report process through ETL. The proposed ETL method in this research had proven used success in processing LCR in the banking industry. This method is a solution and recommendation for banks in making reports based on Basel III to complete LCR reporting through the ETL method.•Maintain, calculate, and monitor liquidity using the Liquidity Coverage Ratio (LCR) indicator in Banking .•Automation process method developed using the concept of Extract Transform Load (ETL).•Recommendation for Banks to complete LCR reporting through the ETL method).

Personel İş Zekası Sistemi ve Veri Madenciliği ile Personel Memnuniyetinin Ölçülmesi

Veri madenciliği uygulamaları ve iş zekası sistemleri verileri anlamlandırarak bilgiye dönüştürülmesinde ve kuruluşlara karar verme süreçlerinde yardımcı olmak için kullanılır. Çalışmada bir devlet kurumunun personel verileri üzerinde iş zekası ve veri madenciliği süreçleri gerçekleştirilerek verilerin yönetimini sağlamak ve dinamik analizler ile stratejik öngörülerin yapılabilmesi amaçlanmıştır. İş zekası süreçlerinin uygulanmasında Microsoft iş zekası araçlarından SSIS (SQL Server Integration Service), SSAS (SQL Server Analysis Service) ve Power BI kullanılmıştır. Öncelikle veri yapısı modellendirilmiş olup ETL (Extract-Transform-Load) sürecinden geçirilerek anlamlı hale getirilen veri, veri ambarına SSIS programı ile aktarılmıştır. Daha sonra SSAS programı ile OLAP veri küpü oluşturulmuştur. Son olarak da OLAP küpü veri kaynağı olarak belirlenip zengin görsel araçlara sahip olan ve veri analizini çok daha etkili hale getiren Power BI iş zekası aracı kullanılarak çeşitli görsellerle veri analizi gerçekleştirilmiştir. Böylece, karar vericilerin veri analizini etkin bir şekilde yapabilecekleri bir karar destek sistemi geliştirilmiştir. Çalışmada kümeleme analizi yöntemlerinden biri olan K-Means algoritması ve birliktelik kurallarından kurallarından Apriori algoritması ile bir devlet kurumundaki İç Kontrol Sistemi anketi verileri kullanılarak personel memnuniyet analizi yapılmıştır. Veri madenciliği aracı olarak ise içerisinde bulundurduğu bir çok algoritma sayesinde verilerden bilgi çıkarımı yapılabilmesine olanak sağlayan ve açık kaynak kodlu bir araç olması sebebi ile WEKA kullanılmıştır. Kümeleme analizi sonucunda oluşan farklı personel grupları detaylı incelenerek bu gruplara nasıl yaklaşılması gerektiği ve nasıl hitap edilmesi gerektiği belirlenmiştir. Her grubun memnuniyet durumunun farklılaşan noktaları belirlenerek, stratejiler ve uygulama faaliyetleri bu grubun ihtiyacına göre planlanmıştır. Apriori algoritması, personelin İç Kontrol Sistemi anket sorularına vermiş olduğu cevaplar arasında anlamlı ilişkileri bulmak, bu ilişkilerden faydalanarak personelin çalıştığı birimden memnuniyetini karşılaştırarak anlamlı sonuçlar elde etmek ve çalışılan birimden memnun olmama nedenlerini keşfetmek için kullanılmıştır. Böylece İç Kontrol Sistemi anketi verilerinden anlamlı ve yararlı bilgiler elde etmek ve bu bilgiler ışığında personel karar destek planlama faaliyetlerinde destek olmak amaçlanmıştır.

PENERAPAN TEKNOLOGI DATAWAREHOUSE NOSQL DAN BUSINESS INTELLIGENCE UNTUK ANALISA TRANSAKSI PENJUALAN

Business process are increasingly developing, causing data generated will be even greater. The application of datawarehouse technology and MongoDB as one of the NoSQL technologies can solve large historical data processing problems. In addition, business intelligence technology that is able to visualize data in into graphics and tables will make it easier for stakeholders to analyze sales transactions. The process for transforming transaction data into a datawarehouse is the Extract Transform Load (ETL) process. The data generated from the ETL process is quality data and is really needed for analysis. Black box testing results and data validity indicate the system can be accepted and can produce the right information for decision support.

From Big Data to business analytics: The case study of churn prediction

The success of companies hugely depends on how well they can analyze the available data and extract meaningful knowledge. The Extract-Transform-Load (ETL) process is instrumental in accomplishing these goals, but requires significant effort, especially for Big Data. Previous works have failed to formalize, integrate, and evaluate the ETL process for Big Data problems in a scalable and cost-effective way. In this paper, we propose a cloud-based ETL framework for data fusion and aggregation from a variety of sources. Next, we define three scenarios regarding data aggregation during ETL: (i) ETL with no aggregation; (ii) aggregation based on predefined columns or time intervals; and (iii) aggregation within single user sessions spanning over arbitrary time intervals. The third scenario is very valuable in the context of feature engineering, making it possible to define features as “the time since the last occurrence of event X”. The scalability was evaluated on Amazon AWS Hadoop clusters by processing user logs collected with Kinesis streams with datasets ranging from 30 GB to 2.6 TB. The business value of the architecture was demonstrated with applications in churn prediction, service-outage prediction, fraud detection, and more generally — decision support and recommendation systems. In the churn prediction case, we showed that over 98% of churners could be detected, while identifying the individual reason. This allowed support and sales teams to perform targeted retention measures.

ARRANGEMENT AND MODULATION OF ETL PROCESS IN THE STORAGE


 
 
 
 Data warehouse (DW) is the basis of systems for operational data analysis (OLAP-Online Analytical Processing). Data extracted from different sources transforms and load in DW. Proper organization of this process, which is called ETL (Extract, Transform, Load) has important significance in creation of DW and analytical data processing. Forms of organization, methods of realization and modeling of ETL processes are considered in this paper.
 
 
 

Extract transform load (ETL) process in distributed database academic data warehouse

While a data warehouse is designed to support the decision-making function, the most time-consuming partis the Extract Transform Load (ETL) process. Case in Academic Data Warehouse, when data source came from thefaculty’s distributed database, although having a typical database but become not easier to integrate. This paperpresents how to an ETL process in distributed database academic data warehouse. Following Data Flow Threadprocess in the data staging area, a deep analysis performed for identifying all tables in each data sources, includingcontent profiling. Then the cleaning, confirming, and data delivery steps pour the different data source into the datawarehouse (DW). Since DW development using bottom-up Kimball’s multidimensional approach, we found the threetypes of extraction activities from data source table: merge, merge-union, and union. Result for cleaning andconforming step set by creating conform dimension on data source analysis, refinement, and hierarchy structure. Thefinal of the ETL step is loading it into integrating dimension and fact tables by a generation of a surrogate key. Thoseprocesses are running gradually from each distributed database data sources until it incorporated. This technicalactivity in distributed database ETL process generally can be adopted widely in other industries which designer musthave advance knowledge to structure and content of data source.

DOD-ETL: distributed on-demand ETL for near real-time business intelligence

The competitive dynamics of the globalized market demand information on the internal and external reality of corporations. Information is a precious asset and is responsible for establishing key advantages to enable companies to maintain their leadership. However, reliable, rich information is no longer the only goal. The time frame to extract information from data determines its usefulness. This work proposes DOD-ETL, a tool that addresses, in an innovative manner, the main bottleneck in Business Intelligence solutions, the Extract Transform Load process (ETL), providing it in near real-time. DOD-ETL achieves this by combining an on-demand data stream pipeline with a distributed, parallel and technology-independent architecture with in-memory caching and efficient data partitioning. We compared DOD-ETL with other Stream Processing frameworks used to perform near real-time ETL and found DOD-ETL executes workloads up to 10 times faster. We have deployed it in a large steelworks as a replacement for its previous ETL solution, enabling near real-time reports previously unavailable.

Efficient incremental loading in ETL processing for real-time data integration

ETL (extract transform load) is the widely used standard process for creating and maintaining a data warehouse (DW). ETL is the most resource-, cost- and time-demanding process in DW implementation and maintenance. Nowadays, many graphical user interfaces (GUI)-based solutions are available to facilitate the ETL processes. In spite of the high popularity of GUI-based tool, there is still some downside of such approach. This paper focuses on alternative ETL developmental approach taken by hand coding. In some contexts like research and academic work, it is appropriate to go for custom-coded solution which can be cheaper, faster and maintainable compared to any GUI-based tools. Some well-known code-based open-source ETL tools developed by the academic world have been studied in this article. Their architecture and implementation details are addressed here. The aim of this paper is to present a comparative evaluation of these code-based ETL tools. Finally, an efficient ETL model is designed to meet the near real-time responsibility of the present days.

Privacy-enhancing ETL-processes for biomedical data

BackgroundModern data-driven approaches to medical research require patient-level information at comprehensive depth and breadth. To create the required big datasets, information from disparate sources can be integrated into clinical and translational warehouses. This is typically implemented with Extract, Transform, Load (ETL) processes, which access, harmonize and upload data into the analytics platform. ObjectivePrivacy-protection needs careful consideration when data is pooled or re-used for secondary purposes, and data anonymization is an important protection mechanism. However, common ETL environments do not support anonymization, and common anonymization tools cannot easily be integrated into ETL workflows. The objective of the work described in this article was to bridge this gap. MethodsOur main design goals were (1) to base the anonymization process on expert-level risk assessment methodologies, (2) to use transformation methods which preserve both the truthfulness of data and its schematic properties (e.g. data types), (3) to implement a method which is easy to understand and intuitive to configure, and (4) to provide high scalability. ResultsWe designed a novel and efficient anonymization process and implemented a plugin for the Pentaho Data Integration (PDI) platform, which enables integrating data anonymization and re-identification risk analyses directly into ETL workflows. By combining different instances into a single ETL process, data can be protected from multiple threats. The plugin supports very large datasets by leveraging the streaming-based processing model of the underlying platform. We present results of an extensive experimental evaluation and discuss successful applications. ConclusionsOur work shows that expert-level anonymization methodologies can be integrated into ETL workflows. Our implementation is available under a non-restrictive open source license and it overcomes several limitations of other data anonymization tools.

ETL Processes Security Modeling

The development of information systems (IS) in a secure environment or condition is a complex task that involves many additional basic security protocols, policies as well as industry standards on passwords, anti-virus programs, firewalls and data encryption. However, in traditional IS development lifecycles, security is either ignored or added as an afterthought, which does not assure the system complete security. So, it is necessary to give more importance to this issue and consider it as part of IS development process. In this context, the authors should guarantee the security of ETL (Extract, Transform, Load) processes, which are among the most critical and complex tasks during DW development project. In this study, security management is carried out for ETL processes by proposing a meta-model integrating the security concepts from the security requirements to the necessary preventive and / or corrective treatments. The proposed meta-model is validated with instantiation.