BuildingADataWarehouse,WithExamplesInSqlServer(2008)资源-CSDN文库

Data

Warehouse

需积分: 10 194 浏览量 2008-10-05 09:43:47 上传评论收藏 10.92MB PDF 举报

资源推荐

资源详情

资源评论

Vincent Rainardi

Building a

Data Warehouse

With Examples in

SQL Server

9314fmfinal.qxd 11/15/07 1:37 PM Page i

Building a Data Warehouse: With Examples in SQL Server

electronic or mechanical, including photocopying, recording, or by any information storage or retrieval

system, without the prior written permission of the copyright owner and the publisher.

ISBN-13 (pbk): 978-1-59059-931-0

ISBN-10 (pbk): 1-59059-931-4

ISBN-13 (electronic): 978-1-4302-0527-2

ISBN-10 (electronic): 1-4302-0527-X

Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1

Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence

of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark

owner, with no intention of infringement of the trademark.

Lead Editor: Jeffrey Pepper

Technical Reviewers: Bill Hamilton and Asif Sayed

Editorial Board: Steve Anglin, Ewan Buckingham, Tony Campbell, Gary Cornell, Jonathan Gennick,

Jason Gilmore, Kevin Goff, Jonathan Hassell, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper,

Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh

Senior Project Manager: Tracy Brown Collins

Copy Editor: Kim Wimpsett

Associate Production Director: Kari Brooks-Copony

Production Editor: Kelly Winquist

Compositor: Linda Weidemann, Wolf Creek Press

Proofreader: Linda Marousek

Indexer: Ron Strauss

Artist: April Milne

Cover Designer: Kurt Krames

Manufacturing Director: Tom Debolski

Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,

New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail

orders-ny@springer-sbm.com,

or visit http://www.springeronline.com.

For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600,

Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, e-mail info@apress.com, or visit http://

www.apress.com.

The information in this book is distributed on an “as is” basis, without warranty. Although every pre-

caution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any

liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly

or indirectly by the information contained in this work.

The source code for this book is available to readers at http://www.apress.com.

9314fmfinal.qxd 11/15/07 1:37 PM Page ii

Contents at a Glance

About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii

Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv

■CHAPTER 1 Introduction to Data Warehousing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

■CHAPTER 2 Data Warehouse Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

■CHAPTER 3 Data Warehouse Development Methodology . . . . . . . . . . . . . . . . . . . . 49

■CHAPTER 4 Functional and Nonfunctional Requirements. . . . . . . . . . . . . . . . . . . . 61

■CHAPTER 5 Data Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

■CHAPTER 6 Physical Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

■CHAPTER 7 Data Extraction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173

■CHAPTER 8 Populating the Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215

■CHAPTER 9 Assuring Data Quality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273

■CHAPTER 10 Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301

■CHAPTER 11 Building Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329

■CHAPTER 12 Multidimensional Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377

■CHAPTER 13 Using Data Warehouse for Business Intelligence. . . . . . . . . . . . . . . 411

■CHAPTER 14 Using Data Warehouse for Customer

Relationship Management

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441

■CHAPTER 15 Other Data Warehouse Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 467

■CHAPTER 16 Testing Your Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477

■CHAPTER 17 Data Warehouse Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491

■APPENDIX Normalization Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505

■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509

9314fmfinal.qxd 11/15/07 1:37 PM Page v

Contents

About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii

Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv

■CHAPTER 1 Introduction to Data Warehousing. . . . . . . . . . . . . . . . . . . . . . . . . . 1

What Is a Data Warehouse?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

Retrieves Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

Consolidates Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

Periodically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

Dimensional Data Store . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Normalized Data Store . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Other Analytical Activities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

Updated in Batches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Other Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Data Warehousing Today. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Customer Relationship Management . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Data Mining. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

Master Data Management (MDM). . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Customer Data Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Future Trends in Data Warehousing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Unstructured Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Service-Oriented Architecture (SOA) . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Real-Time Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

vii

9314fmfinal.qxd 11/15/07 1:37 PM Page vii

■CHAPTER 2 Data Warehouse Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Data Flow Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Single DDS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

NDS + DDS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

ODS + DDS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Federated Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

System Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Case Study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

■CHAPTER 3 Data Warehouse Development Methodology. . . . . . . . . . . . . . 49

Waterfall Methodology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

Iterative Methodology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

■CHAPTER 4 Functional and Nonfunctional Requirements . . . . . . . . . . . . . 61

Identifying Business Areas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Understanding Business Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62

Defining Functional Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Defining Nonfunctional Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Conducting a Data Feasibility Study. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

■CHAPTER 5 Data Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Designing the Dimensional Data Store . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Dimension Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Date Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77

Slowly Changing Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80

Product, Customer, and Store Dimensions. . . . . . . . . . . . . . . . . . . . . . . . . . 83

Subscription Sales Data Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Supplier Performance Data Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94

CRM Data Marts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

Data Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

Source System Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102

Designing the Normalized Data Store . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111

■CONTENTSviii

9314fmfinal.qxd 11/15/07 1:37 PM Page viii

剩余530页未读，继续阅读

评论收藏

内容反馈

delectation

粉丝: 4
资源: 7

Building A Data Warehouse, With Examples In Sql Server (2008)

最新资源

Building A Data Warehouse, With Examples In Sql Server (2008)

Azure SQL Data Warehouse

Building A Data Warehouse With Examples In Sql Server

Building a Data Warehouse_ With Examples in SQL Server

Building a Data Warehouse With Examples in SQL Server

Building the Data Warehouse Fourth Edition

70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012

The Microsoft Data Warehouse Toolkit _ With SQL Server 2005 and the Microsoft Business Intelligence分卷1

Building The Data Warehouse 4th Edition

Inmon, Building the data warehouse, 4th edition

building the data warehouse

吴文杰 - Data Warehouse with ES in Datastory.pdf

Ebook - Building Datawarehouse using SQL2008 (part 1/2)

Ebook - Building Datawarehouse using SQL2008 (part 2/2)

The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset

The DataWarehouse ToolsKit

The Microsoft Data Warehouse Toolkit Second Edition

Qt 5实现串口调试助手 （源工程文件、0积分下载）

【SystemVerilog】路科验证V2学习笔记（全600页）.pdf

AutoSAR标准协议4.2.2

光伏-储能并网系统仿真.rar

NPPJSONViewer.zip

GD32替换STM32注意事项.pdf

XCP协议的规范文档

VS2015安装证书，JavaScript_ProjectSystem.msi，JavaScript_LanguageService.msi

CANoe通过CAPL脚本实现自动测试

蓝牙BLE协议中文版.pdf

BaiduOCR.zip

AD20官方中文教程.pdf

电路分析基础第二版PDF电子书免费下载

最新资源

Qt 5实现串口调试助手（源工程文件、0积分下载）