Foreword xxvii
Introduction xxix
Part 1 Requirements, Realities, and Architecture 1
Chapter 1 Defining Business Requirements 3
The Most Important Determinant of Long-Term Success 5
Adventure Works Cycles Introduction 6
Uncovering Business Value 6
Obtaining Sponsorship 7
Defining Enterprise-Level Business Requirements 8
Prioritizing the Business Requirements 22
Revisiting the Project Planning 25
Gathering Project-Level Requirements 26
Summary 28
Chapter 2 Designing the Business Process Dimensional Model 29
Dimensional Modeling Concepts and Terminology 30
Facts 31
Dimensions 33
Bringing Facts and Dimensions Together 34
The Bus Matrix, Conformed Dimensions, and Drill Across 36
Additional Design Concepts and Techniques 38
Surrogate Keys 38
Slowly Changing Dimensions 39
Dates 42
Degenerate Dimensions 43
Snowflaking 43
Many-to-Many or Multivalued Dimensions 44
Hierarchies 47
Aggregate Dimensions 49
Junk Dimensions 51
The Three Fact Table Types 52
Aggregates 53
The Dimensional Modeling Process 54
Preparation 55
Data Profiling and Research 60
Building Dimensional Models 63
Developing the Detailed Dimensional Model 66
Testing and Refining the Model 68
Reviewing and Validating the Model 68
Case Study: The Adventure Works Cycles Orders Dimensional Model 69
The Orders Fact Table 69
The Dimensions 69
Identifying Dimension Attributes and Facts for the Orders Business Process 72
The Final Draft of the Initial Orders Model 74
Detailed Orders Dimensional Model Development 75
Final Dimensional Model 77
Summary 77
Chapter 3 The Toolset 79
The Microsoft DW/BI Toolset 80
Why Use the Microsoft Toolset? 82
Architecture of a Microsoft DW/BI System 83
Why Analysis Services? 84
Why a Relational Store? 86
ETL Is Not Optional 86
The Role of Master Data Services 88
Delivering BI Applications 88
Overview of the Microsoft Tools 89
Which Products Do You Need? 90
SQL Server Development and Management Tools 92
Summary 97
Chapter 4 System Setup 99
System Sizing Considerations 100
Calculating Data Volumes 101
Determining Usage Complexity 102
Estimating Simultaneous Users 104
Assessing System Availability Requirements 105
How Big Will It Be? 105
System Configuration Considerations 105
Memory 106
Monolithic or Distributed? 106
Storage System Considerations 110
Processors 113
Setting Up for High Availability 114
Software Installation and Configuration 115
Development Environment Software Requirements 116
Test and Production Software Requirements 120
Operating Systems 122
SQL Server Relational Database Setup 122
Analysis Services Setup 126
Integration Services Setup 129
Reporting Services Setup 130
Summary 131
Part 2 Building and Populating the Databases 133
Chapter 5 Creating the Relational Data Warehouse 135
Getting Started 136
Complete the Physical Design 137
Surrogate Keys 138
String Columns 138
To Null, or Not to Null? 140
Housekeeping Columns 140
Table and Column Extended Properties 142
Define Storage and Create Constraints and Supporting Objects 142
Create Files and Filegroups 142
Data Compression 144
Entity and Referential Integrity Constraints 145
Initial Indexing and Database Statistics 147
Aggregate Tables 150
Create Table Views 151
Insert an Unknown Member Row 152
Example CREATE TABLE Statement 152
Partitioned Tables 153
Finishing Up 163
Staging Tables 163
Metadata Setup 163
Summary 164
Chapter 6 Master Data Management 165
Managing Master Reference Data 166
Incomplete Attributes 167
Data Integration 168
Systems Integration 170
Master Data Management Systems and the Data Warehouse 171
Introducing SQL Server Master Data Services 171
Model Definition Features 172
Data Management Features 174
User Interface: Exploring and Managing the Master Data 174
Importing and Updating Data 176
Exporting Data 177
Full Versioning of All Attributes 179
Creating a Simple Application 179
Summary 186
Chapter 7 Designing and Developing the ETL System 187
Round Up the Requirements 188
Develop the ETL Plan 191
Introducing SQL Server Integration Services 192
Control Flow and Data Flow 194
SSIS Package Architecture 197
The Major Subsystems of ETL 198
Extracting Data 199
Subsystem 1: Data Profiling 199
Subsystem 2: Change Data Capture System 200
Subsystem 3: Extract System 202
Cleaning and Conforming Data 206
Subsystem 4: Data Cleaning System 206
Subsystem 5: Error Event Schema 214
Subsystem 6: Audit Dimension Assembler 215
Subsystem 7: Deduplication System 216
Subsystem 8: Conforming System 217
Delivering Data for Presentation 218
Subsystem 9: Slowly Changing Dimension Manager 218
Subsystem 10: Surrogate Key Generator 223
Subsystem 11: Hierarchy Manager 223
Subsystem 12: Special Dimensions Manager 224
Subsystem 13: Fact Table Builders 225
Subsystem 14: Surrogate Key Pipeline 229
Subsystem 15: Multi-Valued Dimension Bridge Table Builder 235
Subsystem 16: Late Arriving Data Handler 235
Subsystem 17: Dimension Manager 238
Subsystem 18: Fact Provider System 238
Subsystem 19: Aggregate Builder 239
Subsystem 20: OLAP Cube Builder 239
Subsystem 21: Data Propagation Manager 240
Managing the ETL Environment 240
Summary 243
Chapter 8 The Core Analysis Services OLAP Database 245
Overview of Analysis Services OLAP 247
Why Use Analysis Services? 247
Why Not Analysis Services? 249
Designing the OLAP Structure 250
Planning 251
Getting Started 253
Create a Project and a Data Source View 255
Dimension Designs 257
Creating and Editing Dimensions 261
Creating and Editing the Cube 274
Physical Design Considerations 291
Understanding Storage Modes 293
Developing the Partitioning Plan 294
Designing Performance Aggregations 296
Planning for Deployment 298
Processing the Full Cube 299
Developing the Incremental Processing Plan 299
Summary 304
Chapter 9 Design Requirements for Real-Time BI 305
Real-Time Triage 306
What Does Real-Time Mean? 306
Who Needs Real Time? 307
Real-Time Tradeoffs 308
Scenarios and Solutions 311
Executing Reports in Real Time 313
Serving Reports from a Cache 313
Creating an ODS with Mirrors and Snapshots 314
Creating an ODS with Replication 314
Building a BizTalk Application 315
Building a Real-Time Relational Partition 315
Querying Real-Time Data in the Relational Database 317
Using Analysis Services to Query Real-Time Data 318
Summary 319
Part 3 Developing the BI Applications 321
Chapter 10 Building BI Applications in Reporting Services 323
A Brief Overview of BI Applications 324
Types of BI Applications 325
The Value of Business Intelligence Applications 326
A High-Level Architecture for Reporting 328
Reviewing Business Requirements for Reporting 328
Examining the Reporting Services Architecture 330
Using Reporting Services as a Standard Reporting Tool 332
Reporting Services Assessment 339
The Reporting System Design and Development Process 340
Reporting System Design 341
Reporting System Development 348
Building and Delivering Reports 351
Planning and Preparation 351
Creating Reports 354
Reporting Operations 368
Ad Hoc Reporting Options 369
The Report Model 370
Shared Datasets 371
Report Parts 371
Summary 372
Chapter 11 PowerPivot and Excel 375
Using Excel for Analysis and Reporting 376
The PowerPivot Architecture: Excel on Steroids 378
Creating and Using PowerPivot Databases 380
Getting Started 381
PowerPivot Table Design 381
Creating Analytics with PowerPivot 385
Observations and Guidelines on PowerPivot for Excel 392
PowerPivot for SharePoint 394
The PowerPivot SharePoint User Experience 394
Server-Level Resources 397
PowerPivot Monitoring and Management 397
PowerPivotâs Role in a Managed DW/BI Environment 400
Summary 401
Chapter 12 The BI Portal and SharePoint 403
The BI Portal 404
Planning the BI Portal 405
Impact on Design 406
Business Process Categories 407
Additional Functions 408
Building the BI Portal 409
Using SharePoint as the BI Portal 411
Architecture and Concepts 412
Setting Up SharePoint 417
Summary 426
Chapter 13 Incorporating Data Mining 429
Defining Data Mining 430
Basic Data Mining Terminology 432
Business Uses of Data Mining 433
Roles and Responsibilities 440
SQL Server Data Mining Architecture Overview 440
The Data Mining Design Environment 442
Build, Deploy, and Process 442
Accessing the Mining Models 443
Integration Services and Data Mining 443
Additional Features 444
Architecture Summary 445
Microsoft Data Mining Algorithms 445
Decision Trees 446
Na¯ve Bayes 447
Clustering 448
Sequence Clustering 448
Time Series 449
Association 449
Neural Network 449
The Data Mining Process 450
The Business Phase 451
The Data Mining Phase 453
The Operations Phase 460
Metadata 462
Data Mining Examples 463
Case Study: Categorizing Cities 463
Case Study: Product Recommendations 472
Summary 488
Part 4 Deploying and Managing the DW/BI System 491
Chapter 14 Designing and Implementing Security 493
Identifying the Security Manager 494
Securing the Hardware and Operating System 495
Securing the Operating System 495
Using Windows Integrated Security 496
Securing the Development Environment 497
Securing the Data 498
Providing Open Access for Internal Users 498
Itemizing Sensitive Data 500
Securing Various Types of Data Access 500
Securing the Components of the DW/BI System 502
Reporting Services Security 502
Analysis Services Security 505
Relational DW Security 514
Integration Services Security 520
Usage Monitoring 521
Summary 521
Chapter 15 Metadata Plan 523
Metadata Basics 524
The Purpose of Metadata 524
Metadata Categories 525
The Metadata Repository 526
Metadata Standards 526
SQL Server 2008 R2 Metadata 527
Cross-Tool Components 528
Relational Engine Metadata 532
Analysis Services 532
Integration Services 533
Reporting Services 533
Master Data Services 534
SharePoint 534
External Metadata Sources 534
Looking to the Future 535
A Practical Metadata Approach 535
Creating the Metadata Strategy 536
Business Metadata Reporting 538
Process Metadata Reporting 541
Technical Metadata Reporting 542
Ongoing Metadata Management 543
Summary 543
Chapter 16 Deployment 545
Setting Up the Environments 546
Testing 550
Development Testing 551
System Testing 555
Data Quality Assurance Testing 557
Performance Testing 559
Usability Testing 562
Testing Summary 563
Deploying to Production 564
Relational Database Deployment 565
Integration Services Package Deployment 567
Analysis Services Database Deployment 568
Reporting Services Report Deployment 571
Master Data Services Deployment 572
Data Warehouse and BI Documentation 573
Core Descriptions 573
Additional Documentation 575
User Training 576
User Support 579
Desktop Readiness and Configuration 580
Summary 581
Chapter 17 Operations and Maintenance 583
Providing User Support 584
Maintaining the BI Portal 585
Extending the BI Applications 586
System Management 587
Governing the DW/BI System 588
Performance Monitoring 593
Usage Monitoring 600
Managing Disk Space 602
Service and Availability Management 603
Performance Tuning the DW/BI System 604
Backup and Recovery 606
Executing the ETL Packages 611
Summary 611
Chapter 18 Present Imperatives and Future Outlook 613
Growing the DW/BI System 613
Lifecycle Review with Common Problems 615
Phase I â" âRequirements, Realities, Plans, and Designs 616
Phase II â" âDeveloping the Databases 616
Phase III â" âDeveloping the BI Applications and Portal Environment 617
Phase IV â" âDeploying and Managing the DW/BI System 618
Iteration and Growth 618
What We Like in the Microsoft BI Toolset 619
Future Directions: Room for Improvement 620
Conclusion 623
Index 625
