Extract, Transform And Load Process Research Articles

3D Cadastral Database Systems—A Systematic Literature Review

Cadastral databases have been used for over 20 years, but most contain 2D data. The increasing presence of high-rise buildings with modern architecture complicates the process of determining property rights, restrictions, and responsibilities. It is, therefore, necessary to develop an efficient system for storing and managing multidimensional cadastral data. While there have been attempts to develop 3D cadastral database schemas, a comprehensive solution that meets all the requirements for effective data storage, manipulation, and retrieval has not yet been presented. This study aims to analyse the literature on 3D cadastral databases to identify approaches and technologies for storing and managing these data. Based on a systematic literature review integrated with a snowballing methodology, 108 documents were identified. During the analysis of the related documents, different parameters were extracted, including the conceptual data model, query type, and evaluation metrics, as well as the database management system (DBMS) used and technologies for visualisation, data preparation, data transformation, and the ETL (extract, transform, and load) process. The study emphasised the importance of adhering to database design principles and identified challenges associated with conceptual design, DBMS selection, logical design, and physical design. The study results provide insights for selecting the appropriate standards, technologies, and DBMSs for designing a 3D cadastral database system.

Development ETL (Extract, Transform and Load) Module in Indonesian Agricultural Commodities OLAP System

The SOLAP system for Indonesian Agricultural Commodities is a successful development based on previous studies. Agricultural commodity data are managed in a data warehouse with a galactic schema, which has 7 fact tables, namely cut flower horticulture, ornamental plant horticulture, horticulture, food crops, plantation, livestock population, and livestock production, as well as 3 dimensional tables, namely location, time, and commodity. The results of SOLAP operations on the system can be visualized in the form of crosstabs, graphs and maps. The system uses a web platform so that it can be accessed by the public. However, the SOLAP system cannot update data in real time. This study aims to develop a data warehouse for Indonesian Agricultural Commodities SOLAP in real time by creating a scraping system. This study has succeeded in developing a data warehouse in real time on the indonesian agricultural commodity SOLAP system by creating a real time scraping system that is applied to the SOLAP server and has succeeded in making the ETL process run in real time on the SOLAP server and optimizing polygon-based spatial data visualization using the Douglas-Peucker. This study has also carried out functional testing of OLAP features and functions on the Indonesian Agricultural Commodity SOLAP system using the black box testing method. The results of this study provide accurate and real-time data on the SOLAP of Indonesian Agricultural Commodities, with the results of SOLAP feature testing achieving 100 percent pass and the data conformity test results of OLAP function as expected. In addition, the results of this study make it possible to automatically update the data according to a predetermined schedule to provide real-time information.

Data Warehouse Model Design PT. Pos Indonesia

This study aims to determine the role of the data warehouse, data warehouse design, and analyze case studies from the of PT. Pos Indonesia. The data warehouse has an important role for a company in terms of storing and processing data automatically to assist companies in terms of finding data, managing data, making quality decisions and data consistency. As the need for practical data develops and is able to handle data with very large results and display historical data, it is therefore necessary to develop a database for PT. Pos Indonesia. This study is a file design activity from the information provided by the PT. Pos Indonesia. The research method used in this study is a quantitative method which is divided into several stages. The planning phase of the warehouse starts with the subject data warehouse, defines the data source, and steps to implement capabilities to create multidimensional data models in the form of star schemas, snowflake schematics and constellation diagram experts, ETL (Extract, Transform and Load) processes and report analysis. Reporting on the data warehouse information analysis process at the Post Shop PT. Pos Indonesia compiles the best sales information based on product type, the highest sales volume per customer and the highest sales volume based on time. The report analysis results are very useful for making data from 2014 and 2015

Automated heuristic based context dependent ETL process to generate multi‐dimensional model for tabular data

SummaryOver the past decade, enterprises have broadly adopted data warehousing in various activities. Today, abundant information is available on websites in the form of tables or spreadsheets. This huge amount of data cannot be processed directly because of its complexity, heterogeneity, and gap between user requirements. In this work, an automatic approach is proposed to build the multi‐dimensional structure (MDS) of heterogeneous tabular data format for intelligent decision‐making. The proposed MDS is generated by identifying components such as dimensions and hierarchies. It automatically extracts measures based on the spatial characteristics of data dimensions like region, time as well as their hierarchies. This proposed approach automatically generates a multi‐dimensional model for BI tools without complicated ETL (Extraction, Transformation and Loading) process and helps to solve several business queries, like “Top 5 states in India based on Irrigated area in 2009”. Moreover, the proposed method reduces the time and cost of building multi‐dimensional models to a very large extent. The correctness of proposed method is tested with the synthetic and economic datasets of Government websites where information is stored in tabular formats and various heterogeneous setups where the proposed method saved approximately 4000 to 5000 computing hours of the ETL process.

An ETL-process design for data harmonization to participate in international research with German real-world data based on FHIR and OMOP CDM

BackgroundInternational studies are increasingly needed in order to gain more unbiased evidence from real-world data. To achieve this goal across the European Union, the EMA set up the DARWIN EU project based on OMOP CDM established by the OHDSI community. The harmonization of heterogeneous local health data in OMOP CDM is an essential step to participate in such networks. Using the widespread communication standard HL7 FHIR can reduce the complexity of the transformation process to OMOP CDM. Enabling German university hospitals to participate in such networks requires an Extract, Transform and Load (ETL)-process that satisfies the following criteria: 1) transforming German patient data from FHIR to OMOP CDM, 2) processing huge amount of data at once and 3) flexibility to cope with changes in FHIR profiles. MethodA mapping of German patient data from FHIR to OMOP CDM was accomplished, validated by an interdisciplinary team and checked through the OHDSI Data Quality Dashboard (DQD). To satisfy criteria 2–3, we decided to use SpringBatch-Framework according to its chunk-oriented design and reusable functions for processing large amounts of data. ResultsWe have successfully developed an ETL-process that fulfills the defined criteria of transforming German patient data from FHIR into OMOP CDM. To measure the validity of the mapping conformance and performance of the ETL-process, it was tested with 392,022 FHIR resources. The ETL execution lasted approximately-one minute and the DQD result shows 99% conformance in OMOP CDM. ConclusionsOur ETL-process has been successfully tested and integrated at 10 German university hospitals. The data harmonization utilizing international recognized standards like FHIR and OMOP fosters their ability to participate in international observational studies. Additionally, the ETL process can help to prepare more German hospitals with their data harmonization journey based on existing standards.

Extraction Transformation and Loading (ETL) of Data Using ETL Tools

Abstract: This Research Paper presents the Extract, Transform, Load (ETL) Process and discusses various ETL Tools Available in the Market. A huge piece of BI frameworks is a well-performing Implementation of the Extract, Transform, and Load (ETL) process. In BI projects, implementing the ETL process can be the big task ETL is the core process of Data integration which is associated with Data Warehouse. This paper also focuses on the best ETL Tools and which tool can be the best for the ETL process.

Pembangunan Dashboard Inventory Pada Bisnis Ritel

In this study, it will be explained how to create a data mart in a retail business to answer the KPI (Key Performance Indicator) inventory of goods which will be visualized in the form of a dashboard to make it easier for company executives to see the condition of inventory in stores. In making a data mart, it will be explained how the ETL (Extraction, Transformation and Loading) process from a transactional database from 9 stores becomes a data mart. The data mart in this study was built using a star schema and produced 4 dimension tables and 1 fact table. Star schema is used to simplify the query process when you want to display data on the dashboard. After the dashboard has successfully visualized the data, then testing is carried out on users using the UEQ (User Experience Questionnaire) method to find out how the user experience when using the dashboard. From this study, it was found that the dashboard was able to visualize the total inventory of goods, consigned goods inventory, non-consigned trunk inventory in monthly, semester, quarterly, and annual periods. And excellent results on 6 scales of User Experience Questionnaire (UEQ).

IMPLEMENTASI BUSINESS INTELLIGENCE UNTUK MENENTUKAN TREND PENJUALAN BARANG (STUDI KASUS: PT. MENIKMATI DISTRIBUSI)

This study aims to analyze the implementation of Business intelligence to determine the trend in sales of goods in response to the needs of the company, make decisions, and choosing sales strategies. Business intelligence (BI) is one of the solutions dealing with company needs, especially analyzing and providing data access to help make or take better decisions. There are several stages could help the company to facilitate the decision making process, such as database design, data warehouse design, ETL (Extract, Transform, and Load) process, OLAP cube making and followed by report making. By seeing the report analyzes, the result of this study is the sale of goods in the Makassar area has the lowest contribution compared to other areas. The lowest sales by outlet can be seen from the O Voult Legian outlet which is ranked last with the fewest sales. Therefore, based on the results above the area or outlet that has the lowest contribution must be treated specifically. Conducting promotions and discounts is one of the objectives to increase the number of orders from customers with a greater range.

Applying the ETL Process to Blockchain Data. Prospect and Findings

We present a novel strategy, based on the Extract, Transform and Load (ETL) process, to collect data from a blockchain, elaborate and make it available for further analysis. The study aims to satisfy the need for increasingly efficient data extraction strategies and effective representation methods for blockchain data. For this reason, we conceived a system to make scalable the process of blockchain data extraction and clustering, and to provide a SQL database which preserves the distinction between transaction and addresses. The proposed system satisfies the need to cluster addresses in entities, and the need to store the extracted data in a conventional database, making possible the data analysis by querying the database. In general, ETL processes allow the automation of the operation of data selection, data collection and data conditioning from a data warehouse, and produce output data in the best format for subsequent processing or for business. We focus on the Bitcoin blockchain transactions, which we organized in a relational database to distinguish between the input section and the output section of each transaction. We describe the implementation of address clustering algorithms specific for the Bitcoin blockchain and the process to collect and transform data and to load them in the database. To balance the input data rate with the elaboration time, we manage blockchain data according to the lambda architecture. To evaluate our process, we first analyzed the performances in terms of scalability, and then we checked its usability by analyzing loaded data. Finally, we present the results of a toy analysis, which provides some findings about blockchain data, focusing on a comparison between the statistics of the last year of transactions, and previous results of historical blockchain data found in the literature. The ETL process we realized to analyze blockchain data is proven to be able to perform a reliable and scalable data acquisition process, whose result makes stored data available for further analysis and business.

Implementation of ETL Process using Pig and Hadoop

ETL stands for extraction, transformation and loading, where extraction is done to active data from the source, transformation involve data cleansing, data filtering, data validation and finally application of certain rules and loading stores back the data to the destination repository where it has to finally reside. Pig is one of the most important to which could be applied in Extract, Transform and Load (ETL) process. It helps in applying the ETL approach to the large set of data. Initially Pig loads the data, and further is able to perform predictions, repetitions, expected conversions and further transformations. UDFs can be used to perform more complex algorithms during the transformation phase. The huge data processed by Pig, could be stored back in HDFS. In this paper we demonstrate the ETL process using Pig in Hadoop. Here we demonstrate how the files in HDFS are extracted, transformed and loaded back to HDFS using Pig. We extend the functionality of Pig Latin with Python UDFs to perform transformations.

Optimization of ETL Process in Data Warehouse Through a Combination of Parallelization and Shared Cache Memory

Extraction, Transformation and Loading (ETL) is introduced as one of the notable subjects in optimization, management, improvement and acceleration of processes and operations in data bases and data warehouses. The creation of ETL processes is potentially one of the greatest tasks of data warehouses and so its production is a time-consuming and complicated procedure. Without optimization of these processes, the implementation of projects in data warehouses area is costly, complicated and time-consuming. The present paper used the combination of parallelization methods and shared cache memory in systems distributed on the basis of data warehouse. According to the conducted assessment, the proposed method exhibited 7.1% speed improvement to kattle optimization instrument and 7.9% to talend instrument in terms of implementation time of the ETL process. Therefore, parallelization could notably improve the ETL process. It eventually caused the management and integration processes of big data to be implemented in a simple way and with acceptable speed.

Aplikasi Business Intelligence Dashboard sebagai Alat Monitoring dan Bahan Pengambilan Keputusan Sales and Account Receivable

Information and data are the important assets for a company. To process data into useful information and getting to be knowledge in the decision making process at management level, there are so many companies which have difficulties, including PT XYZ which was engaging in the production of agricultural materials. PT XYZ had the difficulties in monitoring process and decision-making process related to its main business, sales and account receivable. Business Intelligence dashboard could be the best solution to overcome these problems. This research discusses related design, construction and implementation of business intelligence solutions dashboard starting from the architecture business intelligence, data warehouse in form of a data mart, ETL (extraction, transformation, and loading) process are useful for generating the quality of data, and visualization in the form of dashboard that would represent key business process with XYZ company. The method used in the development of business intelligence dashboard refers to the six-step method of executive information system that is of the justification, planning, business analysis, design, construction, deployment that would generate digital dashboard for level management. The result is a website application consisting of three digital dashboards that become visualization tools to display the information and knowledge needed in the monitoring process and become material to produce decisions related to sales and receivables. The BI solutions that are developed are in line with expectations and can meet the business needs of PT XYZ.

Aplikasi Business Intelligence Dashboard sebagai Alat Monitoring dan Bahan Pengambilan Keputusan Sales and Account Receivable

Information and data are the important assets for a company. To process data into useful information and getting to be knowledge in the decision making process at management level, there are so many companies which have difficulties, including PT XYZ which was engaging in the production of agricultural materials. PT XYZ had the difficulties in monitoring process and decision-making process related to its main business, sales and account receivable. Business Intelligence dashboard could be the best solution to overcome these problems. This research discusses related design, construction and implementation of business intelligence solutions dashboard starting from the architecture business intelligence, data warehouse in form of a data mart, ETL (extraction, transformation, and loading) process are useful for generating the quality of data, and visualization in the form of dashboard that would represent key business process with XYZ company. The method used in the development of business intelligence dashboard refers to the six-step method of executive information system that is of the justification, planning, business analysis, design, construction, deployment that would generate digital dashboard for level management. The result is a website application consisting of three digital dashboards that become visualization tools to display the information and knowledge needed in the monitoring process and become material to produce decisions related to sales and receivables. The BI solutions that are developed are in line with expectations and can meet the business needs of PT XYZ.

A Multi-Agent Framework for Data Extraction,Transformation and Loading in Data Warehouse

The rapid growth in size of data sets poses chal-lenge to extract and analyze information in timely manner for better prediction and decision making. Data warehouse is the solution for strategic decision making. Data warehouse serves as a repository to store historical and current data. Extraction, Transformation and Loading (ETL) process gather data from different sources and integrate it into data warehouse. This paper proposes a multi-agent framework that enhance the efficiency of ETL process. Agents perform specific task assigned to them. The identification of errors at different stages of ETL process become easy. This was difficult and time consuming in traditional ETL process. Multi-agent framework identify data sources, extract, integrate, transform, and load data into data warehouse. A monitoring agent remains active during this process and generate alerts if there is issue at any stage.

Assessment of Automatically Exported Clinical Data from a Hospital Information System for Clinical Research in Multiple Myeloma.

An important part of the electronic information available in Hospital Information System (HIS) has the potential to be automatically exported to Electronic Data Capture (EDC) platforms for improving clinical research. This automation has the advantage of reducing manual data transcription, a time consuming and prone to errors process. However, quantitative evaluations of the process of exporting data from a HIS to an EDC system have not been reported extensively, in particular comparing with manual transcription. In this work an assessment to study the quality of an automatic export process, focused in laboratory data from a HIS is presented. Quality of the laboratory data was assessed in two types of processes: (1) a manual process of data transcription, and (2) an automatic process of data transference. The automatic transference was implemented as an Extract, Transform and Load (ETL) process. Then, a comparison was carried out between manual and automatic data collection methods. The criteria to measure data quality were correctness and completeness. The manual process had a general error rate of 2.6% to 7.1%, obtaining the lowest error rate if data fields with a not clear definition were removed from the analysis (p < 10E-3). In the case of automatic process, the general error rate was 1.9% to 12.1%, where lowest error rate is obtained when excluding information missing in the HIS but transcribed to the EDC from other physical sources. The automatic ETL process can be used to collect laboratory data for clinical research if data in the HIS as well as physical documentation not included in HIS, are identified previously and follows a standardized data collection protocol.

Automated ETL Testing on the Data Quality of a Data Warehouse

ETL (Extract, Transform, and Load) procedures is an important and vital phase during testing Data warehouse (DW); it's almost the most complex phase, because it directly affects the quality of data. It has been proved that automated testing is valuable tool to improve the quality of DW systems while the manual testing process is time consuming and not accurate so automating tests improves Data Quality (DQ) in less time, cost and attaining good data quality. In this paper the author's propose testing framework to automate testing data quality at the stage of ETL process. Different datasets with different volumes (stared from 10,000 records till 50,000 records) are used to evaluate the effectiveness of the proposed automated ETL testing. The conducted experimental results showed that the proposed testing framework is effective in detecting errors with the different data volumes.

MyETL: A Java Software Tool to Extract, Transform, and Load Your Business

Abstract The project follows the development of a Java Software Tool that extracts data from Flat File (Fixed Length Record Type), CSV (Comma Separated Values), and XLS (Microsoft Excel 97-2003 Worksheet file), apply transformation to those sources, and finally load the data into the end target RDBMS. The software refers to a process known as ETL (Extract Transform and Load). Those kinds of systems are called ETL systems. The analysis involved research on the theory behind the ETL process as well as the theory behind the various phases of the applied methodology. Also an in-depth look at the design and architecture of the software has been made. To create a complete design needed to be used for the implementation, different techniques and diagrams where used to visualise and refine ideas: UML class diagrams, System Architecture Diagrams, Physical Data Model, and Project Timeline. The implementation of the project involved the translation of the system architecture into working software using the Extreme Programming Methodology and the Java programming language. A mapping algorithm module and design patterns have been used in the implementation phase. A transformation syntax has been defined to achieve data transformation. The testing of the software was done in the form of a unit test. A formal test plan was prepared to ensure that the main features of the system worked as defined. An error handling code implementation has been developed to avoid an unexpected crash of the system and to communicate to the user problems or errors.

Prototype of a Web ETL Tool

Extract, transform and load (ETL) is a process that makes it possible to extract data from operational data sources, to transform data in the way needed for data warehousing purposes and to load data into a data warehouse (DW). ETL process is the most important part when building the data warehouse. Because the ETL process is a very complex and time consuming, this paper presents a prototype of a web ETL tool that offers step-by-step guidance through the entire process to the end user. This ETL tool is designed as a web application so users can save time (and space) required for installation purposes.

A Comparison Study of Data Scrubbing Algorithms and Frameworks in Data Warehousing

In these days, many organizations tend to use a Data Warehouse to meet the requirements to develop decisionmaking processes and achieve their goals better and satisfy their customers. It enables Executives to access the information they need in a timely manner for making the right decision for any work. Decision Support System (DSS) is one of the means that applied in data mining . Its robust and better decision depends on an important and conclusive factor called Data Quality (DQ), to obtain a high data quality using Data Scrubbing (DS) which is one of data Extraction Transformation and Loading (ETL) tools. Data Scrubbing is very important and necessary in the Data Warehouse (DW). There are growing relationships to get high DQ and effective DS. The use of DS algorithms is a solution to the constraints that limit the DQ which leads to weak decisions and the burden of the high financial costs. These constraints are: dirty data, noise data, missing values, inconsistency, uncertain data, ambiguous, conflicting, duplicated records and similar columns. The Sources and causes of these constraints are many, including: input error, merge data from different sources, difference in representing the same information, etc. In addition there are more than 35 sources and causes of the poor-quality data that arise at the stage of the ETL process. This paper present comparison and analysis for DS algorithms and the pros and cons of each algorithm, accuracy and time complexity. Additionally, it present a comparative and analysis of the Data Scrubbing Frameworks and determine the best framework.

DESAIN ETL DENGAN CONTOH KASUS PERGURUAN TINGGI

Data Warehouse for higher education as a paradigm for helping high management in order to make an effective and efficient strategic decisions based on reliable and trusted reports which is produced from Data Warehouse itself. Data Warehouse is not a software, hardware or tool but Data Warehouse is an environment where the transactional database is modelled in other view for decision making purposes. ETL (Extraction, Transformation and Loading) is a bridge to build Data Warehouse and transform data from transactional database. In every fact and dimension table will be inserted with fields which represent the construction merge loading as an ETL (Extraction, Transformation and Loading) extraction. ETL needs an ETL table and ETL process where ETL table as table connectivity between tables in OLTP database and tables in Data Warehouse and ETL process will transform data from table in OLTP database into Data Warehouse table based on ETL table. The extraction process will be run with a table database as differentiate ETL process and an ETL algorithm which will be run automatically in idle transactional process, along with daily transactional database backup when the information system are not used.