From data warehouses to transformation hubs - A conceptual architecture

Originally, Data Warehouses (DWH) were conceived to be components for the data support of controlling and management. From early on, this brought along the need to cope with extensive data preparation, integration, and distribution requirements. In the growing infrastructures for managerial support (“Business Intelligence”), the DWH turned into a central data hub for decision support. As the business environment and the underlying technical infrastructures are fostering an ever increasing degree of systems integration, the DWH has been recognized to be a pivotal component for all sorts of data transformation and data integration operations. Nowadays, the DWH is supposed to process both managerial and operational data – it becomes a transformation hub (TH). This article delineates the relevant motives that drive the trend towards THs and the resulting requirements. The logical composition of a TH is developed based on data transformation steps. Two case studies exemplify the application of the resulting architecture

Impact of service-oriented architectures (SOA) on business process standardization - Proposing a research model

Originally, Data Warehouses (DWH) were conceived to be components for the data support of controlling and management. From early on, this brought along the need to cope with extensive data preparation, integration, and distribution requirements. In the growing infrastructures for managerial support (“Business Intelligence”), the DWH turned into a central data hub for decision support. As the business environment and the underlying technical infrastructures are fostering an ever increasing degree of systems integration, the DWH has been recognized to be a pivotal component for all sorts of data transformation and data integration operations. Nowadays, the DWH is supposed to process both managerial and operational data – it becomes a transformation hub (TH). This article delineates the relevant motives that drive the trend towards THs and the resulting requirements. The logical composition of a TH is developed based on data transformation steps. Two case studies exemplify the application of the resulting architecture

Data warehouse automation trick or treat?

Data warehousing systems have been around for 25 years playing a crucial role in collecting data and transforming that data into value, allowing users to make decisions based on informed business facts. It is widely accepted that a data warehouse is a critical component to a data-driven enterprise, and it becomes part of the organisation’s information systems strategy, with a significant impact on the business. However, after 25 years, building a Data Warehouse is still painful, they are too time-consuming, too expensive and too difficult to change after deployment. Data Warehouse Automation appears with the promise to address the limitations of traditional approaches, turning the data warehouse development from a prolonged effort into an agile one, with gains in efficiency and effectiveness in data warehousing processes. So, is Data Warehouse Automation a Trick or Treat? To answer this question, a case study of a data warehousing architecture using a data warehouse automation tool, called WhereScape, was developed. Also, a survey was made to organisations that are using data warehouse automation tools, in order to understand their motivation in the adoption of this kind of tools in their data warehousing systems. Based on the results of the survey and on the case study, automation in the data warehouses building process is necessary to deliver data warehouse systems faster, and a solution to consider when modernize data warehouse architectures as a way to achieve results faster, keeping costs controlled and reduce risk. Data Warehouse Automation definitely may be a Treat.Os sistemas de armazenamento de dados existem há 25 anos, desempenhando um papel crucial na recolha de dados e na transformação desses dados em valor, permitindo que os utilizadores tomem decisões com base em fatos. É amplamente aceite, que um data warehouse é um componente crítico para uma empresa orientada a dados e se torna parte da estratégia de sistemas de informação da organização, com um impacto significativo nos negócios. No entanto, após 25 anos, a construção de um Data Warehouse ainda é uma tarefa penosa, demora muito tempo, é cara e difícil de mudar após a sua conclusão. A automação de Data Warehouse aparece com a promessa de endereçar as limitações das abordagens tradicionais, transformando o desenvolvimento da data warehouse de um esforço prolongado em um esforço ágil, com ganhos de eficiência e eficácia. Será, a automação de Data Warehouse uma doçura ou travessura? Foi desenvolvido um estudo de caso de uma arquitetura de data warehousing usando uma ferramenta de automação, designada WhereScape. Foi também conduzido um questionário a organizações que utilizam ferramentas de automação de data warehouse, para entender sua motivação na adoção deste tipo de ferramentas. Com base nos resultados da pesquisa e no estudo de caso, a automação no processo de construção de data warehouses, é necessária para uma maior agilidade destes sistemas e uma solução a considerar na modernização destas arquiteturas, pois permitem obter resultados mais rapidamente, mantendo os custos controlados e reduzindo o risco. A automação de data warehouse pode bem vir a ser uma “doçura”

Modeling Data Lake Metadata with a Data Vault

International audienceWith the rise of big data, business intelligence had to find solutions for managing even greater data volumes and variety than in data warehouses, which proved ill-adapted. Data lakes answer these needs from a storage point of view, but require managing adequate metadata to guarantee an efficient access to data. Starting from a multidimensional metadata model designed for an industrial heritage data lake presenting a lack of schema evolutivity, we propose in this paper to use ensemble modeling, and more precisely a data vault, to address this issue. To illustrate the feasibility of this approach, we instantiate our metadata conceptual model into relational and document-oriented logical and physical models, respectively. We also compare the physical models in terms of metadata storage and query response time

Translating Data Between Xml Schema and 6Nf Conceptual Models

Sixth Normal Form (6NF) is a term used in relational database theory by Christopher Date to describe databases which decompose relational variables to irreducible elements. While this form may be unimportant for non-temporal data, it is certainly important for data containing temporal variables of a point-in-time or interval nature. With the advent of Data Warehousing 2.0 (DW 2.0), there is now an increased emphasis on using fully-temporalized databases in the context of data warehousing, in particular with approaches such as the Anchor Model and Data Vault. In this work, we will explore the concepts of temporal data, 6NF conceptual database models, and their relationship with DW 2.0. Further, we will evaluate the Anchor Model and Data Vault as design methods in which to capture temporal data. Using these methods, we will define a process for translating 6NF-compliant data into a standard eXtensible Markup Language (XML) Schema which can be used to describe and present such data to disparate systems in a structured format suitable for data exchange. Further, we will discuss benefits, advantages, and limitations to using XML representations of 6NF models for the transfer of data into a data warehouse

Showing the Benefits of Applying a Model Driven Architecture for Developing Secure OLAP Applications

Data Warehouses (DW) manage enterprise information that is queried for decision making purposes by using On-Line Analytical Processing (OLAP) tools. The establishment of security constraints in all development stages and operations of the DW is highly important since otherwise, unauthorized users may discover vital business information. The final users of OLAP tools access and analyze the information from the corporate DW by using specific views or cubes based on the multidimensional modelling containing the facts and dimensions (with the corresponding classification hierarchies) that a decision maker or group of decision makers are interested in. Thus, it is important that security constraints will be also established over this metadata layer that connects the DW's repository with the decision makers, that is, directly over the multidimensional structures that final users manage. In doing so, we will not have to define specific security constraints for every particular user, thereby reducing the developing time and costs for secure OLAP applications. In order to achieve this goal, a model driven architecture to automatically develop secure OLAP applications from models has been defined. This paper shows the benefits of this architecture by applying it to a case study in which an OLAP application for an airport DW is automatically developed from models. The architecture is composed of: (1) the secure conceptual modelling by using a UML profile; (2) the secure logical modelling for OLAP applications by using an extension of CWM; (3) the secure implementation into a specific OLAP tool, SQL Server Analysis Services (SSAS); and (4) the transformations needed to automatically generate logical models from conceptual models and the final secure implementation.This research is part of the following projects: SERENIDAD (PEII11- 037-7035) financed by the ”Viceconsejería de Ciencia y Tecnología de la Junta de Comunidades de Castilla-La Mancha” (Spain) and FEDER, and SIGMA-CC (TIN2012-36904) and GEODAS (TIN2012-37493-C03-01) financed by the ”Ministerio de Economía y Competitividad” (Spain)

Designing secure data warehouses by using MDA and QVT

The Data Warehouse (DW) design is based on multidimensional (MD) modeling which structures information into facts and dimensions. Due to the confidentiality of the data that it stores, it is crucial to specify security and audit measures from the early stages of design and to enforce them throughout the lifecycle. Moreover, the standard framework for software development, Model Driven Architecture (MDA), allows us to define transformations between models by proposing Query/View/Transformations (QVT). This proposal permits the definition of formal, elegant and unequivocal transformations between Platform Independent Models (PIM) and Platform Specific Models (PSM). This paper introduces a new framework for the design of secure DWs based on MDA and QVT, which covers all the design phases (conceptual, logical and physical) and specifies security measures in all of them. We first define two metamodels with which to represent security and audit measures at the conceptual and logical levels. We then go on to define a transformation between these models through which to obtain the traceability of the security rules from the early stages of development to the final implementation. Finally, in order to show the benefits of our proposal, it is applied to a case study.This work has been partially supported by the METASIGN project (TIN2004-00779) from the Spanish Ministry of Education and Science, of the Regional Government of Valencia, and by the QUASIMODO and MISTICO projects of the Regional Science and Technology Ministry of Castilla-La Mancha (Spain)

Testing Data Vault-Based Data Warehouse

Data warehouse (DW) projects are undertakings that require integration of disparate sources of data, a well-defined mapping of the source data to the reconciled data, and effective Extract, Transform, and Load (ETL) processes. Owing to the complexity of data warehouse projects, great emphasis must be placed on an agile-based approach with properly developed and executed test plans throughout the various stages of designing, developing, and implementing the data warehouse to mitigate against budget overruns, missed deadlines, low customer satisfaction, and outright project failures. Yet, there are often attempts to test the data warehouse exactly like traditional back-end databases and legacy applications, or to downplay the role of quality assurance (QA) and testing, which only serve to fuel the frustration and mistrust of data warehouse and business intelligence (BI) systems. In spite of this, there are a number of steps that can be taken to ensure DW/BI solutions are successful, highly trusted, and stable. In particular, adopting a Data Vault (DV)-based Enterprise Data Warehouse (EDW) can simplify and enhance various aspects of testing, and curtail delays common in non-DV based DW projects. A major area of focus in this research is raw DV loads from source systems, keeping transformations to a minimum in the ETL process which loads the DV from the source. Certain load errors, classified as permissible errors and enforced by business rules, are kept in the Data Vault until correct values are supplied. Major transformation activities are pushed further downstream to the next ETL process which loads and refreshes the Data Mart (DM) from the Data Vault