Building a Scalable Data Warehouse with Data Vault 2.0

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

Cover 2
Title Page 5
Copyright Page 6
Contents 7
Authors Biography 15
- Daniel Linstedt 15
- Michael Olschimke 15
Foreword 17
Preface 19
Acknowledgments 21
- Daniel Linstedt 21
- Michael Olschimke 21
Chapter 1 - Introduction to Data Warehousing 23
- 1.1 - History of Data Warehousing 24
  - 1.1.1 - Decision Support Systems 25

Cover 2
Title Page 5
Copyright Page 6
Contents 7
Authors Biography 15
- Daniel Linstedt 15
- Michael Olschimke 15
Foreword 17
Preface 19
Acknowledgments 21
- Daniel Linstedt 21
- Michael Olschimke 21
Chapter 1 - Introduction to Data Warehousing 23
- 1.1 - History of Data Warehousing 24
  - 1.1.1 - Decision Support Systems 25
  - 1.1.2 - Data Warehouse Systems 26
- 1.2 - The Enterprise Data Warehouse Environment 27
  - 1.2.1 - Access 27
  - 1.2.2 - Multiple Subject Areas 27
  - 1.2.3 - Single Version of Truth 27
  - 1.2.4 - Single Version of Facts 28
  - 1.2.5 - Mission Criticality 28
  - 1.2.6 - Scalability 28
  - 1.2.7 - Big Data 29
  - 1.2.8 - Performance Issues 29
  - 1.2.9 - Complexity 30
  - 1.2.10 - Auditing and Compliance 31
  - 1.2.11 - Costs 32
  - 1.2.12 - Other Business Requirements 33
- 1.3 - Introduction to Data Vault 2.0 33
- 1.4 - Data Warehouse Architecture 34
  - 1.4.1 - Typical Two-Layer Architecture 34
  - 1.4.2 - Typical Three-Layer Architecture 35
- References 36
Chapter 2 - Scalable Data Warehouse Architecture 39
- 2.1 - Dimensions of Scalable Data Warehouse Architectures 39
  - 2.1.1 - Workload 40
  - 2.1.2 - Data Complexity 40
  - 2.1.4 - Query Complexity 41
  - 2.1.3 - Analytical Complexity 41
  - 2.1.6 - Security 42
  - 2.1.5 - Availability 42
- 2.2 - Data Vault 2.0 Architecture 43
  - 2.2.1 - Business Rules Definition 44
  - 2.2.2 - Business Rules Application 45
  - 2.2.3 - Staging Area Layer 47
  - 2.2.4 - Data Warehouse Layer 48
  - 2.2.5 - Information Mart Layer 49
  - 2.2.6 - Metrics Vault 49
  - 2.2.7 - Business Vault 50
  - 2.2.8 - Operational Vault 51
  - 2.2.9 - Managed Self-Service BI 52
  - 2.2.10 - Other Features 53
- References 53
Chapter 3 - The Data Vault 2.0 Methodology 55
- 3.1 - Project Planning 55
  - 3.1.1 - Capability Maturity Model Integration 61
    - 3.1.1.1 - Capability Levels 62
    - 3.1.1.2 - Maturity Levels 62
    - 3.1.1.4 - Integrating CMMI in the Data Vault 2.0 Methodology 63
    - 3.1.1.3 - Advancing to Maturity Level 5 63
  - 3.1.2 - Managing the Project 64
    - 3.1.2.1 - Scrum 67
    - 3.1.2.2 - Iterative Approach 68
    - 3.1.2.3 - Product and Sprint Backlog 68
    - 3.1.2.4 - Integrating Scrum with the Data Vault 2.0 Methodology 69
  - 3.1.3 - Defining the Project 72
    - 3.1.3.1 - Agile Requirements Gathering 74
  - 3.1.4 - Estimation of the Project 76
    - 3.1.4.1 - Function Point Analysis 76
    - 3.1.4.2 - Measuring with Function Points 76
    - 3.1.4.5 - Estimating Size 78
    - 3.1.4.3 - Function Point Analysis for Data Warehousing 78
    - 3.1.4.4 - Boundaries in Data Warehousing 78
    - 3.1.4.6 - Assessing ETL Complexity Factors 79
    - 3.1.4.7 - Applying Function Point Analysis to Data Warehousing 80
    - 3.1.4.8 - Function Points for Enterprise Data Warehouse 83
  - 3.2 - Project Execution 84
    - 3.2.1 - Traditional Software Development Life-Cycle 85
      - 3.2.1.1 - Requirements Engineering 86
      - 3.2.1.3 - Implementation and Unit Testing 87
      - 3.2.1.2 - Design 87
      - 3.2.1.4 - Integration and System Testing 88
      - 3.2.1.5 - Operation and Maintenance 88
    - 3.2.2 - Applying Software Development Life-Cycle to the Data Vault 2.0 Methodology 89
    - 3.2.3 - Parallel Teams 91
    - 3.2.4 - Technical Numbering 93
  - 3.3 - Review and Improvement 95
    - 3.3.1 - Six Sigma 96
      - 3.3.1.1 - Applying Six Sigma to Software 98
      - 3.3.1.2 - Six Sigma Framework 100
      - 3.3.1.3 - DMAIC Improvement 101
      - 3.3.1.4 - Applying Six Sigma to Data Warehousing 102
    - 3.3.2 - Total Quality Management 103
      - 3.3.2.1 - Data Quality Dimensions 105
      - 3.3.2.2 - Total Data Quality Management 106
      - 3.3.2.3 - Data Warehouse Quality 106
      - 3.3.2.4 - Integrating TQM with the Data Vault 2.0 Methodology 107
    - References 108
  - Chapter 4 - Data Vault 2.0 Modeling 111
    - 4.1 - Introduction to Data Vault Modeling 111
    - 4.2 - Data Vault Modeling Vocabulary 112
      - 4.2.1 - Hub Entities 113
      - 4.2.2 - Link Entities 113
      - 4.2.3 - Satellite Entities 113
    - 4.3 - Hub Definition 115
      - 4.3.1 - Definition of a Business Key 117
        
        4.3.1.1 - Composite Keys (aka Smart Keys, Intelligent Keys) 117
        
        4.3.1.2 - Business Key Identification Process 118
        
        4.3.1.4 - Difference between Business Keys and Surrogate Keys 119
        
        4.3.1.3 - Scope of Business Keys 119
      - 4.3.2 - Hub Entity Structure 120
        
        4.3.2.2 - Business Key 120
        
        4.3.2.1 - Hash Key 120
        
        4.3.2.3 - Load Date 121
        
        4.3.2.4 - Record Source 121
        
        4.3.2.5 - Last Seen Date 122
      - 4.3.3 - Hub Examples 122
    - 4.4 - Link Definition 123
      - 4.4.1 - Reasons for Many-to-Many Relationships 125
      - 4.4.2 - Flexibility of Links 127
      - 4.4.3 - Granularity of Links 128
      - 4.4.4 - Link Unit-of-Work 131
      - 4.4.5 - Link Entity Structure 132
        
        4.4.5.1 - Hash Key 133
        
        4.4.5.2 - Dependent Child Key 133
      - 4.4.6 - Link Examples 133
    - 4.5 - Satellite Definition 134
      - 4.5.2 - Splitting Satellites 136
        
        4.5.2.1 - Splitting by Source System 136
        
        4.5.2.2 - Splitting by Rate of Change 137
      - 4.5.1 - Importance of Keeping History 136
      - 4.5.3 - Satellite Entity Structure 138
        
        4.5.3.2 - Load Date 138
        
        4.5.3.1 - Parent Hash Key 138
        
        4.5.3.5 - Extract Date 139
        
        4.5.3.3 - Load End Date 139
        
        4.5.3.4 - Hash Difference 139
      - 4.5.4 - Satellite Examples 140
      - 4.5.5 - Link Driving Key 141
    - References 143
  - Chapter 5 - Intermediate Data Vault Modeling 145
    - 5.1 - Hub Applications 145
      - 5.1.1 - Business Key Consolidation 146
    - 5.2 - Link Applications 149
      - 5.2.1 - Link-on-link 149
      - 5.2.2 - Same-as Links 151
      - 5.2.3 - Hierarchical Links 151
      - 5.2.4 - Nonhistorized Links 154
      - 5.2.5 - Nondescriptive Links 158
      - 5.2.6 - Computed Aggregate Links 159
      - 5.2.7 - Exploration Links 161
    - 5.3 - Satellite Applications 161
      - 5.3.1 - Overloaded Satellites 161
      - 5.3.2 - Multi-Active Satellites 163
      - 5.3.3 - Status Tracking Satellites 165
      - 5.3.4 - Effectivity Satellites 167
      - 5.3.5 - Record Tracking Satellites 168
      - 5.3.6 - Computed Satellites 171
    - References 172
  - Chapter 6 - Advanced Data Vault Modeling 173
    - 6.1 - Point-in-Time Tables 173
      - 6.1.1 - Point-in-Time Table Structure 175
      - 6.1.2 - Managed PIT Window 178
    - 6.2 - Bridge Tables 180
      - 6.2.1 - Bridge Table Structure 181
      - 6.2.2 - Comparing PIT Tables with Bridge Tables 182
    - 6.3 - Reference Tables 182
      - 6.3.1 - No-History Reference Tables 183
      - 6.3.2 - History-Based Reference Tables 185
      - 6.3.3 - Code and Descriptions 186
        
        6.3.3.1 - Code and Descriptions with History 190
      - Reference 191
    - Chapter 7 - Dimensional Modeling 193
      - 7.1 - Introduction 193
      - 7.2 - Star Schemas 194
        
        7.2.1 - Fact Tables 196
        
        7.2.1.1 - The Grain of a Fact Table 198
        
        7.2.2 - Dimension Tables 198
        
        7.2.3 - Querying Star Schemas 199
      - 7.3 - Multiple Stars 201
        
        7.3.1 - Conformed Dimensions 201
      - 7.4 - Dimension Design 202
        
        7.4.1 - Slowly Changing Dimensions 203
        
        7.4.2 - Hierarchies 205
        
        7.4.3 - Snowflake Design 211
      - References 215
    - Chapter 8 - Physical Data Warehouse Design 217
      - 8.1 - Database Workloads 217
        
        8.1.1 - Workload Characteristics 218
      - 8.2 - Separate Environments for Development, Testing, and Production 219
        
        8.2.1 - Blue-Green Deployment 220
      - 8.3 - Microsoft Azure Cloud Computing Platform 222
      - 8.4 - Physical Data Warehouse Architecture on Premise 225
        
        8.4.1 - Hardware Architectures and Databases 225
        
        8.4.2 - Processor Options 228
        
        8.4.3 - Memory Options 229
        
        8.4.4 - Storage Options 229
        
        8.4.5 - Network Options 231
      - 8.5 - Database Options 232
        
        8.5.1 - tempdb Options 232
        
        8.5.2 - Partitioning 233
        
        8.5.3 - Filegroups 234
        
        8.5.4 - Data Compression 234
      - 8.6 - Setting up the Data Warehouse 235
        
        8.6.1 - Setting up the Stage Area 235
        
        8.6.1.1 - Hardware Considerations for Stage Area 236
        
        8.6.1.2 - Stage Database Setup 236
        
        8.6.2 - Setting up the Data Vault 239
        
        8.6.2.1 - Hardware Considerations for Data Vault Layer 240
        
        8.6.2.2 - Backing Up the Data Vault 240
        
        8.6.2.3 - Data Vault Database Setup 241
        
        8.6.3 - Setting up Information Marts 244
        
        8.6.3.2 - Information Mart Database Setup 245
        
        8.6.3.1 - Hardware Considerations for Information Marts 245
        
        8.6.4 - Setting up the Meta, Metrics, and Error Marts 248
        
        8.6.4.2 - Meta, Metrics, and Error Marts Database Setup 249
        
        8.6.4.1 - Hardware Considerations for Meta, Metrics, and Error Marts 249
        
        References 250
      - Chapter 9 - Master Data Management 251
        
        9.1 - Definitions 251
        
        9.1.1 - Master Data 251
        
        9.1.2 - Data Management 252
        
        9.1.3 - Master Data Management 252
        
        9.2 - Master Data Management Goals 253
        
        9.3 - Drivers for Managing Master Data 254
        
        9.4 - Operational vs. Analytical Master Data Management 257
        
        9.5 - Master Data Management as an Enabler for Managed Self-Service BI 260
        
        9.6 - Master Data Management as an Enabler for Total Quality Management 261
        
        9.6.1 - MDS Object Model 263
        
        9.6.1.1 - Models 263
        
        9.6.1.2 - Entities 267
        
        9.6.1.4 - Attributes 268
        
        9.6.1.3 - Members 268
        
        9.6.1.5 - Attribute Groups 270
        
        9.6.1.6 - Hierarchies 270
        
        9.6.2 - Master Data Manager 271
        
        9.6.3 - Explorer 272
        
        9.6.3.1 - Microsoft Excel and MDS 273
        
        9.6.4 - Version Management 274
        
        9.6.5 - Integration Management 275
        
        9.6.6 - System Administration 276
        
        9.6.7 - User and Group Permissions 277
        
        9.7 - Creating a Model 278
        
        9.7.1 - Creating Entities 280
        
        9.7.2 - Creating Business Rules 283
        
        9.8 - Importing a Model 285
        
        9.9 - Integrating MDS with the Data Vault and Operational Systems 287
        
        9.9.1 - Stage Tables 289
        
        9.9.1.1 - Using T-SQL to Stage Master Data into Microsoft Master Data Services 290
        
        9.9.1.2 - Using SQL Server Integration Services to Stage Master Data into Microsoft Master Data Services 293
        
        9.9.2 - Subscription Views 300
        
        References 304
      - Chapter 10 - Metadata Management 305
        
        10.1 - What Is Metadata? 305
        
        10.1.1 - Business Metadata 306
        
        10.1.2 - Technical Metadata 308
        
        10.1.3 - Process Execution Metadata 309
        
        10.2 - Implementing the Meta Mart 309
        
        10.2.1 - SQL Server BI Metadata Toolkit 310
        
        10.2.2 - Naming Conventions 314
        
        10.2.3 - Capturing Source System Definitions 318
        
        10.2.4 - Capturing Hard Rules 320
        
        10.2.5 - Capturing Metadata for the Staging Area 322
        
        10.2.6 - Capturing Requirements to Source Tables 323
        
        10.2.7 - Capturing Source Tables to Data Vault Tables 324
        
        10.2.7.1 - Metadata for Loading Hub Entities 325
        
        10.2.7.2 - Metadata for Loading Link Entities 326
        
        10.2.7.3 - Metadata for Loading Satellite Entities on Hubs 329
        
        10.2.7.4 - Metadata for Loading Satellite Entities on Links 332
        
        10.2.8 - Capturing Soft Rules 333
        
        10.2.9 - Capturing Data Vault Tables to Information Mart Table Mappings 337
        
        10.2.10 - Capturing Requirements to Information Mart Tables 339
        
        10.2.11 - Capturing Access Control Lists and Other Security Measures 340
        
        10.3 - Implementing the Metrics Vault 342
        
        10.3.1 - Capturing Performance Data in SQL Server Integration Services 345
        
        10.4 - Implementing the Metrics Mart 355
        
        10.5 - Implementing the Error Mart 357
        
        10.5.1 - Capturing Erroneous Data in SQL Server Integration Services 358
        
        References 364
      - Chapter 11 - Data Extraction 365
        
        11.1 - Purpose of Staging Area 365
        
        11.2 - Hashing in the Data Warehouse 369
        
        11.2.1 - Hash Functions Revisited 372
        
        11.2.2 - Applying Hash Functions to Data 373
        
        11.2.3 - Risks of Using Hash Functions 377
        
        11.2.3.1 - Hash Collisions 378
        
        11.2.3.2 - Storage Requirements 380
        
        11.2.3.3 - Tool and Platform Compatibility 380
        
        11.2.3.4 - Maintenance 381
        
        11.2.3.5 - Performance 382
        
        11.2.4 - Hashing Business Keys 382
        
        11.2.5 - Hashing for Change Detection 386
        
        11.2.5.1 - Maintaining the Hash Diffs 389
        
        11.3 - Purpose of the Load Date 392
        
        11.4 - Purpose of the Record Source 394
        
        11.5 - Types of Data Sources 395
        
        11.6 - Sourcing Flat Files 397
        
        11.6.1 - Control Flow 397
        
        11.6.2 - Flat File Connection Manager 402
        
        11.6.3 - Data Flow 405
        
        11.7 - Sourcing Historical Data 421
        
        11.7.1 - SSIS Example for Sourcing Historical Data 423
        
        11.8 - Sourcing the Sample Airline Data 425
        
        11.8.1 - Authenticating with Google Drive 426

Author's affiliation

Dan Linstedt: Founder and Principle of Empowered Holdings,
Michael Olschimke: BI Consultant, Dörffler + Partner GmbH, Hanno

Implementation Guide for Microsoft SQL Server 2014

Description

TOC

Reviews (0)

Author's affiliation