Building a Data Warehouse: With Examples in SQL Server

Building a Data Warehouse: With Examples in SQL Server......Page 1
Contents at a Glance......Page 7
Contents......Page 9
About the Author......Page 15
Preface......Page 17
What Is a Data Warehouse?......Page 19
Retrieves Data......Page 22
Consolidates Data......Page 23
Periodically......Page 24
Dimensional Data Store......Page 25
Normalized Data Store......Page 26
History......Page 28
Query......Page 29
Business Intelligence......Page 30
Reporting......Page 31
Other Analytical Activities......Page 32
Updated in Batches......Page 33
Other Definitions......Page 34
Business Intelligence......Page 35
Customer Relationship Management......Page 36
Data Mining......Page 37
Master Data Management (MDM)......Page 38
Customer Data Integration......Page 41
Unstructured Data......Page 42
Search......Page 43
Service-Oriented Architecture (SOA)......Page 44
Summary......Page 45
Data Flow Architecture......Page 47
Single DDS......Page 51
NDS + DDS......Page 53
ODS + DDS......Page 56
Federated Data Warehouse......Page 57
System Architecture......Page 60
Case Study......Page 62
Summary......Page 65
Waterfall Methodology......Page 67
Iterative Methodology......Page 72
Summary......Page 77
Identifying Business Areas......Page 79
Understanding Business Operations......Page 80
Defining Functional Requirements......Page 81
Defining Nonfunctional Requirements......Page 83
Conducting a Data Feasibility Study......Page 85
Summary......Page 88
Designing the Dimensional Data Store......Page 89
Dimension Tables......Page 94
Date Dimension......Page 95
Slowly Changing Dimension......Page 98
Product, Customer, and Store Dimensions......Page 101
Subscription Sales Data Mart......Page 107
Supplier Performance Data Mart......Page 112
CRM Data Marts......Page 114
Data Hierarchy......Page 119
Source System Mapping......Page 120
Designing the Normalized Data Store......Page 124
Summary......Page 129
Hardware Platform......Page 131
Storage Considerations......Page 138
Configuring Databases......Page 141
Creating DDS Database Structure......Page 146
Creating the Normalized Data Store......Page 157
Using Views......Page 175
Summary Tables......Page 179
Partitioning......Page 180
Indexes......Page 184
Summary......Page 189
Introduction to ETL......Page 191
ETL Approaches and Architecture......Page 192
General Considerations......Page 195
Whole Table Every Time......Page 198
Incremental Extract......Page 199
Fixed Range......Page 203
Related Tables......Page 204
Extracting File Systems......Page 205
Extracting Other Source Types......Page 208
Extracting Data Using SSIS......Page 209
Memorizing the Last Extraction Timestamp......Page 218
Extracting from Files......Page 226
Summary......Page 232
Populating the Data Warehouse......Page 233
Stage Loading......Page 234
Data Firewall......Page 236
Populating NDS......Page 237
Using SSIS to Populate NDS......Page 246
Upsert Using SQL and Lookup......Page 253
Normalization......Page 260
Practical Tips on SSIS......Page 267
Populating DDS Dimension Tables......Page 268
Populating DDS Fact Tables......Page 284
Batches, Mini-batches, and Near Real-Time ETL......Page 287
Pushing the Data In......Page 288
Summary......Page 289
Assuring Data Quality......Page 291
Data Quality Process......Page 292
Data Cleansing and Matching......Page 295
Cross-checking with External Sources......Page 308
Data Quality Rules......Page 309
Action: Reject, Allow, Fix......Page 311
Logging and Auditing......Page 314
Data Quality Reports and Notifications......Page 316
Summary......Page 318
Metadata in Data Warehousing......Page 319
Data Definition and Mapping Metadata......Page 321
Data Structure Metadata......Page 326
Source System Metadata......Page 331
ETL Process Metadata......Page 336
Data Quality Metadata......Page 338
Audit Metadata......Page 341
Usage Metadata......Page 342
Maintaining Metadata......Page 343
Summary......Page 345
Data Warehouse Reports......Page 347
When to Use Reports and When Not to Use Them......Page 350
Report Wizard......Page 352
Report Layout......Page 358
Report Parameters......Page 360
Grouping, Sorting, and Filtering......Page 369
Simplicity......Page 374
Spreadsheets......Page 375
Multidimensional Database Reports......Page 380
Deploying Reports......Page 384
Managing Report Security......Page 388
Managing Report Subscriptions......Page 390
Managing Report Execution......Page 392
Summary......Page 393
What a Multidimensional Database Is......Page 395
Online Analytical Processing......Page 398
Creating a Multidimensional Database......Page 399
Processing a Multidimensional Database......Page 406
Querying a Multidimensional Database......Page 412
Administering a Multidimensional Database......Page 414
Multidimensional Database Security......Page 415
Processing Cubes......Page 417
Backup and Restore......Page 423
Summary......Page 427
Using Data Warehouse for Business Intelligence......Page 429
Business Intelligence Reports......Page 430
Business Intelligence Analytics......Page 431
Business Intelligence Data Mining......Page 434
Business Intelligence Dashboards......Page 450
Business Intelligence Alerts......Page 455
Business Intelligence Portal......Page 456
Summary......Page 457
Using Data Warehouse for Customer Relationship Management......Page 459
Single Customer View......Page 460
Campaign Segmentation......Page 465
Permission Management......Page 468
Delivery and Response Data......Page 472
Customer Analysis......Page 478
Customer Support......Page 481
Personalization......Page 482
Customer Loyalty Scheme......Page 483
Summary......Page 484
Customer Data Integration......Page 485
Unstructured Data......Page 488
Search in Data Warehousing......Page 492
Summary......Page 494
Testing Your Data Warehouse......Page 495
Data Warehouse ETL Testing......Page 496
Functional Testing......Page 498
Performance Testing......Page 500
Security Testing......Page 503
User Acceptance Testing......Page 504
Migrating to Production......Page 505
Summary......Page 507
Data Warehouse Administration......Page 509
Monitoring Data Warehouse ETL......Page 510
Monitoring Data Quality......Page 513
Managing Security......Page 516
Managing Databases......Page 517
Making Schema Changes......Page 519
Summary......Page 521
Second Normal Form......Page 523
Boyce-Codd Normal Form......Page 524
Higher Normal Forms......Page 525
Index......Page 527