Informed decision-making is required for competitive success in the new global marketplace, which is fraught with uncertainty and rapid technology changes. Decision makers must adjust operational processes, corporate strategies, and business models at lightning speed and must be able to leverage business intelligence instantly and take immediate action (Oxford Economics, 2011). Sound decisions are based on data that is analyzed according to well-defined criteria. Such data typically resides in a Database Warehouse for purposes of performing statistical and analytical processing efficiently. Data warehouses (DWH) are typically designed for efficient processing of read only analysis queries over large data, allowing only offline updates at night. The current trends of business globalization and online business activities available 24/7 means DWH must support the increasing demands for the latest versions of the data. Real-Time (or Active) Data Warehousing aims to meet the increasing demands of Business Intelligence for the latest versions of the data (Athanassoulis, et al., 2011). Achieving Real-Time Data Warehousing is highly dependent on the choice of a process in data warehousing technology known as Extract, Transform, and Load (ETL). This process involves: 1) Extracting data from outside sources; 2) Transforming it to fit operational needs; and 3) Loading it into the end target (database or data warehouse). Not all ETL’s are equal when it comes to quality and performance. As such, optimizing the ETL processes for real time decision making is becoming ever increasingly crucial to today's decision-making process. An effective ETL leads to effective business decisions and yields extraordinary decision-making outcomes. This study overviews the theory behind ETL and raises a research vision for its evolution, with the aim of improving the difficult but necessary data management work required for the development of advanced analytics and business intelligence.

ETL Evolution for Real-Time Data Warehousing

Over the past several years the amount of published open data has increased significantly. The majority of this is tabular data, that requires powerful and flexible approaches for data cleaning and preparation in order to convert it into Linked Data. This paper introduces Grafterizer – a software framework developed to support data workers and data developers in the process of converting raw tabular data into linked data. Its main components include Grafter, a powerful software library and DSL for data cleaning and RDF-ization, and Grafterizer, a user interface for interactive specification of data transformations along with a back-end for management and execution of data transformations. The proposed demonstration will focus on Grafterizer’s powerful features for data cleaning and RDF-ization in a scenario using data about the risk of failure of transport infrastructure components due to natural hazards.

/pdf/tabular-data-cleaning-and-linked-data-generation-with-3h86hvzd8n.pdf

Tabular Data Cleaning and Linked Data Generation with Grafterizer

In software development, patterns and standards are two important things that contribute strongly to the success of any system implementation. Characteristics like these ones improve a lot systems communication and data interchange across different computational platforms, integrating processes and data flows in an easy way. In ETL systems, the change of business requirements is a very serious problem leading frequently to reengineer existing populating processes implementations in order to receive new data structures or tasks not defined previously. Every time this happens, existing ETL processes must be changed in order to accommodate new business requirements. Furthermore, ETL modelling and planning suffers from a lack of mature methodology and notation to represent ETL processes in a uniform way across all implementation process, providing means to validate, reduce implementation errors, and improve communication among users with different knowledge in the field. In this paper, we used the BPMN modelling language for ETL conceptual modelling, providing formal specifications for workflow orchestration and data process transformations. We provide a new layer of abstraction that is based on a set of patterns expressed in BPMN for ETL conceptual modelling. These patterns or meta-models represent the most common used tasks in real world ETL systems.

Pattern-Based ETL Conceptual Modelling

The potential applications of big data need semantic annotation and unified integration of heterogeneous data. This paper proposes MOUNT a multi-level annotation and integration framework that significantly process the heterogeneous dataset by exploiting the semantic knowledge to improve the query processing in the large scale infrastructure. The multi-level annotation proposes the coarse-grained and fine-grained annotation models. The coarse-grained annotation employs Yago and SEeds SEarch to categorize the domain information on the big data and fine-grained annotation enables semantic enrichment. Moreover, the MOUNT approach integrates the structured and unstructured data to form the global resource description framework ontology. Moreover, it facilitates the query processing by translating the natural language user query into structured triples. The experimental results prove that the MOUNT approach yields a better performance in terms of result accuracy by 94%.

Multi-level semantic annotation and unified data integration using semantic web ontology in big data processing

One of the important component toward developing a business intelligence framework is data integration. It is used to combine data from different academic data sources and integrate it together before stored it in integrated manner as needed by higher education institute. ETL is the common steps to integrate data and transform it to compatible in targeted destination system. This paper use Universiti Teknologi Malaysia (UTM) as the case study for further understanding on the relation of data integration with extract, transform and load (ETL) process. Data integration plays its important role in BI framework as it integrates different attributes from different tables during transformation process in ETL. ETL process performs data cleaning during extraction process and load significant data into data warehouse. Thus, it maintains the integrity of data to be used for decision making and reporting. This paper will highlight the importance of data integration and ETL in business intelligence framework and its significance for better higher education management.

Significance of data integration and ETL in business intelligence framework for higher education

Extraction-transformation-loading (ETL) tools are pieces of software responsible for the extraction of data from several sources, its cleansing, customization, reformatting, integration, and insertion into a data warehouse. Building the ETL process is potentially one of the biggest tasks of building a warehouse; it is complex, time consuming, and consumes most of data warehouse project's implementation efforts, costs, and resources. Building a data warehouse requires focusing closely on understanding three main areas: the source area, the destination area, and the mapping area (ETL processes). The source area has standard models such as entity relationship diagram, and the destination area has standard models such as star schema, but the mapping area has not a standard model till now. In spite of the importance of ETL processes, little research has been done in this area due to its complexity. There is a clear lack of a standard model that can be used to represent the ETL scenarios. In this paper we will try to navigate through the efforts done to conceptualize the ETL processes. Research in the field of modeling ETL processes can be categorized into three main approaches: Modeling based on mapping expressions and guidelines, modeling based on conceptual constructs, and modeling based on UML environment. These projects try to represent the main mapping activities at the conceptual level. Due to the variation and differences between the proposed solutions for the conceptual design of ETL processes and due to their limitations, this paper also will propose a model for conceptual design of ETL processes. The proposed model is built upon the enhancement of the models in the previous models to support some missing mapping features.

Ali Hamed El Bastawissy

Papers

Original Article: A proposed model for data warehouse ETL processes