Microsoft SQL Server 7.0数据仓库开发技术 影印版PDF电子书下载

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