I can tell - this author has built real data warehouses. This book has so many real world application concepts, distilled into less than 500 pages. I.E. it is not not a bible book, that which puts you to sleep, although it is a great reference book. It is Inmon and Kimball agnostic - huge benefit here. While the DW toolkit books are great, they are only great for Kimball warehouses. Being in the Inmon camp, I appreciate the authors' coverage of the concepts of the Operational Data Store, and Normalized Data Store. This book should be the first book you read in building a data warehouse. Although the specific sql code examples show SQL Server code, all concepts can be applied to Oracle, etc. Only a couple complaints. There is a lack of coverage for modeling localization, i.e. modeling for local language requirements of the users. The author should also mention that Data Modeling tools, such as ErWin, PowerDesigner (best IMO), or ER/Studio can really enhance metadata modeling, and documentation, although he covers metadata very well. That aside, I still give this book a 5 star rating, in lieu of all the theory books out there, which lack real world application examples.
Author(s): Vincent Rainardi
Series: Expert's Voice
Edition: 1
Publisher: Apress
Year: 2007
Language: English
Pages: 531
Building a Data Warehouse: With Examples in SQL Server......Page 1
Contents at a Glance......Page 3
Contents......Page 4
About the Author......Page 10
Preface......Page 11
What Is a Data Warehouse?......Page 13
Retrieves Data......Page 16
Consolidates Data......Page 17
Periodically......Page 18
Dimensional Data Store......Page 19
Normalized Data Store......Page 20
History......Page 22
Query......Page 23
Business Intelligence......Page 24
Reporting......Page 25
Other Analytical Activities......Page 26
Updated in Batches......Page 27
Other Definitions......Page 28
Business Intelligence......Page 29
Customer Relationship Management......Page 30
Data Mining......Page 31
Master Data Management (MDM)......Page 32
Customer Data Integration......Page 35
Unstructured Data......Page 36
Search......Page 37
Service-Oriented Architecture (SOA)......Page 38
Summary......Page 39
Data Flow Architecture......Page 40
Single DDS......Page 44
NDS + DDS......Page 46
ODS + DDS......Page 49
Federated Data Warehouse......Page 50
System Architecture......Page 53
Case Study......Page 55
Summary......Page 58
Waterfall Methodology......Page 59
Iterative Methodology......Page 64
Summary......Page 69
Identifying Business Areas......Page 70
Understanding Business Operations......Page 71
Defining Functional Requirements......Page 72
Defining Nonfunctional Requirements......Page 74
Conducting a Data Feasibility Study......Page 76
Summary......Page 79
Designing the Dimensional Data Store......Page 80
Dimension Tables......Page 85
Date Dimension......Page 86
Slowly Changing Dimension......Page 89
Product, Customer, and Store Dimensions......Page 92
Subscription Sales Data Mart......Page 98
Supplier Performance Data Mart......Page 103
CRM Data Marts......Page 105
Data Hierarchy......Page 110
Source System Mapping......Page 111
Designing the Normalized Data Store......Page 115
Summary......Page 120
Hardware Platform......Page 121
Storage Considerations......Page 128
Configuring Databases......Page 131
Creating DDS Database Structure......Page 136
Creating the Normalized Data Store......Page 147
Using Views......Page 165
Summary Tables......Page 169
Partitioning......Page 170
Indexes......Page 174
Summary......Page 179
Introduction to ETL......Page 181
ETL Approaches and Architecture......Page 182
General Considerations......Page 185
Whole Table Every Time......Page 188
Incremental Extract......Page 189
Fixed Range......Page 193
Related Tables......Page 194
Extracting File Systems......Page 195
Extracting Other Source Types......Page 198
Extracting Data Using SSIS......Page 199
Memorizing the Last Extraction Timestamp......Page 208
Extracting from Files......Page 216
Summary......Page 222
Populating the Data Warehouse......Page 223
Stage Loading......Page 224
Data Firewall......Page 226
Populating NDS......Page 227
Using SSIS to Populate NDS......Page 236
Upsert Using SQL and Lookup......Page 243
Normalization......Page 250
Practical Tips on SSIS......Page 257
Populating DDS Dimension Tables......Page 258
Populating DDS Fact Tables......Page 274
Batches, Mini-batches, and Near Real-Time ETL......Page 277
Pushing the Data In......Page 278
Summary......Page 279
Assuring Data Quality......Page 281
Data Quality Process......Page 282
Data Cleansing and Matching......Page 285
Cross-checking with External Sources......Page 298
Data Quality Rules......Page 299
Action: Reject, Allow, Fix......Page 301
Logging and Auditing......Page 304
Data Quality Reports and Notifications......Page 306
Summary......Page 308
Metadata in Data Warehousing......Page 309
Data Definition and Mapping Metadata......Page 311
Data Structure Metadata......Page 316
Source System Metadata......Page 321
ETL Process Metadata......Page 326
Data Quality Metadata......Page 328
Audit Metadata......Page 331
Usage Metadata......Page 332
Maintaining Metadata......Page 333
Summary......Page 335
Data Warehouse Reports......Page 337
When to Use Reports and When Not to Use Them......Page 340
Report Wizard......Page 342
Report Layout......Page 348
Report Parameters......Page 350
Grouping, Sorting, and Filtering......Page 359
Simplicity......Page 364
Spreadsheets......Page 365
Multidimensional Database Reports......Page 370
Deploying Reports......Page 374
Managing Report Security......Page 378
Managing Report Subscriptions......Page 380
Managing Report Execution......Page 382
Summary......Page 383
What a Multidimensional Database Is......Page 385
Online Analytical Processing......Page 388
Creating a Multidimensional Database......Page 389
Processing a Multidimensional Database......Page 396
Querying a Multidimensional Database......Page 402
Administering a Multidimensional Database......Page 404
Multidimensional Database Security......Page 405
Processing Cubes......Page 407
Backup and Restore......Page 413
Summary......Page 417
Using Data Warehouse for Business Intelligence......Page 419
Business Intelligence Reports......Page 420
Business Intelligence Analytics......Page 421
Business Intelligence Data Mining......Page 424
Business Intelligence Dashboards......Page 440
Business Intelligence Alerts......Page 445
Business Intelligence Portal......Page 446
Summary......Page 447
Using Data Warehouse for Customer Relationship Management......Page 449
Single Customer View......Page 450
Campaign Segmentation......Page 455
Permission Management......Page 458
Delivery and Response Data......Page 462
Customer Analysis......Page 468
Customer Support......Page 471
Personalization......Page 472
Customer Loyalty Scheme......Page 473
Summary......Page 474
Customer Data Integration......Page 475
Unstructured Data......Page 478
Search in Data Warehousing......Page 482
Summary......Page 484
Testing Your Data Warehouse......Page 485
Data Warehouse ETL Testing......Page 486
Functional Testing......Page 488
Performance Testing......Page 490
Security Testing......Page 493
User Acceptance Testing......Page 494
Migrating to Production......Page 495
Summary......Page 497
Data Warehouse Administration......Page 499
Monitoring Data Warehouse ETL......Page 500
Monitoring Data Quality......Page 503
Managing Security......Page 506
Managing Databases......Page 507
Making Schema Changes......Page 509
Summary......Page 511
Second Normal Form......Page 513
Boyce-Codd Normal Form......Page 514
Higher Normal Forms......Page 515
Index......Page 517
