Building a Data Warehouse with Examples in SQL Server

541 Pages·2007·12.12 MB·English

cyan yeLLOW
MaGenTa BLack
PanTOne 123 c
BOOks fOR PROfessIOnaLs By PROfessIOnaLs® The eXPeRT’s VOIce® In sQL seRVeR
Companion
eBook Available
Building a Data Warehouse:
With Examples in SQL Server WB
iu
t
Dear Reader, h
i
This book contains essential topics of data warehousing that everyone embarking El
on a data warehousing journey will need to understand in order to build a data xd
warehouse. It covers dimensional modeling, data extraction from source systems, a
Building a
i
dimension and fact table population, data quality, and database design. It also m
n
explains practical data warehousing applications such as business intelligence,
p
analytic applications, and customer relationship management. All in all, the g
l
book covers the whole spectrum of data warehousing from start to finish. e
I wrote this book to help people with a basic knowledge of database systems s Data Warehouse
who want to take their first step into data warehousing. People who are familiar a
i
with databases such as DBAs and developers who have never built a data ware- n

house will benefit the most from this book. IT students and self-learners will D
S
also benefit. In addition, BI and data warehousing professionals will be interested
in checking out the practical examples, code, techniques, and architectures Q
a
described in the book. L
Throughout this book, we will be building a data warehouse using the t
S
Amadeus Entertainment case study, an entertainment retailer specializing in a
music, films, and audio books. We will use Microsoft SQL Server 2005 and 2008 e With Examples in SQL Server
to build the data warehouse and BI applications. You will gain experience designing r
vW
and building various components of a data warehouse, including the architecture,
e
data model, physical databases (using SQL Server), ETL (using SSIS), BI reports
r
(using SSRS), OLAP cubes (using SSAS), and data mining (using SSAS). a
I wish you great success in your data warehousing journey.
r
Sincerely,
e
Vincent Rainardi
h
o
RELatED titLES u
Companion eBook
s
e

See last page for details
on $10 eBook version

R Vincent Rainardi
ISBN-13: 978-1-59059-931-0 a
ISBN-10: 1-59059-931-4 in
SOURCE CODE ONLINE 90000 a
www.apress.com rd
i
Shelve in
Microsoft: SQL Server
User level:
Intermediate–Advanced 9 781590 599310
this print for content only—size & color not accurate 7" x 9-1/4" / CASEBOUND / MALLOY
(1.0625 INCH BULK -- 544 pages -- 50# Thor)

9314fmfinal.qxd 11/15/07 1:37 PM Page i
Building a
Data Warehouse
With Examples in
SQL Server
Vincent Rainardi
9314fmfinal.qxd 11/15/07 1:37 PM Page ii
Building a Data Warehouse: With Examples in SQL Server
Copyright © 2008 by Vincent Rainardi
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
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,
JasonGilmore, Kevin Goff, Jonathan Hassell, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper,
BenRenow-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 [phone], e-mail [email protected],
orvisit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600,
Berkeley, CA 94705. Phone [phone], fax [phone], e-mail [email protected], 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 iii
For my lovely wife,Ivana.
9314fmfinal.qxd 11/15/07 1:37 PM Page iv
9314fmfinal.qxd 11/15/07 1:37 PM Page v
Contents at a Glance
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
nCHAPTER 1 Introduction to Data Warehousing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
nCHAPTER 2 Data Warehouse Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
nCHAPTER 3 Data Warehouse Development Methodology. . . . . . . . . . . . . . . . . . . . 49
nCHAPTER 4 Functional and Nonfunctional Requirements. . . . . . . . . . . . . . . . . . . . 61
nCHAPTER 5 Data Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
nCHAPTER 6 Physical Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
nCHAPTER 7 Data Extraction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
nCHAPTER 8 Populating the Data Warehouse. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
nCHAPTER 9 Assuring Data Quality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
nCHAPTER 10 Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
nCHAPTER 11 Building Reports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
nCHAPTER 12 Multidimensional Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377
nCHAPTER 13 Using Data Warehouse for Business Intelligence. . . . . . . . . . . . . . . 411
nCHAPTER 14 Using Data Warehouse for Customer
Relationship Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
nCHAPTER 15 Other Data Warehouse Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 467
nCHAPTER 16 Testing Your Data Warehouse. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477
nCHAPTER 17 Data Warehouse Administration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
nAPPENDIX Normalization Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505
nINDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
v
9314fmfinal.qxd 11/15/07 1:37 PM Page vi
9314fmfinal.qxd 11/15/07 1:37 PM Page vii
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
nCHAPTER 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 viii
viii nCONTENTS
nCHAPTER 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
nCHAPTER 3 Data Warehouse Development Methodology. . . . . . . . . . . . . . 49
Waterfall Methodology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Iterative Methodology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
nCHAPTER 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
nCHAPTER 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