GEM: requirement-driven generation of ETL and multidimensional conceptual designs

Technical ReportAt the early stages of a data warehouse design project, the main objective is to collect the business requirements and needs, and translate them into an appropriate conceptual, multidimensional design. Typically, this task is performed manually, through a series of interviews involving two different parties: the business analysts and technical designers. Producing an appropriate conceptual design is an errorprone task that undergoes several rounds of reconciliation and redesigning, until the business needs are satisfied. It is of great importance for the business of an enterprise to facilitate and automate such a process. The goal of our research is to provide designers with a semi-automatic means for producing conceptual multidimensional designs and also, conceptual representation of the extract-transform-load (ETL)processes that orchestrate the data flow from the operational sources to the data warehouse constructs. In particular, we describe a method that combines information about the data sources along with the business requirements, for validating and completing –if necessary– these requirements, producing a multidimensional design, and identifying the ETL operations needed. We present our method in terms of the TPC-DS benchmark and show its applicability and usefulness.Preprin

Requirement modeling for data warehouse using goal-UML approach: the case of health care

Decision makers use Data Warehouse (DW) for performing analysis on business information. DW development is a long term process with high risk of failure and it is difficult to estimate the future requirements for the decision-making. Further, the current DW design does not consider the early and late requirements analysis during its development, especially by using Unified Modeling Language (UML) approach. Due to this problem, it is crucial that current DW modeling approaches covered both early and late requirements analysis in the DW design. A case study was conducted on Malaysia Rural Health Care (MRH) to gather the requirements for DW design. The goal-oriented approach has been used to analyze the early requirements and later was mapped to UML approach to produce a new DW modeling called Goal-UML (G-UML). The proposed approach highlighted the mapping process of DW conceptual schema to a class diagram to produce a complete MRH-DW design. The correctness of the DW design was evaluated using expert reviews. The G-UML method can contribute to the development of DW and be a guideline to the DW developers to produce an improved DW design that meets all the user requirement

Using Ontologies for the Design of Data Warehouses

Obtaining an implementation of a data warehouse is a complex task that forces designers to acquire wide knowledge of the domain, thus requiring a high level of expertise and becoming it a prone-to-fail task. Based on our experience, we have detected a set of situations we have faced up with in real-world projects in which we believe that the use of ontologies will improve several aspects of the design of data warehouses. The aim of this article is to describe several shortcomings of current data warehouse design approaches and discuss the benefit of using ontologies to overcome them. This work is a starting point for discussing the convenience of using ontologies in data warehouse design.Comment: 15 pages, 2 figure

Quality measures for ETL processes: from goals to implementation

Extraction transformation loading (ETL) processes play an increasingly important role for the support of modern business operations. These business processes are centred around artifacts with high variability and diverse lifecycles, which correspond to key business entities. The apparent complexity of these activities has been examined through the prism of business process management, mainly focusing on functional requirements and performance optimization. However, the quality dimension has not yet been thoroughly investigated, and there is a need for a more human-centric approach to bring them closer to business-users requirements. In this paper, we take a first step towards this direction by defining a sound model for ETL process quality characteristics and quantitative measures for each characteristic, based on existing literature. Our model shows dependencies among quality characteristics and can provide the basis for subsequent analysis using goal modeling techniques. We showcase the use of goal modeling for ETL process design through a use case, where we employ the use of a goal model that includes quantitative components (i.e., indicators) for evaluation and analysis of alternative design decisions.Peer ReviewedPostprint (author's final draft