Chapter 1 Benefits of Data Warehousing 1
About This Chapter 1
Before You Begin 1
Lesson 1:Fundamental Data Warehousing Concepts 2
What Is a Data Warehouse? 2
What Is a Data Mare? 3
Lesson Summary 4
Lesson 2:Benefits of Data Warehousing 5
The Ability to Access Enterprisewide Data 5
The Ability to Have Consistent Data 6
The Ability to Perform Analysis Quickly 6
Recognition of Redundancy of Effort 6
Discovery of Gaps in Business Knowledge or Business Processes 6
Decreased Administration Costs 6
Empowering Members of an Enterprise by Providing Them with Information 7
Lesson Summary 7
Lesson 3:Data Warehousing Features of Microsoft SQL Server 7.0 8
Microsoft Data Warehousing Framework 8
SQL Server 7.0 Data Warehousing Tools 9
Lesson Summary 12
Review 13
Chapter 2 Applications of Data Warehousing 15
About This Chapter 15
Before You Begin 15
Lesson 1:What Is a Data Warehouse? 16
History of Corporate Data Analysis 16
What Is Online Analytical Processing? 17
OLAP Data Characteristics 19
Lesson Summary 20
Lesson 2:Data Warehousing Life Cycle 21
Introduction to the Northwind Database 21
The Data Warehousing Life Cycle 23
Source OLTP Systems 23
Transformation Tools 23
The Data Warehouse and Data Marts 23
Cubes 23
Lesson Summary 25
Lesson 3:Business Scenarios 26
Business Scenarios 26
Sales Tracking 26
Parts Tracking 27
Lesson Summary 28
Review 29
Chapter 3 Challenges of Data Warehousing 31
About This Chapter 31
Before You Begin 31
Lesson 1:The Data Warehousing Analysis Process 32
Comparison of System Architectures 32
Business Analysis 35
Business Drivers 36
Lesson Summary 51
Lesson 2:An Examination of Project Management Approaches 54
A Golden Rule of Data Warehousing 54
Data Warehouse or Data Mart? 54
Data Warehousing Issues 55
Effective Development Practices 56
Building the Team 57
Lesson Summary 59
Lesson 3:Identifying Technical Requirements 60
Lesson Summary 62
Review 63
Chapter 4 Developing the Logical Design 65
About This Chapter 65
Before You Begin 66
Lesson 1:Review of OLTP System Design 67
Entity-Relationship Diagrams 67
Database Table Normalization 69
Normalization Example 70
OLTP System Design Issues 72
Lesson Summary 73
Lesson 2:OLAP System Design 74
Designing OLAP Systems 75
Dimensional Modeling:The Star(Join)Schema 76
Dimensional Hierarchies 78
Consolidated vs.Snowflaked Hierarchies 82
Information Object Dimension Tables 82
Dimensional and E-R Models 84
Lesson Summary 85
Lesson 3:Dimensional Schema Design 86
Dimensional Schema Design Steps 86
Lesson Summary 92
Lesson 4:Dimensions 94
Establishing Dimensions 94
Lesson Summary 99
Lesson 5:Aggregations 100
Designing Aggregations 100
Aggregate Characteristics 102
Aggregate Design Issues 103
Aggregation Example 104
Lesson Summary 106
Review 107
Chapter 5 Defining the Technical Architecture for a Solution 109
About This Chapter 109
Before You Begin 109
Lesson 1:Microsoft Data Warehousing Framework 110
Data Warehousing Framework Components 111
Microsoft Components 112
Data Warehousing Framework 114
OLE DB,ODBC,and OLE DB for OLAP 115
Data Transformation Services and Replication 116
OLAP Services 116
PivotTable Service 118
ADO and ADO MD 118
Microsoft English Query 119
Microsoft Repository 119
Lesson Summary 120
Lesson 2:SQL Server 7.0 Data Warehousing Features 121
Types of Servers 121
Backup and Restore Features 122
Query Processor Enhancements 123
Other Enhancements 124
Lesson Summary 124
Lessonm 3:Setting Up the Data Warehouse Source Database 125
Estimating Size of the Data Warehouse 125
Minimizing Fact Table Size 128
Balancing Size and Performance 128
Creating a Database 129
Creating Tables 131
Creating Indexes 137
Lesson Summary 140
Review 141
Chapter 6 SQL Server Data Services 143
About This Chapter 143
Before You Begin 143
Lesson 1:Overview of SQL Server Data Services 144
Transferring and Transforming Data 144
Loading the Data Warehouse 146
OLE DB,ODBC,and OLE DB for OLAP 148
Tools for Transferring Data in SQL Server 151
Other Tools for Transferring Data 153
Lesson Summary 153
Lesson 2:Introduction to Data Transformation Services 154
Overview of DTS 154
The DTS Process 155
DTS Tools 156
Lesson Summary 161
Review 162
Chapter 7 Replication 163
About This Chapter 163
Before You Begin 163
Lesson 1:Introduction to Distributed Data 164
The Need for Distributed Data 164
Considerations for Distributing Data 164
Methods of Distributing Data with SQL Server 166
Lesson Summary 168
Lesson 2:Introduction to SQL Server Replication 169
The Publisher/Subscriber Metaphor 169
Publications and Articles 170
Filtering Data 171
Subscriptions 173
Lesson Summary 175
Lesson 3:SQL Server Replication Types 176
Snapshot Replication 176
The Snapshot Replication Process 177
Transactional Replication 177
The Transactional Replication Process 178
The Immediate Updating Subscribers Option 178
Merge Replication 180
SQL Server Replication Agents 182
Lesson Summary 183
Lesson 4:Physical Replication Models 185
Overview of the Replication Models 185
Combining Replication Models and Types 186
Using Replication to Update Warehoused Data 191
Selecting a Data Population Mechanism 193
Replication Exercises 194
Lesson Summary 201
Review 202
Chapter 8 Advanced DTS 203
About This Chapter 203
Before You Begin 203
Lesson 1:Planning and Creating DTS Workflows 204
Restructuring and Mapping Data 204
Creating a DTS Package 205
Defining Workflows 207
Executing and Scheduling a DTS Package 215
Lesson Summary 232
Lesson 2:Using Data-Driven Queries to Update the Data Warehouse 233
What Is a Data-Driven Query? 233
When to Use a Data-Driven Query 233
Performance Issues 234
Data-Driven Query Examples 234
Creating Data-Driven Query Tasks 235
Lesson Summary 241
Lesson 3:DTS Programming 242
DTS Object Model 242
DTS Package Object Internals 243
Transacting DTS Packages 245
DTS Programming Samples 245
Lesson Summary 245
Review 246
Chapter 9 Microsoft SQL Server OLAP Services 247
About This Chapter 247
Before You Begin 247
Lesson 1:Moving from OLAP Data Warehouses to Cubes 249
Determining Requirements 249
Designing and Building the OLAP Data Warehouse Database 250
Extracting,Cleaning,and Loading Data 251
Multidimensional Structure(Cube) 252
Lesson Summary 255
Lesson 2:Microsoft SQL Server OLAP Services 256
OLAP Services Features 256
OLAP Services COM Interface 259
OLAP Services System Requirements 261
Lesson Summary 263
Lesson 3:Designing and Building Cubes 264
Building Dimensions 264
Creating Calculated Members 265
Designing and Building Aggregations 267
How OLAP Services Handles Data Explosion 269
Lesson Summary 275
Lesson 4:Managing Cubes Using Storage TYpes and Partitions 276
Storing in a MOLAP Structure 276
Storing in a ROLAP Structure 277
Storing in a HOLAP Structure 279
Comparing Storage Structures 280
MOLAP vs.ROLAP 280
Processing,Updating,and Refreshing Cubes 282
Virtual Cubes 283
Partitions 286
Security 288
Lesson Summary 290
Review 291
Chapter lO Data Analysis Tools and Architecture 293
About This Chapter 293
Before You Begin 293
Lesson 1:Data Analysis Concepts 295
Drilling into Data 295
Data Mining 296
Client and Server Caching 296
Analyzing Data Offline 297
Lesson Summary 298
Lesson 2:Data Analysis Tools 299
OLAP Manager 299
Learn about Data Warehouse Concepts 302
Excel 2000 302
Microsoft English Query 309
Third-Party Products 310
Lesson Summary 310
Lesson 3:OLAP Services and PivotTable Service Architecture 311
Microsoft SQL Server OLAP Services Architecture 311
PivotTable Service 314
OLAP Services:Client/Server Cache 320
Lesson Summary 323
Lesson 4:Introduction to Programming with ADO MD Objects 324
Client Interfaces for OLAP 324
Analyzing Existing Cubes 325
Local Cubes 337
Lesson Summary 338
Review 339
Chapter 11 Using Microsoft English Query to Query Warehouse Data 341
About This Chapter 341
Before You Begin 342
Lesson 1:Introduction to Microsoft English Query 343
Enables Plain-English Queries 343
Simple Design Environment 344
COM-Compliant Engine 344
The English Query Design Environment 344
Modeling the Domain 345
Flow of an English Query Application 347
Lesson Summary 348
Lesson 2:Creating an English Query Application 349
Creating or Importing Database Schema 349
Defining Entities 350
Creating Relationships between Entities 351
Testing the Application 352
Building the Application 353
Lesson Summary 356
Lesson 3:Deploying an English Query Application 357
The English Query Object Model 357
Deploying the Application in Other Environments 359
Recommended Practices 360
Lesson Summary 361
Review 362
Chapter 12 MDX Statements and ADO MD Objects 363
About This Chapter 363
Before You Begin 363
Lesson 1:Introduction to MDX 365
MDX vs.Transact-SQL 365
Working with Multidimensional Data 367
Accessing Multidimensional Data 370
Lesson Summary 374
Lesson 2:Writing an MDX Query 375
Building an MDX Statement 375
Specifying the Axes 377
Defining the Contents of an Axis 378
Defining Sets 379
Generating Sets of Tuples 381
Specifying the WHERE Clause 383
Defining a Calculated Member 390
Lesson Summary 395
Lesson 3:Programming with ADO MD Objects 396
Retrieving Multidimensional Data 396
How to Create a Local Cube 406
ADO MD Object Usage Summary 416
Lesson Summary 417
Review 418
Chapter 13 Maintaining a SQL Server Data Warehouse 421
About This Chapter 421
Before You Begin 422
Lesson 1:Developing a Maintenance Plan 423
Identifying Routine Maintenance Tasks 423
Performing Maintenance Tasks Periodically 424
Automating Administrative Tasks 425
Lesson Summary 428
Lesson 2:Maintaining OLAP Services Data 429
Maintaining OLAP Data 429
Processing Cubes 430
Updating Dimensions 434
Planning Data Updates 436
The DTS OLAP Services Processing Task 436
Lesson Summary 441
Lesson 3:Backing Up and Restoring Databases 442
Backing Up Databases 442
Types of Backup Methods 443
Verifying Backups 444
Restoring Databases 445
Lesson Summary 446
Lesson 4:Maintaining Data and Metadata 447
Archiving Enterprise Data 447
Archiving OLAP Data 448
Migrating the OLAP Services Repository 450
Lesson Summary 453
Review 454
Chapter 14 Managing a SQL Server Data Warehouse 455
About This Chapter 455
Before You Begin 455
Lesson 1:Managing Slowly Changing Dimensions 457
Slowly Changing Dimensional Attributes 457
Type 1:Overwriting the Dimension Record 458
Type 2:Writing Another Dimension Record 459
Type 3:Values in the Dimension Record 461
Lesson Summary 462
Lesson 2:Managing Resources 463
Optimizing Your Configuration 463
Optimizing Your Server Configuration 464
Lesson Summary 466
Lesson 3:Managing Security 467
Cube-Level and Database-Level Security 467
Cell-Level Security 468
Lesson Summary 472
Lesson 4:Managing Performance 473
Optimizing Data Warehouse Performance 473
Optimizing Cube Design 474
Creating Partitions 474
Optimizing Based on Usage 483
Lesson Summary 486
Review 487
Appendix A Questions and Answers 489
Appendix B Database Schemes 505
Appendix C Performing Basic Queries 507
Appendix D Querying Multiple Tabies 549
Appendix E Advanced Query Techniques 577
Appendix F Summarizing Data 611
Glossary 651
Index 703