Data-Centric Systems and Applications Alejandro Vaisman Esteban Zimányi Data Warehouse Systems Design and Implementation Second Edition Data-Centric Systems and Applications Series Editors Michael J. Carey, University of California, Irvine, CA, USA Stefano Ceri, Politecnico di Milano, Milano, Italy Editorial Board Members Anastasia Ailamaki, École Polytechnique Fédérale de Lausanne (EPFL), Lausanne, Switzerland Shivnath Babu, Duke University, Durham, NC, USA Philip A. Bernstein, Microsoft Corporation, Redmond, WA, USA Johann-Christoph Freytag, Humboldt Universität zu Berlin, Berlin, Germany Alon Halevy, Facebook, Menlo Park, CA, USA Jiawei Han, University of Illinois, Urbana, IL, USA Donald Kossmann, Microsoft Research Laboratory, Redmond, WA, USA Gerhard Weikum, Max-Planck-Institut für Informatik, Saarbrücken, Germany Kyu-Young Whang, Korea Advanced Institute of Science & Technology, Daejeon, Korea (Republic of) Jeffrey Xu Yu, Chinese University of Hong Kong, Shatin, Hong Kong Intelligent data management is the backbone of all information processing and has hence been one of the core topics in computer science from its very start. This series is intended to offer an international platform for the timely publication of all topics relevant to the development of data-centric systems and applications.
All books show a strong practical or application relevance as well as a thorough scientific basis. They are therefore of particular interest to both researchers and professionals wishing to acquire detailed knowledge about concepts of which they need to make intelligent use when designing advanced solutions for their own problems. Special emphasis is laid upon: • Scientifically solid and detailed explanations of practically relevant concepts and techniques (what does it do) • Detailed explanations of the practical relevance and importance of concepts and techniques (why do we need it) • Detailed explanation of gaps between theory and practice (why it does not work) According to this focus of the series, submissions of advanced textbooks or books for advanced professional use are encouraged; these should preferably be authored books or monographs, but coherently edited, multi-author books are also envisaged (e. for emerging topics).
On the other hand, overly technical topics (like physical data access, data compression etc.), latest research results that still need validation through the research community, or mostly product-related information for practitioners (“how to use Oracle 9i efficiently”) are not encouraged. Alejandro Vaisman • Esteban Zimányi Data Warehouse Systems Design and Implementation Second Edition Alejandro Vaisman Esteban Zimányi Instituto Tecnológico de Buenos Aires Université Libre de Bruxelles Buenos Aires, Argentina Brussels, Belgium ISSN 2197-9723 ISSN 2197-974X (electronic) Data-Centric Systems and Applications ISBN 978-3-662-65166-7 ISBN 978-3-662-65167-4 (eBook) https://doi.1007/978-3-662-65167-4 © Springer-Verlag GmbH Germany, part of Springer Nature 2014, 2022 This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. The use of general descriptive names, registered names, trademarks, service marks, etc.
in this publication does not imply, even in the absence of a specific statement, that such names are exempt from the relevant protective laws and regulations and therefore free for general use. The publisher, the authors and the editors are safe to assume that the advice and information in this book are believed to be true and accurate at the date of publication. Neither the publisher nor the authors or the editors give a warranty, expressed or implied, with respect to the material contained herein or for any errors or omissions that may have been made. The publisher remains neutral with regard to jurisdictional claims in published maps and institutional affiliations.
This Springer imprint is published by the registered company Springer-Verlag GmbH, DE part of Springer Nature. The registered company address is: Heidelberger Platz 3, 14197 Berlin, Germany To Andrés and Manuel, who bring me joy and happiness day after day A. To Elena, the star that shed light upon my path, with all my love E. Foreword to the Second Edition Dear reader, Assuming you are looking for a textbook on data warehousing and the analytical processing of data, I can assure you that you are certainly in the right spot.
In fact, I could easily argue how panoramic and lucid the view from this spot is, and in the next few paragraphs, this is exactly what I am going to do. Assembling a good book from the bits and pieces of writings, slides, and article commentaries that an author has in his folders, is no easy task. Even more, if the book is intended to serve as a textbook, it requires an extra dose of love and care for the students who are going to use it (and their instructors, too, in fact). The book you have at hand is the product of hard work and deep caring by our two esteemed colleagues, Alejandro Vaisman and Esteban Zimányi, who have invested a large amount of effort to produce a book that is (a) comprehensive, (b) up-to-date, (c) easy to follow, and, (d) useful and to-the-point.
While the book is also addressing the researcher who, coming from a different background, wants to enter the area of data warehousing, as well as the newcomer to data processing, who might prefer to start the journey of working with data from the neat setup of data cubes, the book is perfectly suited as a textbook for advanced undergraduate and graduate courses in the area of data warehousing. The book comprehensively covers all the fundamental modeling issues, and addresses also the practical aspects on querying and populating the ware- house. The usage of concrete examples, consistently revisited throughout the book, guide the student to understand the practical considerations, and a set of exercises help the instructor with the hands-on design of a course. For what it’s worth, I have already used the first edition of the book for my graduate data warehouse course and will certainly switch to the new version in the years to come.
If you, dear reader, have already read the first edition of the book, you already know that the first part, covering the modeling fundamentals, and the second part, covering the practical usage of data warehousing are both vii viii Foreword to the Second Edition comprehensive and detailed. To the extent that the fundamentals have not changed (and are not really expected to change in the future), apart from a set of extensions spread throughout the first part of the book, the main improve- ments concern readability on the one hand, and the technological advances on the other. Specifically, the dedicated chapter 7 on practical data analysis with lots of examples over a specific example, as well as the new topics cov- ering partitioning and parallel data processing in the physical management of the data warehouse provide an even more easy path to the novice reader into the areas of querying and managing the warehouse. I would like, however, to take the opportunity and direct your attention to the really new features of this second edition, which are found in the last unit of the book, concerning advanced areas of data warehousing.
This part goes beyond the traditional data warehousing modeling and implementation and is practically completely refreshed compared to the first edition of the book. The chapter on temporal and multiversion warehousing covers the problem of time encoding for evolving facts and the management of versions. The part on spatial warehouses has been significantly updated. There is a brand-new chapter on graph data processing, and its application to graph warehous- ing and graph OLAP.
Last but extremely significant, the crown jewel of the book, a brand-new chapter on the management of Big Data and the usage of Hadoop, Spark and Kylin, as well as the coverage of distributed, in-memory, columnar, and Not-Only-SQL DBMS’s in the context of analytical data pro- cessing. Recent advents like data processing in the cloud, polystores and data lakes are also covered in the chapter. Based on all that, dear reader, I can only invite you to dive into the con- tents of the book, feeling certain that, once you have completed its reading (or maybe, targeted parts of it), you will join me in expressing our gratitude to Alejandro and Esteban, for providing such a comprehensive textbook for the field of data warehousing in the first place, and for keeping it up to date with the recent developments, in this, current, second edition. Ioannina, Greece Panos Vassiliadis Foreword to the First Edition Having worked with data warehouses for almost 20 years, I was both honored and excited when two veteran authors in the field asked me to write a foreword for their new book and sent me a PDF file with the current draft.
Already the size of the PDF file gave me a first impression of a very comprehensive book, an impression that was heavily reinforced by reading the Table of Contents. After reading the entire book, I think it is quite simply the most comprehensive textbook about data warehousing on the market. The book is very well suited for one or more data warehouse courses, ranging from the most basic to the most advanced. It has all the features that are necessary to make a good textbook.
First, a running case study, based on the Northwind database known from Microsoft’s tools, is used to illustrate all aspects using many detailed figures and examples. Second, key terms and concepts are highlighted in the text for better reading and under- standing. Third, review questions are provided at the end of each chapter so students can quickly check their understanding. Fourth, the many detailed exercises for each chapter put the presented knowledge into action, yielding deep learning and taking students through all the steps needed to develop a data warehouse.
Finally, the book shows how to implement data warehouses using leading industrial and open-source tools, concretely Microsoft’s suite of data warehouse tools, giving students the essential hands-on experience that enables them to put the knowledge into practice. For the complete database novice, there is even an introductory chapter on standard database concepts and design, making the book self-contained even for this group. It is quite impressive to cover all this material, usually the topic of an entire textbook, without making it a dense read. Next, the book provides a good introduction to basic multidimensional concepts, later moving on to advanced concepts such as summarizability.
A complete overview of the data warehouse and online analytical processing (OLAP) “architecture stack” is given. For the conceptual modeling of the data warehouse, a concise and intuitive graphical notation is used, a full specification of which is given in ix x Foreword to the First Edition an appendix, along with a methodology for the modeling and the translation to (logical-level) relational schemas. Later, the book provides a lot of useful knowledge about designing and querying data warehouses, including a detailed, yet easy to read, description of the de facto standard OLAP query language: MultiDimensional eXpres- sions (MDX). I certainly learned a thing or two about MDX in a short time.
The chapter on extract-transform-load (ETL) takes a refreshingly different approach by using a graphical notation based on the Business Process Mod- eling Notation (BPMN), thus treating the ETL flow at a higher and more understandable level. Unlike most other data warehouse books, this book also provides comprehensive coverage on analytics, including data mining and re- porting, and on how to implement these using industrial tools. The book even has a chapter on methodology issues such as requirements capture and the data warehouse development process, again something not covered by most data warehouse textbooks. However, the one thing that really sets this book apart from its peers is the coverage of advanced data warehouse topics, such as spatial databases and data warehouses, spatiotemporal or mobility databases and data ware- houses, and semantic web data warehouses.
