Dwh data ware house

Mastering Data Warehouse Design
Relational and Dimensional Techniques

Claudia Imhoff Nicholas Galemmo Jonathan G. Geiger

Vice President and Executive Publisher: Robert Ipsen Publisher: Joe Wikert Executive Editor: Robert M. Elliott Developmental Editor: Emilie Herman Editorial Manager: Kathryn Malm Managing Editor: Pamela M. Hanley Text Design & Composition: Wiley Composition ServicesThis book is printed on acid-free paper. ∞ Copyright © 2003 by Claudia Imhoff, Nicholas Galemmo, and Jonathan G. Geiger. All rights reserved. Published by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada 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 Section 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, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8700. Requests to the Publisher for permissionshould be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4447, E-mail: permcoordinator@wiley.com. Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy orcompleteness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher norauthor shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages. For general information on our other products and services please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. Trademarks: Wiley, the WileyPublishing logo and related trade dress are trademarks or registered trademarks of Wiley Publishing, Inc., in the United States and other countries, and may not be used without written permission. 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 varietyof electronic formats. Some content that appears in print may not be available in electronic books. ISBN: 0-471-32421-3 Printed in the United States of America 10 9 8 7 6 5 4 3 2 1

D E D I C AT I O N

Claudia: For all their patience and understanding throughout the years, this book is dedicated to David and Jessica Imhoff.

Nick: To my wife Sarah, and children Amanda and Nick Galemmo, fortheir understanding over the many weekends I spent working on this book. Also to my college professor, Julius Archibald at the State University of New York at Plattsburgh for instilling in me the science and art of computing.

Jonathan: To my wife, Alma Joy, for her patience and understanding of the time spent writing this book, and to my children, Avi and Shana, who are embarking on theirrespective careers and of whom I am extremely proud.

iii

CO NTE NTS

Acknowledgments About the Authors

xv xvii

Part One
Chapter 1

Concepts
Introduction Overview of Business Intelligence BI Architecture What Is a Data Warehouse? Role and Purpose of the Data Warehouse The Corporate Information Factory Operational Systems Data Acquisition Data Warehouse Operational Data Store Data...