Designing A Data Warehouse: Supporting Customer Relationship Management

414 Pages·2001·6.15 MB·English

by  Chris Todman

This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to reg.ister it. Thanks
only for RuBoard - do not distribute or recompile
Front Matter
Table of Contents
About the Author
Designing a Data Warehouse: Supporting Customer Relationship Management
Chris Todman
Publisher: Prentice Hall PTR
First Edition December 01, 2000
ISBN: 0-13-089712-4, 360 pages
Today’s next-generation data warehouses are being built with a clear goal; to maximize the power of
Customer Relationship Management. To make CRM_focused data warehousing work, you need new
techniques, and new methodologies.
In Designing A Data Warehouse, Dr. Chris Todman - one of the world’s leading data warehousing
consultants - delivers the first start-to-finish methodolgy for defining, designing, and implementing
CRM-focused data warehouses. Todman covers all this, and more:
A new look at data warehouse conceptual models, logical models, and physical implementation;
Project management: deliverables, assumptions, risks, and team-building - including a full breakdown
of work;
DW futures: temporal databases, OLAP SQL extensions, active decision support, integrating external
and unstructured data, search agents, and more.
only for RuBoard - do not distribute or recompile
1
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
only for RuBoard - do not distribute or recompile
Designing a Data Warehouse: Supporting Customer Relationship
Management
List of Figures
Preface
FIRST GENERATION DATA WAREHOUSES
SECOND-GENERATION DATA WAREHOUSES AND CUSTOMER
RELATIONSHIP MANAGEMENT
WHO SHOULD READ THIS BOOK
Acknowledgments
1. Customer Relationship Management
THE BUSINESS DIMENSION
BUSINESS GOALS
BUSINESS STRATEGY
THE VALUE PROPOSITION
CUSTOMER RELATIONSHIP MANAGEMENT
SUMMARY
2. An Introduction to Data Warehousing
INTRODUCTION
WHAT IS A DATA WAREHOUSE?
DIMENSIONAL ANALYSIS
BUILDING A DATA WAREHOUSE
PROBLEMS WHEN USING RELATIONAL DATABASES
SUMMARY
3. Design Problems We Have to Face Up To
DIMENSIONAL DATA MODELS
WHAT WORKS FOR CRM
2
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
SUMMARY
4. The Implications of Time in Data Warehousing
THE ROLE OF TIME
PROBLEMS INVOLVING TIME
CAPTURING CHANGES
FIRST-GENERATION SOLUTIONS FOR TIME
VARIATIONS ON A THEME
CONCLUSION TO THE REVIEW OF FIRST-GENERATION METHODS
5. The Conceptual Model
REQUIREMENTS OF THE CONCEPTUAL MODEL
THE IDENTIFICATION OF CHANGES TO DATA
DOT MODELING
DOT MODELING WORKSHOPS
SUMMARY
6. The Logical Model
LOGICAL MODELING
THE IMPLEMENTATION OF RETROSPECTION
THE USE OF THE TIME DIMENSION
LOGICAL SCHEMA
PERFORMANCE CONSIDERATIONS
CHOOSING A SOLUTION
FREQUENCY OF CHANGED DATA CAPTURE
CONSTRAINTS
EVALUATION AND SUMMARY OF THE LOGICAL MODEL
7. The Physical Implementation
THE DATA WAREHOUSE ARCHITECTURE
CRM APPLICATIONS
BACKUP OF THE DATA
ARCHIVAL
EXTRACTION AND LOAD
SUMMARY
8. Business Justification
THE INCREMENTAL APPROACH
THE SUBMISSION
SUMMARY
9. Managing the Project
INTRODUCTION
WHAT ARE THE DELIVERABLES?
WHAT ASSUMPTIONS AND RISKS SHOULD I INCLUDE?
WHAT SORT OF TEAM DO I NEED?
3
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to reg.ister it. Thanks
Summary
10. Software Products
EXTRACTION, TRANSFORMATION, AND LOADING
OLAP
QUERY TOOLS
DATA MINING
CAMPAIGN MANAGEMENT
PERSONALIZATION
METADATA TOOLS
SORTS
11. The Future
TEMPORAL DATABASES (TEMPORAL EXTENSIONS)
OLAP EXTENSIONS TO SQL
ACTIVE DECISION SUPPORT
EXTERNAL DATA
UNSTRUCTURED DATA
SEARCH AGENTS
DSS AWARE APPLICATIONS
A. Wine Club Temporal Classifications
B. Dot Model for the Wine Club
APPENDIX B DOT MODEL FOR THE WINE CLUB
C. Logical Model for the Wine Club
D. Customer Attributes
HOUSEHOLD AND PERSONAL ATTRIBUTES
BEHAVIORAL ATTRIBUTES
FINANCIAL ATTRIBUTES
EMPLOYMENT ATTRIBUTES
INTERESTS AND HOBBY ATTRIBUTES
References
only for RuBoard - do not distribute or recompile
4
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
only for RuBoard - do not distribute or recompile
Designing a Data Warehouse: Supporting Customer Relationship
Management
Library of Congress Cataloging-in-Publication Date
Todman, Chris.
Designing a data warehouse: in support of customer relationship
management/Chris Todman.
p. cm.
Includes bibliographical references and index.
ISBN: 0-13-089712-4
1. Data warehousing. I. Title.
HD30.2.T498 2001 CIP
658.4/03/0285574 21 1220202534
Credits
Editorial/Production Supervisor:
Kerry Reardon
5
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
Project Coordinator:
Anne Trowbridge
Acquisitions Editor:
Jill Pisoni
Editorial Assistant:
Justin Somma
Manufacturing Buyer:
Maura Zaldivar
Manufacturing Manager:
Alexis Heydt
Marketing Manager:
Dan DePasquale
Art Director:
Gail Cocker-Bogusz
Cover Designer:
Nina Scuderi
Cover Design Director:
Jerry Votta
Manager HP Books:
Patricia Pekary
Editorial Director, Hewlett Packard Professional
6
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
Books:
Susan Wright
© 2001 by Hewlett-Packard Company
Published by Prentice Hall PTR
Prentice-Hall, Inc.
Upper Saddle River, NJ 07458
Prentice Hall books are widely used by corporation and government
agencies for training, marketing, and resale.
The publisher offers discounts on this book when ordered in bulk quantities.
For more information, contact Corporate Sales Department, Phone:
800-382-3419; FAX: [phone];
E-mail: [email protected]
Or write: Prentice Hall PTR, Corporate Sales Dept., One Lake Street, Upper
Saddle River, NJ 07458
All rights reserved. No part of this book may be reproduced, in any form or
by any means, without permission in writing from the publisher.
Printed in the United States of America
10 9 8 7 6 5 4 3 2 1
Prentice-Hall International (UK) Limited, London
Prentice-Hall of Australia Pty. Limited, Sydney
Prentice-Hall Canada Inc., Toronto
Prentice-Hall Hispanoamericana, S.A., Mexico
Prentice-Hall of India Private Limited, New Delhi
7
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to reg.ister it. Thanks
Prentice-Hall of Japan, Inc., Tokyo
Pearson Education Asia, Pte. Ltd.
Editora Prentice-Hall do Brasil, Ltda., Rio de Janeiro
only for RuBoard - do not distribute or recompile
8
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
only for RuBoard - do not distribute or recompile
List of Figures
1.1 Just who are our best customers?
1.2 CRM in an organization.
1.3 The components of CRM.
1.4 The number of communication channels is growing.

2.1 Fragment of data model for the Wine Club.
2.2 Three-dimensional data cube.
2.3 Wine sales dimensional model for the Wine Club.
2.4 Data model showing multiple join paths.
2.5 The main components of a data warehouse system.
2.6 General-state transition diagram.
2.7 State transition diagram for the orders process.
2.8 Star schema showing the relationships between facts and dimensions.
2.9 Stratification of the data.
2.10 Snowflake schema for the sale of wine.
2.11 Levels of summarization in a data warehouse.
2.12 Modified data warehouse structure incorporating summary navigation and data mining.

3.1 Star schema for the Wine Club.
3.2 Third normal form version of the Wine Club dimensional model.
3.3 Confusing and intimidating hierarchy.
3.4 Common organizational hierarchy.
3.5 Star schema for the Wine Club.
3.6 Sharing information.
3.7 General model for customer details.
3.8 General model for a customer with changing circumstances.
3.9 Example model showing customer with changing circumstances.
3.10 The general model extended to include behavior.
3.11 The example model extended to include behavior.
3.12 General conceptual model for a customer-centric data warehouse.
3.13 Wine Club customer changing circumstances.
3.14 Wine Club customer behavior.
9
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
3.15 Derived segment examples for the Wine Club.

4.1 Fragment of operational data model.
4.2 Operational model with additional sales fact table.
4.3 Sales hierarchy.
4.4 Sales hierarchy with sales table attached.
4.5 Sales hierarchy showing altered relationships.
4.6 Sales hierarchy with intersection entities.
4.7 Sales hierarchy with data.
4.8 Simple general business hierarchy.
4.9 Graphical representation of temporal functions.
4.10 Types of temporal query.
4.11 Traditional resolution of m:n relationships.
4.12 Representation of temporal attributes by attaching them to the dimension.
4.13 Representation of temporal hierarchies by attaching them to the facts.
4.14 Representation of temporal attributes by attaching them to the facts.

5.1 Example of a two-dimensional report.
5.2 Example of a three-dimensional cube.
5.3 Simple multidimensional dot model.
5.4 Representation of the Wine Club using a dot model.
5.5 Customer-centric dot model.
5.6 Initial dot model for the Wine Club.
5.7 Refined dot model for the Wine Club.
5.8 Dot modeling worksheet showing Wine Club sales behavior.
5.9 Example of a hierarchy

6.1 ER diagram showing new relationships to the time dimension.
6.2 Logical model of part of the Wine Club

7.1 The EASI data architecture.
7.2 Metadata model for validation.
7.3 Integration layer.
7.4 Additions to the metadata model to include the source mapping layer.
7.5 Metadata model for the VIM layer.
7.6 Customer nonchanging details.
7.7 The changing circumstances part of the GCM.
10