About

Joy Mundy

and

Warren Thornthwaite

with

Ralph Kimball

WILEY

Wiley Publishing, Inc.

Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com

2006 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN-13: 978-0-471-26715-7

ISBN-10: 0471267155

10 9 8 7 6 5 4 3 2 1

1B/SW/QR/QW/IN

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise , except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at [phone], outside the U.S. at [phone] or fax [phone].

Library of Congress Cataloging-in-Publication Data

Mundy, Joy, 1961
The Microsoft data warehouse toolkit : with SQL Server 2005 and the Microsoft Business Intelligence toolset / Joy Mundy and Warren Thornthwaite with Ralph Kimball.
p. cm.
Includes index.
ISBN-13: 978-0-471-26715-7 (paper/website)
ISBN-10: 0-471-26715-5 (paper/website)
1. Data warehousing. 2. SQL server. I. Thornthwaite, Warren, 1957 II. Kimball, Ralph. III. Title.
QA76.9.D37M95 2006
005.74dc22
2005032275

Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates , in the United States and other countries, and may not be used without written permission. Microsoft and SQL Server are trademarks or registered trademarks of Microsoft Corporation in the U.S. and/or other countries . All other trademarks are the property of their respective owners . Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

About the Authors

Joy Mundy , a member of the Kimball Group, has been developing, consulting on, and speaking and writing about business intelligence systems and technology since 1992. Joy began her career as a business analyst in banking and finance as one of the power users we talk about in business intelligence. In 1992 she joined the data warehouse team at Stanford University, an effort that was both educational and character building. She next co-founded InfoDynamics LLC, a data warehouse consulting firm, and then joined Microsoft WebTV to develop closed-loop analytic applications and a packaged business intelligence system.

From 2000 to 2004, Joy worked with the Microsoft SQL Server Business Intelligence product development team. She managed a team that developed the best practices for building business intelligence systems on the Microsoft platform. Joy graduated from Tufts University with a B.A. in Economics, and from Stanford with an M.S. in Engineering Economic Systems.

Warren Thornthwaite , a member of the Kimball Group, has been building decision support and data warehousing systems since 1980. Warren co- authored the best-selling Data Warehouse Lifecycle Toolkit (Wiley, 1998).

Warren worked at Metaphor Computer Systems for eight years starting in 1983, 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. Warren joined up with WebTV to help build a world-class, multi-terabyte customer-focused data warehouse before returning to consulting.

In addition to designing data warehouses for a range of industries, Warren has extensive experience helping clients develop scalable, practical information access architectures. Warren holds an MBA in Decision Sciences from the University of Pennsylvanias Wharton School, and a BA in Communications Studies from the University of Michigan.

Ralph Kimball, Ph.D., founder of the Kimball Group, has been designing information systems and data warehouses since 1972.

Ralph wrote his Ph.D. dissertation in the Electrical Engineering department at Stanford University on the design of a manmachine system for tutoring mathematics students. In 1972 he joined the Xerox Palo Alto Research Center as a research scientist. Over the following ten years at Xerox, he became a development manager and the product marketing manager for the Xerox Star workstation, the first commercial product that used windows , icons, and the mouse. For this work at Xerox, he received the Alexander Williams Award from the IEEE Human Factors Society for user interface design.

Following his years at Xerox, Ralph was a vice president and member of the founding team at Metaphor Computer Systems, the first data warehousing company. Between 1982 and 1986, Metaphor installed many client-server data warehouse systems. In 1986 Ralph founded Red Brick Systems, which developed the first high-performance relational database for decision support. Since 1993, Ralph has designed data warehouse systems, written bestselling data warehouse books, and taught data warehousing skills to more than 10,000 IT professionals.

Acknowledgments

First, we want to thank the thousands of you who have read the Kimball Groups Toolkit books, attended our courses, and engaged us in consulting projects. We always learn from you, and youve had a profound impact on our thinking and the business intelligence industry.

This book would not have been written without the assistance of many people on the SQL Server product development team. Of special note is Donald Farmer, who gave generously of his time in clarifying ideas and reviewing the chapters related to Integration Services and metadata. Siva Harinath and Stephen Quinn contributed material for the real-time chapter. Stuart Ozer reviewed the entire book, at a point when his time was at a premium (which is almost always the case at Microsoft!). Others at Microsoft reviewed individual chapters and answered many questions. These reviewers, listed alphabetically , include Carolyn Chau, Grant Dickinson, Jamie MacLennan, John Miller, Ashvini Sharma, Dave Wickert, and Rob Zare.

We especially thank Bill Baker, the General Manager of SQL Server Business Intelligence at Microsoft, for his vision of BI for the masses, for his leadership of the SQL BI product development team, and for his support for our project.

Our colleagues at the Kimball Group were invaluable. Their encouragement kept us going while we were writing the book, and their reviews helped us polish and prune material. Ralph Kimball, of course, had a huge impact on the book, not just from his writing and thinking in the business intelligence arena but more directly by helping us improve the books overall structure and flow. Bob Becker and Margy Ross helped enormously.

Carl Rabeler of Solid Quality Learning provided an especially detailed review of the entire book. Were indebted to him. Sara Shlaer and Bob Elliott, our editors at Wiley, have been very helpful and encouraging. Its been a pleasure to work with them.

To our life partners , thanks for being there when we needed you, for giving us the time we needed, and for occasionally reminding us that it was time to take a break. Tony Navarrete and Elizabeth Wright, the book wouldnt exist without you.