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 Building a Data Warehouse With Examples in SQL Server Dear Reader, This book contains essential topics of data warehousing that everyone embarking on a data warehousing journey will need to understand in order to build a data Building a warehouse. It covers dimensional modeling, data extraction from source systems, dimension and fact table population, data quality, and database design. It also explains practical data warehousing applications such as business intelligence, analytic applications, and customer relationship management. All in all, the book covers the whole spectrum of data warehousing from start to finish.
Data Warehouse I wrote this book to help people with a basic knowledge of database systems who want to take their first step into data warehousing. People who are familiar with databases such as DBAs and developers who have never built a data ware- house will benefit the most from this book. IT students and self-learners will also benefit. In addition, BI and data warehousing professionals will be interested in checking out the practical examples, code, techniques, and architectures described in the book.
Throughout this book, we will be building a data warehouse using the Amadeus Entertainment case study, an entertainment retailer specializing in music, films, and audio books. We will use Microsoft SQL Server 2005 and 2008 to build the data warehouse and BI applications. You will gain experience designing and building various components of a data warehouse, including the architecture, With Examples in SQL Server data model, physical databases (using SQL Server), ETL (using SSIS), BI reports (using SSRS), OLAP cubes (using SSAS), and data mining (using SSAS). I wish you great success in your data warehousing journey.
Sincerely, Vincent Rainardi Related Titles Companion eBook See last page for details on $10 eBook version Vincent Rainardi Rainardi ISBN-13: 978-1-59059-931-0 ISBN-10: 1-59059-931-4 SOURCE CODE ONLINE 90000 www.com 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 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, 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. 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.
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.qxd 11/15/07 1:37 PM Page iii For my lovely wife, Ivana.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. 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.
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 .qxd 11/15/07 1:37 PM Page vi 9314fmfinal.qxd 11/15/07 1:37 PM Page vii Contents About the Author.
xv ■CHAPTER 1 Introduction to Data Warehousing. 1 What Is a Data Warehouse?. 6 Dimensional Data Store. 7 Normalized Data Store.
12 Other Analytical Activities. 14 Updated in Batches. 16 Data Warehousing Today. 17 Customer Relationship Management.
19 Master Data Management (MDM). 20 Customer Data Integration. 23 Future Trends in Data Warehousing. 25 Service-Oriented Architecture (SOA).
26 Real-Time Data Warehouse .qxd 11/15/07 1:37 PM Page viii viii ■CONTENTS ■CHAPTER 2 Data Warehouse Architecture. 29 Data Flow Architecture. 38 Federated Data Warehouse. 47 ■CHAPTER 3 Data Warehouse Development Methodology.
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. 70 ■CHAPTER 5 Data Modeling. 71 Designing the Dimensional Data Store.
77 Slowly Changing Dimension. 80 Product, Customer, and Store Dimensions. 83 Subscription Sales Data Mart. 89 Supplier Performance Data Mart.
94 CRM Data Marts. 101 Source System Mapping. 102 Designing the Normalized Data Store .qxd 11/15/07 1:37 PM Page ix ■CONTENTS ix ■CHAPTER 6 Physical Database Design. 123 Creating DDS Database Structure.
128 Creating the Normalized Data Store. 171 ■CHAPTER 7 Data Extraction. 173 Introduction to ETL. 173 ETL Approaches and Architecture.
177 Extracting Relational Databases. 180 Whole Table Every Time. 186 Testing Data Leaks. 187 Extracting File Systems.
187 Extracting Other Source Types. 190 Extracting Data Using SSIS. 191 Memorizing the Last Extraction Timestamp. 200 Extracting from Files.
214 ■CHAPTER 8 Populating the Data Warehouse. 219 Using SSIS to Populate NDS. 228 Upsert Using SQL and Lookup. 242 Practical Tips on SSIS .qxd 11/15/07 1:37 PM Page x x ■CONTENTS Populating DDS Dimension Tables.
250 Populating DDS Fact Tables. 266 Batches, Mini-batches, and Near Real-Time ETL. 269 Pushing the Data In. 271 ■CHAPTER 9 Assuring Data Quality.
273 Data Quality Process. 274 Data Cleansing and Matching. 277 Cross-checking with External Sources. 290 Data Quality Rules.
291 Action: Reject, Allow, Fix. 293 Logging and Auditing. 296 Data Quality Reports and Notifications. 301 Metadata in Data Warehousing.
301 Data Definition and Mapping Metadata. 303 Data Structure Metadata. 308 Source System Metadata. 313 ETL Process Metadata.
318 Data Quality Metadata. 327 ■CHAPTER 11 Building Reports. 329 Data Warehouse Reports. 329 When to Use Reports and When Not to Use Them.
342 Grouping, Sorting, and Filtering. 357 Multidimensional Database Reports .qxd 11/15/07 1:37 PM Page xi ■CONTENTS xi Managing Reports. 370 Managing Report Security. 370 Managing Report Subscriptions.
372 Managing Report Execution. 375 ■CHAPTER 12 Multidimensional Database. 377 What a Multidimensional Database Is. 377 Online Analytical Processing.
380 Creating a Multidimensional Database. 381 Processing a Multidimensional Database. 388 Querying a Multidimensional Database. 394 Administering a Multidimensional Database.
396 Multidimensional Database Security. 399 Backup and Restore. 409 ■CHAPTER 13 Using Data Warehouse for Business Intelligence. 411 Business Intelligence Reports.
412 Business Intelligence Analytics. 413 Business Intelligence Data Mining. 416 Business Intelligence Dashboards. 432 Business Intelligence Alerts.
437 Business Intelligence Portal. 439 ■CHAPTER 14 Using Data Warehouse for Customer Relationship Management. 441 Single Customer View. 450 Delivery and Response Data.
464 Customer Loyalty Scheme .qxd 11/15/07 1:37 PM Page xii xii ■CONTENTS ■CHAPTER 15 Other Data Warehouse Usage. 467 Customer Data Integration. 470 Search in Data Warehousing. 476 ■CHAPTER 16 Testing Your Data Warehouse.
477 Data Warehouse ETL Testing. 485 User Acceptance Testing. 486 End-to-End Testing. 487 Migrating to Production.
489 ■CHAPTER 17 Data Warehouse Administration. 491 Monitoring Data Warehouse ETL. 492 Monitoring Data Quality. 499 Making Schema Changes.
503 ■APPENDIX Normalization Rules .qxd 11/15/07 1:37 PM Page xiii About the Author ■VINCENT RAINARDI is a data warehouse architect and developer with more than 12 years of experience in IT. He started working with data warehous- ing in 1996 when he was working for Accenture. He has been working with Microsoft SQL Server since 2000. He worked for Lastminute.com (part of the Travelocity group) until October 2007.
He now works as a data warehousing consultant in London specializing in SQL Server. He is a member of The Data Warehousing Institute (TDWI) and regularly writes data warehousing articles for SQLServerCentral.qxd 11/15/07 1:37 PM Page xiv 9314fmfinal.qxd 11/15/07 1:37 PM Page xv Preface F riends and colleagues who want to start learning data warehousing sometimes ask me to recommend a practical book about the subject matter. They are not new to the database world; most of them are either DBAs or developers/consultants, but they have never built a data warehouse. They want a book that is practical and aimed at beginners, one that contains all the basic essentials.
There are many data warehousing books on the market, but they usu- ally cover a specialized topic such as clickstream, ETL, dimensional modeling, data mining, OLAP, or project management and therefore a beginner would need to buy five to six books to understand the complete spectrum of data warehousing. Other books cover multiple aspects, but they are not as practical as they need to be, targeting executives and project managers instead of DBAs and developers. Because of that void, I took a pen (well, a laptop really) and spent a whole year writing in order to provide a practical, down-to-earth book containing all the essential subjects of building a data warehouse, with many examples and illustrations from projects that are easy to understand. The book can be used to build your first data warehouse straightaway; it cov- ers all aspects of data warehousing, including approach, architecture, data modeling, ETL, data quality, and OLAP.
I also describe some practical issues that I have encountered in my experience—issues that you’ll also likely encounter in your first data warehousing project— along with the solutions. It is not possible to show examples, code, and illustrations for all the different database platforms, so I had to choose a specific platform.