Amazon no longer offers textbook rentals. We do!

Data Warehousing Fundamentals: A Comprehensive Guide for It Professionals

by: Paulraj Ponniah (Consultant)

Data Warehousing Fundamentals: A Comprehensive Guide for It Professionals

by: Paulraj Ponniah (Consultant)

ISBN 13:
9780471412540
ISBN 10:
0471412546
Format: Hardcover
Copyright: 09/01/2001
Publisher: Wiley-Interscience
Newer Edition

Rent

Sorry, this item is currently unavailable.

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

Extend or Purchase Your Rental at Any Time

Need to keep your rental past your due date? At any time before your due date you can extend or purchase your rental through your account.

Summary

Geared to IT professionals eager to get into the all-important field of data warehousing, this book explores all topics needed by those who design and implement data warehouses. Readers will learn about planning requirements, architecture, infrastructure, data preparation, information delivery, implementation, and maintenance. They'll also find a wealth of industry examples garnered from the author's 25 years of experience in designing and implementing databases and data warehouse applications for major corporations.Market: IT Professionals, Consultants.

Foreword

xxi

Preface

xxiii

Part 1 OVERVIEW AND CONCEPTS

The Compelling Need for Data Warehousing

(18)

Chapter Objectives

(1)

Escalating Need for Strategic Information

(5)

The Information Crisis

(1)

Technology Trends

(1)

Opportunities and Risks

(2)

Failures of Past Decision-Support Systems

(2)

History of Decision-Support Systems

(1)

Inability to Provide Information

(1)

Operational Versus Decision-Support Systems

(3)

Making the Wheels of Business Turn

(1)

Watching the Wheels of Business Turn

(1)

Different Scope, Different Purposes

(2)

Data Warehousing-The Only Viable Solution

(1)

A New Type of System Environment

(1)

Processing Requirements in the New Environment

(1)

Business Intelligence at the Data Warehouse

(1)

Data Warehouse Defined

(2)

A Simple Concept for Information Delivery

(1)

An Environment, Not a Product

(1)

A Blend of Many Technologies

(1)

Chapter Summary

(1)

Review Questions

(1)

Exercises

(3)

Data Warehouse: The Building Blocks

(20)

Chapter Objectives

(1)

Defining Features

(4)

Subject-Oriented Data

(1)

Integrated Data

(1)

Time-Variant Data

(1)

Nonvolatile Data

(1)

Data Granularity

(1)

Data Warehouses and Data Marts

(4)

How are They Different?

(1)

Top-Down Versus Bottom-Up Approach

(1)

A Practical Approach

(1)

Overview of the Components

(7)

Source Data Component

(3)

Data Staging Component

(2)

Data Storage Component

(1)

Information Delivery Component

(1)

Metadata Component

(1)

Management and Control Component

(1)

Metadata in the Data Warehouse

(1)

Types of Metadata

(1)

Special Significance

(1)

Chapter Summary

(1)

Review Questions

(1)

Exercises

(2)

Trends in Data Warehousing

(24)

Chapter Objectives

(1)

Continued Growth in Data Warehousing

(3)

Data Warehousing is Becoming Mainstream

(1)

Data Warehouse Expansion

(1)

Vendor Solutions and Products

(1)

Significant Trends

(13)

Multiple Data Types

(2)

Data Visualization

(2)

Parallel Processing

(1)

Query Tools

(1)

Browser Tools

(1)

Data Fusion

(1)

Multidimensional Analysis

(1)

Agent Technology

(1)

Syndicated Data

(1)

Data Warehousing and ERP

(1)

Data Warehousing and KM

(1)

Data Warehousing and CRM

(2)

Active Data Warehousing

(1)

Emergence of Standards

(2)

Metadata

(1)

OLAP

(1)

Web-Enabled Data Warehouse

(3)

The Warehouse to the Web

(1)

The Web to the Warehouse

(1)

The Web-Enabled Configuration

(1)

Chapter Summary

(1)

Review Questions

(1)

Exercises

(1)

Part 2 PLANNING AND REQUIREMENTS

Planning and Project Management

(26)

Chapter Objectives

(1)

Planning Your Data Warehouse

(5)

Key Issues

(2)

Business Requirements, Not Technology

(1)

Top Management Support

(1)

Justifying Your Data Warehouse

(1)

The Overall Plan

(1)

The Data Warehouse Project

(5)

How is it Different?

(1)

Assessment of Readiness

(1)

The Life-Cycle Approach

(2)

The Development Phases

(1)

The Project Team

(6)

Organizing the Project Team

(1)

Roles and Responsibilities

(2)

Skills and Experience Levels

(1)

User Participation

(2)

Project Management Considerations

(6)

Guiding Principles

(1)

Warning Signs

(1)

Success Factors

(1)

Anatomy of a Successful Project

(1)

Adopt a Practical Approach

(2)

Chapter Summary

(1)

Review Questions

(1)

Exercises

(2)

Defining the Business Requirements

(20)

Chapter Objectives

(1)

Dimensional Analysis

(3)

Usage of Information Unpredictable

(1)

Dimensional Nature of Business Data

(2)

Examples of Business Dimensions

(1)

Information Packages-A new Concept

(4)

Requirements Not Fully Determinate

(2)

Business Dimensions

(1)

Dimension Hierarchies/Categories

(1)

Key Business Metrics or Facts

(1)

Requirements Gathering Methods

(7)

Interview Techniques

(3)

Adapting the JAD Methodology

102

(1)

Review of Existing Documentation

103

(1)

Requirements Definition: Scope and Content

104

(2)

Data Sources

105

(1)

Data Transformation

105

(1)

Data Storage

105

(1)

Information Delivery

105

(1)

Information Package Diagrams

106

(1)

Requirements Definition Document Outline

106

(1)

Chapter Summary

106

(1)

Review Questions

107

(1)

Exercises

107

(2)

Requirements as the Driving Force for Data Warehousing

109

(18)

Chapter Objectives

109

(1)

Data Design

110

(3)

Structure for Business Dimensions

112

(1)

Structure for Key Measurements

112

(1)

Levels of Detail

113

(1)

The Architectural Plan

113

(6)

Composition of the Components

114

(1)

Special Considerations

115

(3)

Tools And Products

118

(1)

Data Storage Specifications

119

(2)

DBMS Selection

120

(1)

Storage Sizing

120

(1)

Information Delivery Strategy

121

(3)

Queries and Reports

122

(1)

Types of Analysis

123

(1)

Information Distribution

123

(1)

Decision Support Applications

123

(1)

Growth and Expansion

123

(1)

Chapter Summary

124

(1)

Review Questions

124

(1)

Exercises

125

(2)

Part 3 ARCHITECTURE AND INFRASTRUCTURE

The Architectural Components

127

(18)

Chapter Objectives

127

(1)

Understanding Data Warehouse Architecture

127

(2)

Architecture: Definitions

127

(1)

Architecture in Three Major Areas

128

(1)

Distinguishing Characteristics

129

(3)

Different Objectives and Scope

130

(1)

Data Content

130

(1)

Complex Analysis and Quick Response

131

(1)

Flexible and Dynamic

131

(1)

Metadata-driven

132

(1)

Architecture Framework

132

(2)

Architecture Supporting Flow of Data

132

(1)

The Management and Control Module

133

(1)

Technical Architecture

134

(8)

Data Acquisition

135

(3)

Data Storage

138

(2)

Information Delivery

140

(2)

Chapter Summary

142

(1)

Review Questions

142

(1)

Exercises

143

(2)

Infrasturcture as the Foundation for Data Warehousing

145

(28)

Chapter Objectives

145

(1)

Infrastructure Supporting Architecture

145

(3)

Operational Infrastructure

147

(1)

Physical Infrastructure

147

(1)

Hardware and Operating Systems

148

(16)

Plateform Options

150

(8)

Server Hardware

158

(6)

Database Software

164

(3)

Parallel Processing Options

164

(2)

Selection of the DBMS

166

(1)

Collection of Tools

167

(3)

Architecture First, Then Tools

168

(1)

Data Modeling

169

(1)

Data Extraction

169

(1)

Data Transformation

169

(1)

Data Loading

169

(1)

Data Quality

169

(1)

Queries and Reports

170

(1)

Online Analytical Processing (OLAP)

170

(1)

Alert Systems

170

(1)

Middleware and Connectivity

170

(1)

Data Warehouse Management

170

(1)

Chapter Summary

170

(1)

Review Questions

171

(1)

Exercises

171

(2)

The Significant Role of Metadata

173

(30)

Chapter Objectives

173

(1)

Why Metadata is Important

173

(10)

A Critical Need in the Data Warehouse

175

(2)

Why Metadata is Vital for End-Users

177

(2)

Why Metadata is Essential for IT

179

(2)

Automation of Warehousing Tasks

181

(2)

Establishing the Context of Information

183

(1)

Metadata Types by Functional Areas

183

(4)

Data Acquisition

184

(2)

Data Storage

186

(1)

Information Delivery

186

(1)

Business Metadata

187

(3)

Content Overview

188

(1)

Examples of Business Metadata

188

(1)

Content Highlights

189

(1)

Who Benefits?

190

(1)

Technical Metadata

190

(3)

Content Overview

190

(1)

Examples of Technical Metadata

191

(1)

Content Highlights

192

(1)

Who Benefits?

192

(1)

How to Provide Metadata

193

(7)

Metadata Requirements

193

(1)

Sources of Metadata

194

(2)

Challenges for Metadata Management

196

(1)

Metadata Repository

196

(2)

Metadata Integration and Standards

198

(1)

Implementation Options

199

(1)

Chapter Summary

200

(1)

Review Questions

201

(1)

Exercises

201

(2)

Part 4 DATA DESIGN AND DATA PREPARATION

Principles of Dimensional Modeling

203

(22)

Chapter Objectives

203

(1)

From Requirements to Data Design

203

(7)

Design Decisions

204

(1)

Dimensional Modeling Basics

204

(5)

E-R Modeling Versus Dimensional Modeling

209

(1)

Use of CASE Tools

209

(1)

The STAR Schema

210

(8)

Review of a Simple STAR Schema

210

(2)

Inside a Dimension Table

212

(2)

Inside the Fact Table

214

(2)

The Factless Fact Table

216

(1)

Data Granularity

217

(1)

STAR Schema Keys

218

(2)

Primary Keys

218

(1)

Surrogate Keys

219

(1)

Foreign Keys

219

(1)

Advantages of the STAR Schema

220

(3)

Easy for Users to Understand

220

(1)

Optimizes Navigation

221

(1)

Most Suitable for Query Processing

222

(1)

STARjoin and STARindex

223

(1)

Chapter Summary

223

(1)

Review Questions

224

(1)

Exercises

224

(1)

Dimensional Modeling: Advanced Topics

225

(32)

Chapter Objectives

225

(1)

Updates to the Dimension Tables

226

(5)

Slowly Changing Dimensions

226

(1)

Correction of Errors

227

(1)

Preservation of History

228

(2)

Tentative Soft Revisions

230

(1)

Miscellaneous Dimensions

231

(4)

Large Dimensions

231

(2)

Rapidly Changing Dimensions

233

(2)

Junk Dimensions

235

(1)

The Snowflake Schema

235

(4)

Options to Normalize

235

(3)

Advantages and Disadvantages

238

(1)

When to Snowflake

238

(1)

Aggregate Fact Tables

239

(10)

Fact Table Sizes

241

(1)

Need for Aggregates

242

(1)

Aggregating Fact Tables

243

(4)

Aggregation Options

247

(2)

Families of STARS

249

(6)

Snapshot and Transaction Tables

250

(1)

Core and Custom Tables

251

(1)

Supporting Enterprise Value Chain or Value Circle

251

(2)

Conforming Dimensions

253

(1)

Standardizing Facts

254

(1)

Summary of Family of STARS

254

(1)

Chapter Summary

255

(1)

Review Questions

255

(1)

Exercises

256

(1)

Data Extraction, Transformation, and Loading

257

(34)

Chapter Objectives

257

(1)

ETL Overview

258

(4)

Most Important and Most Challenging

259

(1)

Time-consuming and Arduous

260

(1)

ETL Requirements and Steps

260

(1)

Key Factors

261

(1)

Data Extraction

262

(9)

Source Identification

263

(1)

Data Extraction Techniques

263

(7)

Evaluation of the Techniques

270

(1)

Data Transformation

271

(8)

Data Transformation: Basic Tasks

272

(1)

Major Transformation Types

273

(2)

Data Integration and Consolidation

275

(2)

Transformation for Dimension Attributes

277

(1)

How to Implement Transformation

277

(2)

Data Loading

279

(6)

Applying Data: Techniques and Processes

280

(2)

Data Refresh Versus Updata

282

(1)

Procedure for Dimension Tables

283

(1)

Fact Tables: History and Incremental Loads

284

(1)

ETL Summary

285

(3)

ETL Tool Options

285

(1)

Reemphasizing ETL Metadata

286

(1)

ETL Summary and Approach

287

(1)

Chapter Summary

288

(1)

Review Questions

288

(1)

Exercises

289

(2)

Data Quality: A key to Success

291

(24)

Chapter Objectives

291

(1)

Why is Data Quality Critical?

292

(7)

What is Data Quality?

292

(3)

Benefits of Improved Data Quality

295

(1)

Types of Data Quality Problems

296

(3)

Data Quality Challenges

299

(4)

Sources of Data Pollution

299

(2)

Validation of Names and Addresses

301

(1)

Costs of Poor Data Quality

302

(1)

Data Quality Tools

303

(1)

Categories of Data Cleansing Tools

303

(1)

Error Discovery Features

303

(1)

Data Correction Features

303

(1)

The DBMS for Quality Control

304

(1)

Data Quality Initiative

304

(7)

Data Cleansing Decisions

305

(2)

Who Should be Responsible?

307

(2)

The Purification Process

309

(2)

Practical Tips on Data Quality

311

(1)

Chapter Summary

311

(1)

Review Questions

312

(1)

Exercises

312

(3)

Part 5 INFORMATION ACCESS AND DELIVERY

Matching Information to the Classes of Users

315

(28)

Chapter Objectives

315

(1)

Information from the Data Warehouse

316

(7)

Data Warehouse Versus Operational Systems

316

(2)

Information Potential

318

(3)

User-Information Interface

321

(2)

Industry Applications

323

(1)

Who Will Use the Information?

323

(6)

Classes of Users

323

(3)

What They Need

326

(3)

How to Provide Information

329

(1)

Information Delivery

329

(6)

Queries

331

(1)

Reports

332

(1)

Analysis

333

(1)

Applications

334

(1)

Information Delivery Tools

335

(6)

The Desktop Environment

335

(1)

Methodology for Tool Selection

335

(3)

Tool Selection Criteria

338

(2)

Information Delivery Framework

340

(1)

Chapter Summary

341

(1)

Review Questions

341

(1)

Exercises

341

(2)

OLAP in the Data Warehouse

343

(34)

Chapter Objectives

343

(1)

Demand for Online Analytical Processing

344

(9)

Need for Multidimensional Analysis

344

(1)

Fast Access and Powerful Calculations

345

(2)

Limitations of Other Analysis Methods

347

(2)

OLAP is the Answer

349

(1)

OLAP Definitions and Rules

349

(3)

OLAP Characteristics

352

(1)

Major Features and Functions

353

(10)

General Features

353

(1)

Dimensional Analysis

353

(4)

What are Hypercubes?

357

(3)

Drill-Down and Roll-Up

360

(2)

Slice-and-Dice or Rotation

362

(1)

Uses and Benefits

363

(1)

OLAP Models

363

(5)

Overview of Variations

364

(1)

The MOLAP Model

365

(1)

The ROLAP Model

366

(1)

ROLAP Versus MOLAP

367

(1)

OLAP Implementation Considerations

368

(6)

Data Design and Preparation

368

(2)

Administration and Performance

370

(2)

OLAP Platforms

372

(1)

OLAP Tools and Products

373

(1)

Implementation Steps

374

(1)

Chapter Summary

374

(1)

Review Questions

374

(1)

Exercises

375

(2)

Data Warehousing and the Web

377

(22)

Chapter Objectives

377

(1)

Web-Enabled Data Warehouse

378

(5)

Why theWeb?

378

(2)

Convergence of Technologies

380

(1)

Adapting the Data Warehouse for the Web

381

(1)

The Web as a Data Source

382

(1)

Web-Based Information Delivery

383

(6)

Expanded Usage

383

(2)

New Information Strategies

385

(2)

Browser Technology for the Data Warehouse

387

(2)

Security Issues

389

(1)

OLAP and the Web

389

(2)

Enterprise OLAP

389

(1)

Web-OLAP Approaches

390

(1)

OLAP Engine Design

390

(1)

Building a Web-Enabled Data Warehouse

391

(5)

Nature of the Data Webhouse

391

(2)

Implementation Considerations

393

(1)

Putting the Pieces Together

394

(1)

Web Processing Model

394

(2)

Chapter Summary

396

(1)

Review Questions

396

(1)

Exercises

396

(3)

Data Mining Basics

399

(30)

Chapter Objectives

399

(1)

What is Data Mining?

400

(8)

Data Mining Defined

401

(1)

The Knowledge Discovery Process

402

(2)

OLAP Versus Data Mining

404

(2)

Data Mining and the Data Warehouse

406

(2)

Major Data Mining Techniques

408

(14)

Cluster Detection

409

(2)

Decision Trees

411

(2)

Memory-Based Reasoning

413

(2)

Link Analysis

415

(2)

Neural Networks

417

(1)

Genetic Algorithms

418

(1)

Moving into Data Mining

419

(3)

Data Mining Applications

422

(4)

Benefits of Data Mining

423

(1)

Applications in Retail Industry

424

(1)

Applications in Telecommunications Industry

425

(1)

Applications in Banking and Finance

426

(1)

Chapter Summary

426

(1)

Review Questions

426

(1)

Exercises

427

(2)

Part 6 IMPLEMENTATION AND MAINTENANCE

The Physical Design Process

429

(26)

Chapter Objectives

429

(1)

Physical Design Steps

430

(3)

Develop Standards

430

(1)

Create Aggregates Plan

431

(1)

Determine the Data Partitioning Scheme

431

(1)

Establish Clustering Options

432

(1)

Prepare an Indexing Strategy

432

(1)

Assign Storage Structures

432

(1)

Complete Physical Model

433

(1)

Physical Design Considerations

433

(5)

Physical Design Objectives

433

(1)

From Logical Model to Physical Model

434

(1)

Physical Model Components

435

(1)

Significance of Standards

436

(2)

Physical Storage

438

(5)

Storage Area Data Structures

439

(1)

Optimizing Storage

440

(2)

Using RAID Technology

442

(1)

Estimating Storage Sizes

442

(1)

Indexing the Data Warehouse

443

(6)

Indexing Overview

443

(2)

B-Tree Index

445

(1)

Bitmapped Index

446

(2)

Clustered Indexes

448

(1)

Indexing the Fact Table

448

(1)

Indexing the Dimension Tables

449

(1)

Performance Enhancement Techniques

449

(3)

Data Partitioning

449

(1)

Data Clustering

450

(1)

Parallel Processing

450

(1)

Summary Levels

451

(1)

Referential Integrity Checks

451

(1)

Initialization Parameters

451

(1)

Data Arrays

452

(1)

Chapter Summary

452

(1)

Review Questions

452

(1)

Exercises

453

(2)

Data Warehouse Deployment

455

(22)

Chapter Objectives

455

(1)

Major Deployment Activities

456

(6)

Complete User Acceptance

456

(1)

Perform Initial Loads

457

(1)

Get User Desktops Ready

458

(1)

Complete Initial User Training

459

(1)

Institute Initial User Support

460

(1)

Depoly in Stages

460

(2)

Considerations for a Pilot

462

(5)

When Is a Pilot Data Mart Useful?

462

(1)

Types of Pilot Projects

463

(2)

Choosing the Pilot

465

(1)

Expanding and Integrating the Pilot

466

(1)

Security

467

(3)

Security Policy

467

(1)

Managing User Privileges

468

(1)

Password Considerations

469

(1)

Security Tools

469

(1)

Backup and Recovery

470

(3)

Why Back Up the Data Warehouse?

470

(1)

Backup Strategy

471

(1)

Setting Up a Practical Schedule

472

(1)

Recovery

472

(1)

Chapter Summary

473

(1)

Review Questions

474

(1)

Exercises

474

(3)

Growth and Maintenance

477

(16)

Chapter Objectives

477

(1)

Monitoring the Data Warehouse

478

(3)

Collection of Statistics

478

(2)

Using Statistics for Growth Planning

480

(1)

Using Statistics for Fine-Tuning

480

(1)

Publishing Trends for Users

481

(1)

User Training and Support

481

(6)

User Training Content

482

(1)

Preparing the Training Program

482

(2)

Delivering the Training Program

484

(1)

User Support

485

(2)

Managing the Data Warehouse

487

(3)

Platform Upgrades

487

(1)

Managing Data Growth

488

(1)

Storage Management

488

(1)

ETL Management

489

(1)

Data Model Revisions

489

(1)

Information Delivery Enhancements

489

(1)

Ongoing Fine-Tuning

490

(1)

Chapter Summary

490

(1)

Review Questions

491

(1)

Exercises

491

(2)

Appendix A. Project Life Cycle Steps and Checklists

493

(4)

Appendix B. Critical Factors for Success

497

(2)

Appendix C. Guidelines for Evaluating Vendor Solutions

499

(2)

References

501

(2)

Glossary

503

(8)

Index

511

Amazon no longer offers textbook rentals. We do!

Data Warehousing Fundamentals: A Comprehensive Guide for It Professionals

Data Warehousing Fundamentals: A Comprehensive Guide for It Professionals

9780471412540

0471412546

Summary

Author Biography

Table of Contents

Supplemental Materials

Write a Review