A Manager's Guide to Data Warehousing E-Book

Laura Reeves

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Veröffentlichungsjahr: 2009

Beschreibung

Aimed at helping business and IT managers clearly communicate with each other, this helpful book addresses concerns straight-on and provides practical methods to building a collaborative data warehouse . You’ll get clear explanations of the goals and objectives of each stage of the data warehouse lifecycle while learning the roles that both business managers and technicians play at each stage. Discussions of the most critical decision points for success at each phase of the data warehouse lifecycle help you understand ways in which both business and IT management can make decisions that best meet unified objectives.

Leseprobe

Table of Contents

Title Page

Copyright Page

About the Author

Credits

Acknowledgements

Introduction

Overview of the Book

How This Book Is Organized

Who Should Read This Book

PART I - The Essentials of Data Warehousing

CHAPTER 1 - Gaining Data Warehouse Success

The Essentials of Data Warehousing

The Promises of Data Warehousing

Summary

CHAPTER 2 - The Executive’s FAQ for Data Warehousing

Question: What is the business benefit of a data warehouse?

Question: How much will it cost?

Question: How long will it take?

Question: How can I ensure success?

Question: Do other companies really build these in 90 days?

Question: How will we know we are doing this right?

Question: Why didn’t this work last time? What is different this time?

Question: Do we have the right technology in place?

Question: Are we the only company with data warehouse problems?

Question: Will I get one version of the truth?

Question: Why can’t we just use our current systems?

Question: Will the data warehouse replace our old systems?

Question: Who needs to be involved?

Question: Do we know where we are going? How will we know when we get there?

Question: How do we get started and stay focused?

Summary

PART II - The Business Side of Data Warehousing

CHAPTER 3 - Understanding Where You Are and Finding Your Way

Assessing Your Current State

Understanding Your Existing Reporting Environment

Introducing the Case Studies

Summary

CHAPTER 4 - Successful IT-Business Partnerships

What a Partnership Really Means

What the Business Partners Should Expect to Do

What You Should Expect from IT

Tips for Building and Sustaining a Partnership

Partnerships Beyond a Project

In Real Life

Summary

CHAPTER 5 - Setting Up a Successful Project

Defining the Project

Starting the Project

Managing a Successful Project

In Real Life

Summary

CHAPTER 6 - Providing Business Requirements

What Requirements Are Needed?

Providing Business Requirements

Communicating What You Really Need

Practical Techniques for Gathering Requirements

Putting the Pieces Together

Setting Attainable Goals

In Real Life

Summary

PART III - Dealing with the Data

CHAPTER 7 - Modeling the Data for your Business

The Purpose of Dimensional Models

Understanding Your Data

What Is a Dimensional Model?

The Business Dimensional Model

A Call Center Case Study

Enterprise Considerations

Business Participation in the Modeling Process

Reflecting on Business Realities: Advanced Concepts

Taking the Model Forward

In Real Life

Summary

CHAPTER 8 - Managing Data As a Corporate Asset

What Is Information Management?

Master Data Management

Data Governance

Implementing a Data Dictionary

Getting Started with Information Management

In Real Life

Summary

PART IV - Building the Project

CHAPTER 9 - Architecture, Infrastructure, and Tools

What Is Architecture?

Why Do We Need Architecture?

Data Architecture

Technical Architecture

Navigating the Technology Jungle

Making Architecture Work for You

In Real Life

Summary

CHAPTER 10 - Implementation: Building the Database

Extract, Transform, and Load (ETL) Fundamentals

The Business Role in ETL

Balancing Requirements and Data Reality

Tracking Progress—Are We There Yet?

What Else Can You Do to Help?

In Real Life

Summary

CHAPTER 11 - Data Delivery: What you Finally See

What Is Business Intelligence?

BI Building Blocks

Supporting Different Levels of Use

Construction of the BI Solution

Planning a Successful Launch

In Real Life

Summary

PART V - Next Steps—Expanding on Success

CHAPTER 12 - Managing the Production Data Warehouse

Finishing the Project

Adopting the Solution

The Production Data Warehouse

When the Data Warehouse Falls Short

In Real Life

Summary

CHAPTER 13 - Achieving Long-Term Success

Planning for Expansion and Growth

Managing Enterprise DW Resources

Pushing into the Future

Moving Toward Business Value

Conclusion

Glossary

Index

A Manager’s Guide to Data Warehousing

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

eISBN : 978-0-470-54938-4

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 Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at 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 Web site 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 Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites 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 please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Library of Congress Cataloging-in-Publication Data Reeves, Laura L.

A manager’s guide to data warehousing / Laura L. Reeves.

p. cm.

Includes index.

1. Data warehousing-Management. I. Title.

QA76.9.D37R44 2009

005.74068 - dc22

2009007401

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. 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 Author

Laura L. Reeves started designing and implementing data warehouse solutions in 1986. Since then she has been involved in hundreds of projects. She has extensive experience in end-to-end data warehouse development, including developing comprehensive project plans, collecting business requirements, developing business dimensional models, designing database schemas (both star and snowflake designs), and developing enterprise data warehouse architecture and strategies. These have been implemented for many business functions for private and public industry.

Laura co-founded StarSoft Solutions, Inc., in 1995 and has been a faculty member with The Data Warehousing Institute since 1997. She is a contributing author of Building a Data Warehouse for Decision Support (Prentice Hall, 1996) and a co-author of the first edition of The Data Warehouse Lifecycle Toolkit (Wiley, 1998). Laura graduated magna cum laude from Alma College with a bachelor of science degree in mathematics and computer science, with departmental honors.

Credits

Executive EditorRobert Elliott

Development EditorSara Shlaer

Technical EditorJonathan Geiger

Production EditorMelissa Lopez

Copy EditorLuann Rouff

Editorial ManagerMary Beth Wakefield

Production ManagerTim Tate

Vice President and ExecutiveGroup PublisherRichard Swadley

Vice President and ExecutivePublisherBarry Pruett

Associate PublisherJim Minatel

ProofreaderJosh Chase, Jen Larsen, and Kyle Schlesinger, WordOne

IndexerRobert Swanson

Cover Image© Digital Vision

Acknowledgments

I have been very blessed with great family, friends, and colleagues. I would like to thank the many clients and colleagues who have challenged me, pushed me, and collaborated with me on so many initiatives over the years. I appreciate the opportunity to work with such high-quality people. I want to acknowledge the contributions that have been made to the data warehousing industry and to me personally by the amazing people who worked at Metaphor. I want to express my gratitude to my dear friend and colleague Paul Kautza for his belief in me and for all his hard work all these years.

Thanks are also due to the dedicated staff at Wiley who believed in me and had great patience to help see this project through. Thanks to Bob Elliott for being the impetus to get this project started and to Sara Shlaer and Rosanne Koneval for their detailed efforts to produce a quality product. I want to express appreciation to Cindi Howson for her insight on business intelligence tools.

I want to extend a sincere and special thank you to Jonathon Geiger for his meticulous comments and suggestions.

I also want to thank two very special people who have provided unflagging support and encouragement every step of the way: my friends Ingrid Korb and Paula Johnson. I am not sure I could have done this without you!

Of course, none of this would be possible without the dedication, sacrifice, love, and support given to me by my family: Mark, Ryan, Michael, and Leah.

Introduction

Many executives, managers, business analysts, and nontechnical personnel are highly motivated to learn more about data warehousing. They want to understand what data warehouses are and how they work. More important, many are truly interested in doing their part to ensure success when implementing a data warehouse in their company. They are not interested in learning how to write code or tune a database.

Unfortunately, most data warehouse publications available today are written for the people who design and build them. Some are from a project management perspective and others provide a great deal of technical depth. While these are very valuable to the technical team, they do not help the nontechnical audience. This book was written to provide a resource for those nontechnical people.

Overview of the Book

The information in this book has been gathered over years of working on data warehouse projects. Hundreds of hours have been invested in learning what works well and what does not. One constant thread over the years is the need to develop and strengthen the partnership between business and systems personnel. There has always been a need to help nontechnical people in the organization understand the different parts of a data warehouse and what needs to be done to build and maintain one.

This book covers the topics and questions that come up repeatedly in executive briefings, classes, meetings, and casual conversation. It also includes coverage of topics related to how organizations frequently get into trouble. The goal is to minimize the frustration of all participants and to ultimately help organizations to build and maintain valuable data warehouse environments.

The book provides a sound introduction to data warehousing concepts and then moves on to explain the process of developing and creating a data warehouse project. A description of each step is provided, including details about how the business should participate. This book does not provide the technical level of detail that IT team members need to know, such as specific coding techniques or how to best set parameters to improve performance with a specific technology. The book does provide what is needed to understand these areas so that you are better prepared to have clear communication across the organization. By knowing what is being designed or developed, you can be more effective in sharing your ideas, requirements, and concerns.

More technical readers will benefit by gaining a complete picture of data warehousing, with an emphasis on how representatives from the business community can help you be more successful. This will provide you with ideas about how to interact more clearly with the business community. Use this as a resource; highlight the most pertinent chapters or sections that would be beneficial for your business counterparts to read.

How This Book Is Organized

This book is divided into five major parts. Each part focuses on a specific aspect of data warehousing.

• The first part shares the essentials of data warehousing. This can help executives and managers get a realistic understanding of the fundamentals and gain insight into both the most common factors for success and how to avoid roadblocks.

• The second part of the book takes a look at the business management of a data warehouse. This includes how to cultivate a strong partnership between the business and IT communities, what is entailed in setting up a data warehouse project, and how to effectively communicate your business requirements for the data warehouse. It is just as important for different business groups to work together in order to support a consistent view of the data.

• The third part of the book focuses on the data itself. How the data is organized is critical to ensure that you, the business, can understand and exploit what is available. Without the right data, presented in a useful manner, the data warehouse will not be used, which jeopardizes the entire investment. This section of the book gives you the tools you need to ensure that the data meets your needs. Beyond how data is processed or stored, the issues surrounding how organizations view data ownership and management are also addressed.

• Part four delves into data warehouse architecture, what is involved to efficiently build the data warehouse, and finally what must be done to deliver the data. While most of the work during this part of a project is more technical in nature, there is still a need for active business participation. This section will help managers and business participants understand what work is being done and how they can help.

• Part five explores what is needed to launch the data warehouse and wrap up the project. Looking beyond a single project, the work needed to maintain and grow the data warehouse is discussed. The causes of, and recommendations to address, a stalled data warehouse are presented. A data warehouse can have initial success that decreases over time unless specific steps are taken. Suggestions are provided that can help you sustain your success.

Who Should Read This Book

The target audience for this book is anyone who is responsible for, working on, or paying for a data warehouse. In particular, it is targeted toward nontechnical readers in order to help them understand the basics of data warehousing, and, more importantly, how to successfully build one. For the more technical readers, this book provides the tools you need in order to be able to communicate with your business partners.

The information is presented in a layered manner. The basic concepts are presented early in the book, while more in-depth coverage is provided in later chapters. This book is designed to serve a variety of levels of need. Some readers may benefit from reading only the first part, returning later to learn more about a specific area. Other readers may benefit by reading all of the content from start to finish.

While all readers could benefit from reading the entire text, I realize that not everyone has the same passion for data warehousing. Realistically, different types of readers will benefit from various parts of the book:

• Executives and senior managers should read Part 1. Then, based upon what the organization is facing, subsequent chapters may be worthwhile as different issues crop up. For example, if your company has problems getting the data loaded into the data warehouse, it would be helpful to read Chapter 10 to get a better understanding of what is really involved in building the database.

• Middle managers, both business and IT, will benefit from Part 1 but will also find Part 2 to be important. These parts help you get the project set up properly and, most important, learn about how to provide business requirements. Skimming the rest of the book can help all managers learn what is available to help them with the rest of the project. Then, as needed, the specific chapters can be studied in detail when the organization is working on that part of a data warehouse initiative.

• Business personnel involved with a data warehouse can skim the chapters on setting up a project, but will find it helpful to study in depth Chapter 6 to learn about providing requirements, Chapter 7 to understand how the data should be organized, and Chapter 11 to learn about how the data can be delivered. It is recommended that the rest of the book be reviewed to familiarize yourself with the content. Specific chapters can be studied in more detail when the organization is working on that area.

• Everyone on the data warehouse project team should read this entire book. It can provide a common ground for dialogue and more meaningful discussions between the business and technical personnel. The project team can provide this book to their business counterparts and suggest specific chapters for them to read to better support the project.

• Technical staff can also benefit from this book, which can help anyone with in-depth technical knowledge to communicate with their managers and business counterparts about data warehousing. This provides the backdrop for all interactions with the business community.

When designed and implemented properly, a data warehouse is a valuable tool for an organization to improve how it runs. Getting the right design requires active participation of knowledgeable business personnel. Keeping things on track requires the support of middle managers to ensure that everything is progressing smoothly. Executives and senior managers will be able to ask meaningful questions about data warehouse projects and be able to understand the answers. All of these things require that business and technical staff have a common understanding of the different parts of a data warehouse and what is involved in a successful project. It is hoped that this book provides the foundation you and your organization need to achieve success with all of your data warehouse initiatives.

PART I

The Essentials of Data Warehousing

CHAPTER 1

Gaining Data Warehouse Success

You’ve heard about them. You may have used one. You may be asked to pay for one. But what is a data warehouse and why should you invest any time, energy, and money on one? The short answer is that a data warehouse can help your organization to be more profitable, run more efficiently, and meet the challenges of today’s marketplace. Yet it is not a quick, simple, or inexpensive undertaking to build a data warehouse.

There is often a disconnect between the technical side that builds and maintains a data warehouse and the business side that will use it. This book will help bridge that gap. Both business managers and IT managers will learn what is involved with building and deploying a successful data warehouse. Executives and senior managers will also find this book helpful, especially Part 1, in order to be able to provide effective oversight and support. This book will also be beneficial for all business and technical personnel involved with a data warehouse, providing a common foundation for better communication. Managers on both sides need the knowledge and information that will enable them to help their organization build and use a data warehouse most effectively, and this book is the path to that knowledge.

This chapter explains the value of a data warehouse and highlights what is needed for success. To help frame the discussion, the chapter begins with some definitions.

The Essentials of Data Warehousing

Data warehousing is not new. Most large organizations have been investing in data warehousing for years. Currently, cost-effective technology is creating more possibilities for small and medium-size companies to build and deploy data warehouse solutions too. There are many stories about wild successes, and just as many about failed projects. With so much buzz about data warehousing, it is often assumed that everyone already knows the basics. However, many people are being exposed to these concepts for the first time. To ensure a common understanding, it is worth taking the time to boil things down to the essence of data warehousing.

What Is a Data Warehouse?

A data warehouse (DW) is the collection of processes and data whose overarching purpose is to support the business with its analysis and decision-making. In other words, it is not one thing per se, but a collection of many different parts. Before looking more closely at the specific parts of a data warehouse environment, it is helpful to compare the characteristics and purpose of a data warehouse with an operational application system.

Differences Between Operational and DW Systems

Applications that run the business are called online transaction processing systems (OLTPs). OLTP systems are geared toward functions such as processing incoming orders, getting products shipped out, and transferring funds as requested. These applications must ensure that transactions are handled accurately and efficiently. No one wants to wait minutes to get cash from an automated teller machine, or to enter sales orders into a company’s system.

In contrast, the purpose and characteristics of a data warehousing environment are to provide data in a format easily understood by the business community in order to support decision-making processes. The data warehouse supports looking at the business data over time to identify significant trends in buying behavior, customer retention, or changes in employee productivity. Table 1-1 lays out the primary differences between these two types of systems.

The inherent differences between the functions performed in OLTP and DW systems result in methodology, architecture, tool, and technology differences. Data warehousing emerged as an outgrowth of necessity, but has blossomed into a full-fledged industry that serves a valuable function in the business community.

Now that the differences between data warehouse and OLTP systems have been reviewed, it is time to look deeper into the makeup of the data warehouse itself.

The Data Warehousing Environment

There are many different parts of a data warehouse environment, which encompasses everything from where the data lives today through where it is ultimately used on reports and for analysis. Each of the main parts of the data warehousing environment, shown in Figure 1-1, are described in the following sections. This figure indicates how the data flows throughout the environment.