• Cowritten by Ralph Kimball, the world's leading data warehousing authority, whose previous books have sold more than 150,000 copies
• Delivers real-world solutions for the most time- and labor-intensive portion of data warehousing-data staging, or the extract, transform, load (ETL) process
• Delineates best practices for extracting data from scattered sources, removing redundant and inaccurate data, transforming the remaining data into correctly formatted data structures, and then loading the end product into the data warehouse
• Offers proven time-saving ETL techniques, comprehensive guidance on building dimensional structures, and crucial advice on ensuring data quality

The extract, transform, and load (ETL) phase of the data warehouse development life cycle is far and away the most difficult, time-consuming, and labor-intensive phase of building a data warehouse. Done right, companies can maximize their use of data storage; if not, they can end up wasting millions of dollars storing obsolete and rarely used data. Bestselling author Ralph Kimball, along with Joe Caserta, shows you how a properly designed ETL system extracts the data from the source systems, enforces data quality and consistency standards, conforms the data so that separate sources can be used together, and finally delivers the data in a presentation-ready format.

Serving as a road map for planning, designing, building, and running the back-room of a data warehouse, this book provides complete coverage of proven, timesaving ETL techniques. Beginning with a quick overview of ETL fundamentals, it then looks at ETL data structures, both relational and dimensional. The authors show how to build useful dimensional structures, providing practical examples of techniques.

Along the way you’ll learn how to:

• Plan and design your ETL system
• Choose the appropriate architecture from the many possible options
• Build the development/test/production suite of ETL processes
• Build a comprehensive data cleaning subsystem
• Tune the overall ETL process for optimum performance

The New York Times Book Review

A fascinating case historyDescribes the events that led up to one of the great biological discoveries of our time.

Jacob Bronowski

The Nation

No one could miss the excitement in this story of a great and beautiful discovery.The book communicates the spirit of science as no formal account has ever done.the sense of the future, the high spirits, and the rivalry and the guesses right and wrong, the surge of imagination and the test of fact.

Peter B. Medewar

The New York Review of Books

An enormous successa classic.

Andre Lwoff

Scientific American

The history of a scientific endeavor, a true detective story that leaves the reader breathless from beginning to end.

Richard Feynman

He has described admirably how it feels to have that frightening and beautiful experience of making a scientific discovery.

Product Review
Richard Feynman He has described admirably how it feels to have that frightening and beautiful experience of making a scientific discovery.

Building a data warehousing and business intelligence system is a complex business and engineering effort. While there are significant technical challenges to overcome in successfully deploying a data warehouse, the authors find that the most common reason for data warehouse project failure is insufficient focus on the business users and business problems. In an effort to help people gain success, this book takes the proven business Dimensional Lifecycle approach first described in best selling The Data Warehouse Lifecycle Toolkit and applies it to the microsoft SQL Server 2005 tool set.

Beginning with a thorough description of how to gather business requirements, the book then works through the details of creating the target dimensional model, setting up the data warehouse infrastructure, creating the relational atomic database, creating the analysis services databases, designing and building the standard report set, implementing security, dealing with metadata, managing ongoing maintenance and growing the DW/BI system. All of these steps tie back to the business requirements. Each chapter describes the practical steps in the context of the SQL Server 2005 platform.

Intended Audience

The target audience for this book is the IT department or service provider (consultant) who is:

• Planning a small to mid-range data warehouse project;
• Evaluating or planning to use microsoft technologies as the primary or exclusive data warehouse server technology;
• Familiar with the general concepts of data warehousing and business intelligence.

The book will be directed primarily at the project leader and the warehouse developers, although everyone involved with a data warehouse project will find the book useful. Some of the book’s content will be more technical than the typical project leader will need; other chapters and sections will focus on business issues that are interesting to a database administrator or programmer as guiding information.

The book is focused on the mass market, where the volume of data in a single application or data mart is less than 500 GB of raw data. While the book does discuss issues around handling larger warehouses in the microsoft environment, it is not exclusively, or even primarily, concerned with the unusual challenges of extremely large datasets.

About the Authors

JOY MUNDY has focused on data warehousing and business intelligence since the early 1990s, specializing in business requirements analysis, dimensional modeling, and business intelligence systems architecture. Joy co-founded InfoDynamics LLC, a data warehouse consulting firm, then joined microsoft WebTV to develop closed-loop analytic applications and a packaged data warehouse.

Before returning to consulting with the Kimball Group in 2004, Joy worked in microsoft SQL Server product development, managing a team that developed the best practices for building business intelligence systems on the microsoft platform. Joy began her career as a business analyst in banking and finance. She graduated from Tufts University with a BA in Economics, and from Stanford with an MS in engineering Economic Systems.

WARREN THORNTHWAITE has been building data warehousing and business intelligence systems since 1980. Warren worked at Metaphor for eight years, where he managed the consulting organization and implemented many major data warehouse systems. After Metaphor, Warren managed the enterprise-wide data warehouse development at Stanford University. He then co-founded InfoDynamics LLC, a data warehouse consulting firm, with his co-author, Joy Mundy. Warren joined up with WebTV to help build a world class, multi-terabyte customer focused data warehouse before returning to consulting with the Kimball Group. In addition to designing data warehouses for a range of industries, Warren speaks at major industry conferences and for leading vendors, and is a long-time instructor for Kimball University. Warren holds an MBA in Decision Sciences from the University of Pennsylvania's Wharton School, and a BA in Communications Studies from the University of Michigan.

RALPH KIMBALL, PH.D., has been a leading visionary in the data warehouse industry since 1982 and is one of today's most internationally well-known authors, speakers, consultants, and teachers on data warehousing. He writes the "Data Warehouse Architect" column for Intelligent Enterprise (formerly DBMS) magazine.

Back Cover Copy

As longtime data warehousing practitioners and former microsoft insiders, authors Joy Mundy and Warren Thornthwaite have extensive experience in building and managing data warehouse (DW) and business intelligence (BI) systems. With this book, they share best practices for using SQL Server 2005 to build a successful DW/BI system. Covering the complete suite of data warehousing tools that accompanies SQL Server 2005, they focus on the full project lifecycle, including design, development, deployment, and maintenance.

You'll learn how and when to use BI tools such as Analysis Services, Integration Services, and the SQL Server database to accomplish various data warehousing tasks. A helpful case study used throughout the book provides examples of the techniques presented. You'll find practical guidance for every member of the data warehouse team and learn how to:

• Identify high-value business requirements and build organizational support for the project
• Design an information infrastructure for the enterprise using established dimensional design
• Design and build a flexible and powerful ETL system to clean, align, and restructure data for business use
• Provide decision makers with tools to analyze business problems and opportunities
• Use data mining to uncover data relationships and trends
• Build BI applications in Reporting Services
• Maintain, secure, and operate the DW/BI system

Visit the companion Web site at www.wiley.com/go/MsftDWToolkit

The companion Web site contains all the code samples, the sample database used throughout, sample templates, and other job aids.