You are previewing Building a Scalable Data Warehouse with Data Vault 2.0.
O'Reilly logo
Building a Scalable Data Warehouse with Data Vault 2.0

Book Description

The Data Vault was invented by Dan Linstedt at the U.S. Department of Defense, and the standard has been successfully applied to data warehousing projects at organizations of different sizes, from small to large-size corporations. Due to its simplified design, which is adapted from nature, the Data Vault 2.0 standard helps prevent typical data warehousing failures.

"Building a Scalable Data Warehouse" covers everything one needs to know to create a scalable data warehouse end to end, including a presentation of the Data Vault modeling technique, which provides the foundations to create a technical data warehouse layer. The book discusses how to build the data warehouse incrementally using the agile Data Vault 2.0 methodology. In addition, readers will learn how to create the input layer (the stage layer) and the presentation layer (data mart) of the Data Vault 2.0 architecture including implementation best practices. Drawing upon years of practical experience and using numerous examples and an easy to understand framework, Dan Linstedt and Michael Olschimke discuss:

  • How to load each layer using SQL Server Integration Services (SSIS), including automation of the Data Vault loading processes.
  • Important data warehouse technologies and practices.
  • Data Quality Services (DQS) and Master Data Services (MDS) in the context of the Data Vault architecture.


  • Provides a complete introduction to data warehousing, applications, and the business context so readers can get-up and running fast
  • Explains theoretical concepts and provides hands-on instruction on how to build and implement a data warehouse
  • Demystifies data vault modeling with beginning, intermediate, and advanced techniques
  • Discusses the advantages of the data vault approach over other techniques, also including the latest updates to Data Vault 2.0 and multiple improvements to Data Vault 1.0

Table of Contents

  1. Cover
  2. Title page
  3. Table of Contents
  4. Copyright
  5. Authors Biography
  6. Foreword
  7. Preface
  8. Acknowledgments
  9. Chapter 1: Introduction to Data Warehousing
    1. Abstract
    2. 1.1. History of Data Warehousing
    3. 1.2. The Enterprise Data Warehouse Environment
    4. 1.3. Introduction to Data Vault 2.0
    5. 1.4. Data Warehouse Architecture
  10. Chapter 2: Scalable Data Warehouse Architecture
    1. Abstract
    2. 2.1. Dimensions of Scalable Data Warehouse Architectures
    3. 2.2. Data Vault 2.0 Architecture
  11. Chapter 3: The Data Vault 2.0 Methodology
    1. Abstract
    2. 3.1. Project Planning
    3. 3.2. Project Execution
    4. 3.3. Review and Improvement
  12. Chapter 4: Data Vault 2.0 Modeling
    1. Abstract
    2. 4.1. Introduction to Data Vault Modeling
    3. 4.2. Data Vault Modeling Vocabulary
    4. 4.3. Hub Definition
    5. 4.4. Link Definition
    6. 4.5. Satellite Definition
  13. Chapter 5: Intermediate Data Vault Modeling
    1. Abstract
    2. 5.1. Hub Applications
    3. 5.2. Link Applications
    4. 5.3. Satellite Applications
  14. Chapter 6: Advanced Data Vault Modeling
    1. Abstract
    2. 6.1. Point-in-Time Tables
    3. 6.2. Bridge Tables
    4. 6.3. Reference Tables
  15. Chapter 7: Dimensional Modeling
    1. Abstract
    2. 7.1. Introduction
    3. 7.2. Star Schemas
    4. 7.3. Multiple Stars
    5. 7.4. Dimension Design
  16. Chapter 8: Physical Data Warehouse Design
    1. Abstract
    2. 8.1. Database Workloads
    3. 8.2. Separate Environments for Development, Testing, and Production
    4. 8.3. Microsoft Azure Cloud Computing Platform
    5. 8.4. Physical Data Warehouse Architecture on Premise
    6. 8.5. Database Options
    7. 8.6. Setting up the Data Warehouse
  17. Chapter 9: Master Data Management
    1. Abstract
    2. 9.1. Definitions
    3. 9.2. Master Data Management Goals
    4. 9.3. Drivers for Managing Master Data
    5. 9.4. Operational vs. Analytical Master Data Management
    6. 9.5. Master Data Management as an Enabler for Managed Self-Service BI
    7. 9.6. Master Data Management as an Enabler for Total Quality Management
    8. 9.7. Creating a Model
    9. 9.8. Importing a Model
    10. 9.9. Integrating MDS with the Data Vault and Operational Systems
  18. Chapter 10: Metadata Management
    1. Abstract
    2. 10.1. What is Metadata?
    3. 10.2. Implementing the Meta Mart
    4. 10.3. Implementing the Metrics Vault
    5. 10.4. Implementing the Metrics Mart
    6. 10.5. Implementing the Error Mart
  19. Chapter 11: Data Extraction
    1. Abstract
    2. 11.1. Purpose of Staging Area
    3. 11.2. Hashing in the Data Warehouse
    4. 11.3. Purpose of the Load Date
    5. 11.4. Purpose of the Record Source
    6. 11.5. Types of Data Sources
    7. 11.6. Sourcing Flat Files
    8. 11.7. Sourcing Historical Data
    9. 11.8. Sourcing the Sample Airline Data
    10. 11.9. Sourcing Denormalized Data Sources
    11. 11.10. Sourcing Master Data from MDS
  20. Chapter 12: Loading the Data Vault
    1. Abstract
    2. 12.1. Loading Raw Data Vault Entities
    3. 12.2. Loading Reference Tables
    4. 12.3. Truncating the Staging Area
  21. Chapter 13: Implementing Data Quality
    1. Abstract
    2. 13.1. Business Expectations Regarding Data Quality
    3. 13.2. The Costs of Low Data Quality
    4. 13.3. The Value of Bad Data
    5. 13.4. Data Quality in the Architecture
    6. 13.5. Correcting Errors in the Data Warehouse
    7. 13.6. Transform, Enhance and Calculate Derived Data
    8. 13.7. Standardization of Data
    9. 13.8. Correct and Complete Data
    10. 13.9. Match and Consolidate Data
    11. 13.10. Creating Dimensions from Same-As Links
  22. Chapter 14: Loading the Dimensional Information Mart
    1. Abstract
    2. 14.1. Using the Business Vault as an Intermediate to the Information Mart
    3. 14.2. Materializing the Information Mart
    4. 14.3. Leveraging PIT and Bridge Tables for Virtualization
    5. 14.4. Implementing Temporal Dimensions
    6. 14.5. Implementing Data Quality Using PIT Tables
    7. 14.6. Dealing with Reference Data
    8. 14.7. About Hash Keys in the Information Mart
  23. Chapter 15: Multidimensional Database
    1. Abstract
    2. 15.1. Accessing the Information Mart
    3. 15.2. Creating Dimensions
    4. 15.3. Creating Cubes
    5. 15.4. Accessing the Cube
  24. Subject Index