Microsoft SQL Server 7.0数据仓库开发技术 影印版【2025|PDF|Epub|mobi|kindle电子书版本百度云盘下载】

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

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

Chapter 1 Benefits of Data Warehousing1

About This Chapter1

Before You Begin1

Lesson 1:Fundamental Data Warehousing Concepts2

What Is a Data Warehouse?2

What Is a Data Mare?3

Lesson Summary4

Lesson 2:Benefits of Data Warehousing5

The Ability to Access Enterprisewide Data5

The Ability to Have Consistent Data6

The Ability to Perform Analysis Quickly6

Recognition of Redundancy of Effort6

Discovery of Gaps in Business Knowledge or Business Processes6

Decreased Administration Costs6

Empowering Members of an Enterprise by Providing Them with Information7

Lesson Summary7

Lesson 3:Data Warehousing Features of Microsoft SQL Server 7.08

Microsoft Data Warehousing Framework8

SQL Server 7.0 Data Warehousing Tools9

Lesson Summary12

Review13

Chapter 2 Applications of Data Warehousing15

About This Chapter15

Before You Begin15

Lesson 1:What Is a Data Warehouse?16

History of Corporate Data Analysis16

What Is Online Analytical Processing?17

OLAP Data Characteristics19

Lesson Summary20

Lesson 2:Data Warehousing Life Cycle21

Introduction to the Northwind Database21

The Data Warehousing Life Cycle23

Source OLTP Systems23

Transformation Tools23

The Data Warehouse and Data Marts23

Cubes23

Lesson Summary25

Lesson 3:Business Scenarios26

Business Scenarios26

Sales Tracking26

Parts Tracking27

Lesson Summary28

Review29

Chapter 3 Challenges of Data Warehousing31

About This Chapter31

Before You Begin31

Lesson 1:The Data Warehousing Analysis Process32

Comparison of System Architectures32

Business Analysis35

Business Drivers36

Lesson Summary51

Lesson 2:An Examination of Project Management Approaches54

A Golden Rule of Data Warehousing54

Data Warehouse or Data Mart?54

Data Warehousing Issues55

Effective Development Practices56

Building the Team57

Lesson Summary59

Lesson 3:Identifying Technical Requirements60

Lesson Summary62

Review63

Chapter 4 Developing the Logical Design65

About This Chapter65

Before You Begin66

Lesson 1:Review of OLTP System Design67

Entity-Relationship Diagrams67

Database Table Normalization69

Normalization Example70

OLTP System Design Issues72

Lesson Summary73

Lesson 2:OLAP System Design74

Designing OLAP Systems75

Dimensional Modeling:The Star(Join)Schema76

Dimensional Hierarchies78

Consolidated vs.Snowflaked Hierarchies82

Information Object Dimension Tables82

Dimensional and E-R Models84

Lesson Summary85

Lesson 3:Dimensional Schema Design86

Dimensional Schema Design Steps86

Lesson Summary92

Lesson 4:Dimensions94

Establishing Dimensions94

Lesson Summary99

Lesson 5:Aggregations100

Designing Aggregations100

Aggregate Characteristics102

Aggregate Design Issues103

Aggregation Example104

Lesson Summary106

Review107

Chapter 5 Defining the Technical Architecture for a Solution109

About This Chapter109

Before You Begin109

Lesson 1:Microsoft Data Warehousing Framework110

Data Warehousing Framework Components111

Microsoft Components112

Data Warehousing Framework114

OLE DB,ODBC,and OLE DB for OLAP115

Data Transformation Services and Replication116

OLAP Services116

PivotTable Service118

ADO and ADO MD118

Microsoft English Query119

Microsoft Repository119

Lesson Summary120

Lesson 2:SQL Server 7.0 Data Warehousing Features121

Types of Servers121

Backup and Restore Features122

Query Processor Enhancements123

Other Enhancements124

Lesson Summary124

Lessonm 3:Setting Up the Data Warehouse Source Database125

Estimating Size of the Data Warehouse125

Minimizing Fact Table Size128

Balancing Size and Performance128

Creating a Database129

Creating Tables131

Creating Indexes137

Lesson Summary140

Review141

Chapter 6 SQL Server Data Services143

About This Chapter143

Before You Begin143

Lesson 1:Overview of SQL Server Data Services144

Transferring and Transforming Data144

Loading the Data Warehouse146

OLE DB,ODBC,and OLE DB for OLAP148

Tools for Transferring Data in SQL Server151

Other Tools for Transferring Data153

Lesson Summary153

Lesson 2:Introduction to Data Transformation Services154

Overview of DTS154

The DTS Process155

DTS Tools156

Lesson Summary161

Review162

Chapter 7 Replication163

About This Chapter163

Before You Begin163

Lesson 1:Introduction to Distributed Data164

The Need for Distributed Data164

Considerations for Distributing Data164

Methods of Distributing Data with SQL Server166

Lesson Summary168

Lesson 2:Introduction to SQL Server Replication169

The Publisher/Subscriber Metaphor169

Publications and Articles170

Filtering Data171

Subscriptions173

Lesson Summary175

Lesson 3:SQL Server Replication Types176

Snapshot Replication176

The Snapshot Replication Process177

Transactional Replication177

The Transactional Replication Process178

The Immediate Updating Subscribers Option178

Merge Replication180

SQL Server Replication Agents182

Lesson Summary183

Lesson 4:Physical Replication Models185

Overview of the Replication Models185

Combining Replication Models and Types186

Using Replication to Update Warehoused Data191

Selecting a Data Population Mechanism193

Replication Exercises194

Lesson Summary201

Review202

Chapter 8 Advanced DTS203

About This Chapter203

Before You Begin203

Lesson 1:Planning and Creating DTS Workflows204

Restructuring and Mapping Data204

Creating a DTS Package205

Defining Workflows207

Executing and Scheduling a DTS Package215

Lesson Summary232

Lesson 2:Using Data-Driven Queries to Update the Data Warehouse233

What Is a Data-Driven Query?233

When to Use a Data-Driven Query233

Performance Issues234

Data-Driven Query Examples234

Creating Data-Driven Query Tasks235

Lesson Summary241

Lesson 3:DTS Programming242

DTS Object Model242

DTS Package Object Internals243

Transacting DTS Packages245

DTS Programming Samples245

Lesson Summary245

Review246

Chapter 9 Microsoft SQL Server OLAP Services247

About This Chapter247

Before You Begin247

Lesson 1:Moving from OLAP Data Warehouses to Cubes249

Determining Requirements249

Designing and Building the OLAP Data Warehouse Database250

Extracting,Cleaning,and Loading Data251

Multidimensional Structure(Cube)252

Lesson Summary255

Lesson 2:Microsoft SQL Server OLAP Services256

OLAP Services Features256

OLAP Services COM Interface259

OLAP Services System Requirements261

Lesson Summary263

Lesson 3:Designing and Building Cubes264

Building Dimensions264

Creating Calculated Members265

Designing and Building Aggregations267

How OLAP Services Handles Data Explosion269

Lesson Summary275

Lesson 4:Managing Cubes Using Storage TYpes and Partitions276

Storing in a MOLAP Structure276

Storing in a ROLAP Structure277

Storing in a HOLAP Structure279

Comparing Storage Structures280

MOLAP vs.ROLAP280

Processing,Updating,and Refreshing Cubes282

Virtual Cubes283

Partitions286

Security288

Lesson Summary290

Review291

Chapter lO Data Analysis Tools and Architecture293

About This Chapter293

Before You Begin293

Lesson 1:Data Analysis Concepts295

Drilling into Data295

Data Mining296

Client and Server Caching296

Analyzing Data Offline297

Lesson Summary298

Lesson 2:Data Analysis Tools299

OLAP Manager299

Learn about Data Warehouse Concepts302

Excel 2000302

Microsoft English Query309

Third-Party Products310

Lesson Summary310

Lesson 3:OLAP Services and PivotTable Service Architecture311

Microsoft SQL Server OLAP Services Architecture311

PivotTable Service314

OLAP Services:Client/Server Cache320

Lesson Summary323

Lesson 4:Introduction to Programming with ADO MD Objects324

Client Interfaces for OLAP324

Analyzing Existing Cubes325

Local Cubes337

Lesson Summary338

Review339

Chapter 11 Using Microsoft English Query to Query Warehouse Data341

About This Chapter341

Before You Begin342

Lesson 1:Introduction to Microsoft English Query343

Enables Plain-English Queries343

Simple Design Environment344

COM-Compliant Engine344

The English Query Design Environment344

Modeling the Domain345

Flow of an English Query Application347

Lesson Summary348

Lesson 2:Creating an English Query Application349

Creating or Importing Database Schema349

Defining Entities350

Creating Relationships between Entities351

Testing the Application352

Building the Application353

Lesson Summary356

Lesson 3:Deploying an English Query Application357

The English Query Object Model357

Deploying the Application in Other Environments359

Recommended Practices360

Lesson Summary361

Review362

Chapter 12 MDX Statements and ADO MD Objects363

About This Chapter363

Before You Begin363

Lesson 1:Introduction to MDX365

MDX vs.Transact-SQL365

Working with Multidimensional Data367

Accessing Multidimensional Data370

Lesson Summary374

Lesson 2:Writing an MDX Query375

Building an MDX Statement375

Specifying the Axes377

Defining the Contents of an Axis378

Defining Sets379

Generating Sets of Tuples381

Specifying the WHERE Clause383

Defining a Calculated Member390

Lesson Summary395

Lesson 3:Programming with ADO MD Objects396

Retrieving Multidimensional Data396

How to Create a Local Cube406

ADO MD Object Usage Summary416

Lesson Summary417

Review418

Chapter 13 Maintaining a SQL Server Data Warehouse421

About This Chapter421

Before You Begin422

Lesson 1:Developing a Maintenance Plan423

Identifying Routine Maintenance Tasks423

Performing Maintenance Tasks Periodically424

Automating Administrative Tasks425

Lesson Summary428

Lesson 2:Maintaining OLAP Services Data429

Maintaining OLAP Data429

Processing Cubes430

Updating Dimensions434

Planning Data Updates436

The DTS OLAP Services Processing Task436

Lesson Summary441

Lesson 3:Backing Up and Restoring Databases442

Backing Up Databases442

Types of Backup Methods443

Verifying Backups444

Restoring Databases445

Lesson Summary446

Lesson 4:Maintaining Data and Metadata447

Archiving Enterprise Data447

Archiving OLAP Data448

Migrating the OLAP Services Repository450

Lesson Summary453

Review454

Chapter 14 Managing a SQL Server Data Warehouse455

About This Chapter455

Before You Begin455

Lesson 1:Managing Slowly Changing Dimensions457

Slowly Changing Dimensional Attributes457

Type 1:Overwriting the Dimension Record458

Type 2:Writing Another Dimension Record459

Type 3:Values in the Dimension Record461

Lesson Summary462

Lesson 2:Managing Resources463

Optimizing Your Configuration463

Optimizing Your Server Configuration464

Lesson Summary466

Lesson 3:Managing Security467

Cube-Level and Database-Level Security467

Cell-Level Security468

Lesson Summary472

Lesson 4:Managing Performance473

Optimizing Data Warehouse Performance473

Optimizing Cube Design474

Creating Partitions474

Optimizing Based on Usage483

Lesson Summary486

Review487

Appendix A Questions and Answers489

Appendix B Database Schemes505

Appendix C Performing Basic Queries507

Appendix D Querying Multiple Tabies549

Appendix E Advanced Query Techniques577

Appendix F Summarizing Data611

Glossary651

Index703