Data warehouse

The Data Warehouse Toolkit
Second Edition

TE

AM FL Y

The Complete Guide to Dimensional Modeling

Ralph Kimball Margy Ross

Wiley Computer Publishing

John Wiley & Sons, Inc.
N E W YO R K • C H I C H EST E R • W E I N H E I M • B R I S BA N E • S I N G A P O R E • TO R O N TO

The Data Warehouse Toolkit
Second Edition

The Data Warehouse Toolkit
Second Edition

TheComplete Guide to Dimensional Modeling

Ralph Kimball Margy Ross

Wiley Computer Publishing

John Wiley & Sons, Inc.
N E W YO R K • C H I C H EST E R • W E I N H E I M • B R I S BA N E • S I N G A P O R E • TO R O N TO

Publisher: Robert Ipsen Editor: Robert Elliott Assistant Editor: Emilie Herman Managing Editor: John Atkins Associate New Media Editor: Brian Snapp Text Composition: JohnWiley Composition Services Designations used by companies to distinguish their products are often claimed as trademarks. In all instances where John Wiley & Sons, Inc., is aware of a claim, the product names appear in initial capital or ALL CAPITAL LETTERS. Readers, however, should contact the appropriate companies for more complete information regarding trademarks and registration. This book isprinted on acid-free paper. ∞ Copyright © 2002 by Ralph Kimball and Margy Ross. All rights reserved. Published by John Wiley and Sons, Inc. 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 Sections107 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, (978) 750-8400, fax (978) 750-4744. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons,Inc., 605 Third Avenue, New York, NY 10158-0012, (212) 850-6011, fax (212) 850-6008, E-Mail: PERMREQ@WILEY.COM. This publication is designed to provide accurate and authoritative information in regard to the subject matter covered. It is sold with the understanding that the publisher is not engaged in professional services. If professional advice or other expert assistance is required, theservices of a competent professional person should be sought. Library of Congress Cataloging-in-Publication Data: Kimball, Ralph. The data warehouse toolkit : the complete guide to dimensional modeling / Ralph Kimball, Margy Ross. — 2nd ed. p. cm. “Wiley Computer Publishing.” Includes index. ISBN 0-471-20024-7 1. Database design. 2. Data warehousing. I. Ross, Margy, 1959– II. Title. QA76.9.D26 K575 2002658.4'038'0285574—dc21 Printed in the United States of America. 10 9 8 7 6 5 4 3 2 1 2002002284

CO NTE NTS

Acknowledgments Introduction Chapter 1 Dimensional Modeling Primer

xv xvii 1

Different Information Worlds Goals of a Data Warehouse
The Publishing Metaphor

2 2
4

Components of a Data Warehouse
Operational Source Systems Data Staging Area Data Presentation Data AccessTools Additional Considerations

6
7 8 10 13 14

Dimensional Modeling Vocabulary
Fact Table Dimension Tables Bringing Together Facts and Dimensions

16
16 19 21

Dimensional Modeling Myths
Common Pitfalls to Avoid

24
26

Summary
Chapter 2 Retail Sales

27
29

Four-Step Dimensional Design Process Retail Case Study
Step 1. Select the Business Process Step 2. Declare the GrainStep 3. Choose the Dimensions Step 4. Identify the Facts

30 32
33 34 35 36

v

vi

Contents

Dimension Table Attributes
Date Dimension Product Dimension Store Dimension Promotion Dimension Degenerate Transaction Number Dimension

38
38 42 45 46 50

Retail Schema in Action Retail Schema Extensibility Resisting Comfort Zone Urges
Dimension Normalization (Snowflaking) Too Many...