book

A Manager's Guide to Data Warehousing

Name: A Manager's Guide to Data Warehousing
Author: Laura Reeves
ISBN: 9780470176382

by Laura Reeves

May 2009

Intermediate to advanced

480 pages

12h 37m

English

Wiley

Read now

Unlock full access

Copyright
About the Author
Credits
Acknowledgments
Introduction
Overview of the BookHow This Book Is OrganizedWho Should Read This Book
I. The Essentials of Data Warehousing
1. Gaining Data Warehouse Success
1.1. The Essentials of Data Warehousing1.1.1. What Is a Data Warehouse?1.1.1.1. Differences Between Operational and DW Systems1.1.1.2. The Data Warehousing Environment1.1.1.3. What Is a Data Model?1.1.1.4. Understanding Industry Perspectives1.1.1.5. Design and Development Sequence1.1.2. Why Build a Data Warehouse?1.1.3. The Value of Data Warehousing1.2. The Promises of Data Warehousing1.2.1. Keys to Success1.2.1.1. Developing and Maintaining Strong Business and Technology Partnerships1.2.1.2. Identifying True Business Requirements1.2.1.3. Shifting to a Global Perspective1.2.1.4. Overcoming Unrealistic Expectations1.2.1.5. Providing Clear Communication1.2.1.6. Treating Data As a Corporate Asset1.2.1.7. Effectively Leveraging Technology1.2.2. Roadblocks to Success1.2.2.1. Believing the Myth: "If You Build It, They Will Come"1.2.2.2. Falling into the Project Deadline Trap1.2.2.3. Failing to Uphold Organizational Discipline1.2.2.4. Lacking Business Process Change1.2.2.5. Narrowing the Focus Too Much1.2.2.6. Resting on Your Laurels1.2.2.7. Relying on the Technology Fix1.2.2.8. Getting the Right People Involved1.2.2.9. Finding Lost Institutional Knowledge1.3. Summary
2. The Executive's FAQ for Data Warehousing
2.1. Question: What is the business benefit of a data warehouse?2.1.1. Answer2.2. Question: How much will it cost?2.2.1. Answer2.3. Question: How long will it take?2.3.1. Answer2.4. Question: How can I ensure success?2.4.1. Answer2.5. Question: Do other companies really build these in 90 days?2.5.1. Answer2.6. Question: How will we know we are doing this right?2.6.1. Answer2.7. Question: Why didn't this work last time? What is different this time?2.7.1. Answer2.8. Question: Do we have the right technology in place?2.8.1. Answer2.9. Question: Are we the only company with data warehouse problems?2.9.1. Answer2.10. Question: Will I get one version of the truth?2.10.1. Answer2.11. Question: Why can't we just use our current systems?2.11.1. Answer2.12. Question: Will the data warehouse replace our old systems?2.13. Question: Who needs to be involved?2.14. Question: Do we know where we are going? How will we know when we get there?2.14.1. Answer2.15. Question: How do we get started and stay focused?2.15.1. Answer2.16. Summary
II. The Business Side of Data Warehousing
3. Understanding Where You Are and Finding Your Way
3.1. Assessing Your Current State3.1.1. What Is Your Company's Strategic Direction?3.1.2. What Are the Company's Top Initiatives?3.1.3. How Healthy Is Your Data?3.1.4. Does the Business Place Value on Analysis?3.1.5. Reflecting on Your Data Warehouse History3.2. Understanding Your Existing Reporting Environment3.2.1. Finding the Reporting Systems3.2.2. Compiling an Inventory3.2.2.1. Identifying the Business Purpose3.2.2.2. Discovering the Data You Already Have3.2.2.3. Understanding the People3.2.2.4. Tracking Technology and Tools3.2.2.5. Understanding Enterprise Resources3.2.3. Netting It All Out3.3. Introducing the Case Studies3.3.1. The Call Center Data Warehouse Project3.3.2. In Real Life3.3.2.1. Giant Company3.3.2.2. Agile, Inc3.4. Summary

4. Successful IT–Business Partnerships
4.1. What a Partnership Really Means4.2. What the Business Partners Should Expect to Do4.2.1. Business Executives and Senior Management4.2.2. The Executive Business Sponsor4.2.3. Business Managers4.2.4. The Business Champion4.2.5. Business Analysts4.2.5.1. Helping the Business Analyst Deal with Change4.2.6. Business User Audience4.2.7. Project Manager4.3. What You Should Expect from IT4.3.1. CIO/IT Executive Sponsor4.3.2. Data Warehouse Manager4.3.3. Business Systems Analyst4.3.4. Source System Analyst4.3.5. Data Modeler/Data Architect4.3.6. ETL Developer(s)4.3.7. Business Intelligence Application Developer4.3.8. Other Supporting Roles4.4. Tips for Building and Sustaining a Partnership4.4.1. Leveraging External Consulting4.4.2. Building Strong Project Teams4.5. Effective Communication4.5.1. Netting Out Key Messages4.5.2. Presenting in Business Terms4.5.3. Meeting Preparation4.5.3.1. Presentation Tips4.5.4. When to Communicate4.6. Partnerships Beyond a Project4.6.1. The Decision-Making Process4.6.1.1. Executive Steering Committee4.6.1.2. DW Business Support Team4.6.2. Enterprise Considerations4.7. In Real Life4.7.1. A Glimpse into Giant, Co4.7.2. Insight from Agile, Inc4.8. Summary
5. Setting Up a Successful Project
5.1. Defining the Project5.1.1. Setting Up the Project Charter5.1.2. Documenting Project Scope5.1.3. Developing a Statement of Work5.1.3.1. How Much Will It Cost?5.1.4. Project Approval5.2. Starting the Project5.2.1. Launching the Project5.3. Managing a Successful Project5.3.1. Issue Tracking5.3.2. Using Project Change Control5.3.3. Discussing Change in Business Terms5.3.4. Managing Expectations5.4. In Real Life5.4.1. Structured Projects with Giant5.4.2. Freedom for Creativity at Agile, Inc5.5. Summary
6. Providing Business Requirements
6.1. What Requirements Are Needed?6.1.1. Peeling Back the Layers of Requirements Gathering6.1.1.1. Who Provides Input?6.1.1.2. Who Gathers the Requirements?6.2. Providing Business Requirements6.2.1. Strategic Requirements6.2.2. Broad Business Requirements6.2.3. Business Analyses6.2.4. Business Data Requirements6.2.4.1. Systems and Technical Requirements6.3. Communicating What You Really Need6.3.1. What Else Would Help the Project Team?6.3.1.1. Data Integration Challenges6.3.1.2. Assess Organizational Motivation6.3.1.3. Complete Picture of the Data6.3.2. What If No One Is Asking?6.4. Practical Techniques for Gathering Requirements6.4.1. Interview Session Characteristics6.4.1.1. Individual Interviews6.4.1.2. Group Interviews6.4.1.3. Project Team Participation6.4.1.4. Interview Tips6.4.2. Who Needs to Be Included?6.4.2.1. Setting a Good Example6.4.3. Preparing for Interview Sessions6.4.4. Conducting the Interview Sessions6.4.4.1. Capturing Content: Notes vs. Tapes6.4.4.2. Running the Interview6.4.4.3. Concluding the Interview6.5. Putting the Pieces Together6.5.1. Individual Interview Documentation6.5.1.1. Responsibilities6.5.1.2. Business Themes6.5.1.3. Business Data6.5.2. Consolidated Requirements Documentation6.5.2.1. Executive Summary6.5.2.2. Consolidated Business Themes6.5.2.3. Candidate Business Analyses6.5.2.4. Consolidated Business Data Requirements6.5.2.5. Identification of Non-Data Warehouse Requirements6.5.3. Common Requirements Gathering Challenges6.5.3.1. Sifting Through Reports6.5.3.2. Listing Data Elements6.5.3.3. Developing Functional Specifications6.5.3.4. Moving Beyond Immediate6.5.3.5. Lack of Requirements6.5.3.6. The Cynic6.6. Setting Attainable Goals6.6.1. Exploring Alternatives6.6.2. Setting Priorities6.7. In Real Life6.7.1. A Glimpse into Giant Company6.7.2. Insight from Agile, Inc6.8. Summary
III. Dealing with the Data
7. Modeling the Data for your Business
7.1. The Purpose of Dimensional Models7.1.1. Ease of Use7.1.2. Query Performance7.2. Understanding Your Data7.3. What Is a Dimensional Model?7.3.1. Dimensions7.3.2. Facts7.3.3. Using Both Parts of the Model7.3.4. Implementing a Dimensional Model7.3.5. Diagramming Your Dimensional Model7.4. The Business Dimensional Model7.4.1. Business Dimensions7.4.2. Fact Groups7.5. A Call Center Case Study7.5.1. Call Center Dimensions7.5.1.1. Date Dimension7.5.1.2. Time Dimension7.5.1.3. Customer Dimension7.5.1.4. Employee Dimension7.5.1.5. Call Dimension7.5.1.6. Call Outcome Dimension7.5.1.7. Employee Task Dimension7.5.2. Call Center Fact Groups7.5.2.1. Calls Fact Group7.5.2.2. Call Center Time Tracking Fact Group7.5.2.3. Call Forecast Fact Group7.5.3. Working with the Model7.5.4. Business Dimensional Model Index7.6. Enterprise Considerations7.6.1. Conformed Dimensions7.6.2. Conformed Facts7.6.3. Practical Guidelines7.6.3.1. Guidelines for a Single Dimension7.6.3.2. Guidelines for a Single Fact Group7.6.3.3. Characteristics of the Model across the Enterprise7.7. Business Participation in the Modeling Process7.7.1. Creating the First Draft7.7.2. Preparing for Modeling Sessions7.7.2.1. Brainstorming the Framework7.7.2.2. Drafting the Initial Dimensions7.7.2.3. Drafting the Initial Fact Groups7.7.2.4. Documenting the Model7.7.2.5. Logging Questions and Issues7.7.2.6. Building the Business Measures Worksheet7.7.2.7. Preliminary Source to Target Data Map7.7.3. Completing or Fleshing Out the Model7.7.3.1. Working Through the Issues7.7.3.2. Completing the Documentation7.7.3.3. Working Through All the Data Elements7.7.3.4. Refining the Model7.7.4. Business Reviews of the Model7.7.4.1. Small Business Reviews7.7.4.2. When Are You Done?7.7.4.3. Gaining Final Commitment7.7.5. Expanding Business Data Over Time7.7.5.1. Enhancing Dimensions7.7.5.2. Adding More Fact Groups7.8. Reflecting on Business Realities: Advanced Concepts7.8.1. Supporting Multiple Perspectives: Multiple Hierarchies7.8.2. Tracking Changes in the Dimension: Slowly Changing Dimensions7.8.3. Depicting the Existence of a Relationship: Factless Fact Tables7.8.4. Linking Parts of a Transaction: Degenerate Dimensions7.8.5. Pulling Together Components: Junk Dimensions7.8.6. Multiple Instances of a Dimension: Role Playing7.8.7. Other Notation7.8.7.1. Dimension Connectors7.8.7.2. Clusters of Future Attributes7.8.7.3. Notation Summary7.9. Taking the Model Forward7.9.1. Translating the Business Dimensional Model7.9.1.1. Dimension Table Design7.9.1.2. Translating Fact Groups7.9.1.3. Physical Database Design7.10. In Real Life7.10.1. A Glimpse into Giant Co7.10.2. Insight from Agile, Inc.7.11. Summary
8. Managing Data As a Corporate Asset
8.1. What Is Information Management?8.1.1. Information Management Example—Customer Data8.1.2. IM Beyond the Data Warehouse8.2. Master Data Management8.2.1. Master Data Feeds the Data Warehouse8.2.2. Finding the Right Resources8.3. Data Governance8.3.1. Data Ownership8.3.1.1. Who Really Owns the Data?8.3.1.2. Your Responsibilities If You Are "the Owner"8.3.1.3. What are IT's Responsibilities?8.3.1.4. Challenges with Data Ownership8.3.2. Data Quality8.3.2.1. Profiling the Data8.3.2.2. How Clean Does the Data Really Need to Be?8.3.2.3. Measuring Quality8.3.2.4. Quality of Historical Data8.3.2.5. Cleansing at the Source8.3.2.6. Cleaning Up for Reporting8.3.2.7. Managing the Integrity of Data Integration8.3.2.8. Quality Improves When It Matters8.3.2.9. Example: Data Quality and Grocery Checkout Scanners8.3.2.10. Example: Data Quality and the Evaluation of Public Education8.3.2.11. Realizing the Value of Data Quality8.4. Implementing a Data Dictionary8.4.1. The Data Dictionary Application8.4.2. Populating the Data Dictionary8.4.3. Accessing the Data Dictionary8.4.4. Maintaining the Data Dictionary8.5. Getting Started with Information Management8.5.1. Understanding Your Current Data Environment8.5.1.1. What Data Do You Have?8.5.1.2. What Already Exists?8.5.2. Where Do You Want to Be?8.5.3. Develop a Realistic Strategy8.5.3.1. Sharing the Information Management Strategy8.5.4. Setting Up a Sustainable Process8.5.4.1. Enterprise Commitment8.5.4.2. The Data Governance Committee8.5.4.3. Revising the Strategy8.6. In Real Life8.6.1. A Glimpse into Giant, Co8.6.2. Insight from Agile, Inc8.7. Summary
IV. Building the Project
9. Architecture, Infrastructure, and Tools
9.1. What Is Architecture?9.2. Why Do We Need Architecture?9.2.1. Making Architecture Work9.3. Data Architecture9.3.1. Revisiting DW Goals9.3.2. Components of DW Data Architecture9.3.3. A Closer Look at Common Data Warehouse Architectures9.3.3.1. Bottom-Up Data Architecture9.3.3.1.1. Capture/Create the Data: Source Systems9.3.3.1.2. Extract the Data9.3.3.1.3. Prepare the Data: The ETL Data Stores9.3.3.1.4. Publish the Data: The Presentation Server9.3.3.1.5. Use the Data: Business Intelligence Applications9.3.3.2. Top-Down Data Architecture9.3.3.2.1. Capture/Create the Data9.3.3.2.2. Extract the Data: The Staging Data Store9.3.3.2.3. Prepare the Data: The Data Warehouse9.3.3.3. Publish the Data: Data Marts9.3.3.3.1. Use the Data: Business Intelligence Applications9.3.3.4. Adopting an Architecture9.3.3.4.1. Common Mistakes Adopting the Top-Down Approach9.3.3.4.2. Common Mistakes Adopting the Bottom-Up Approach9.3.3.4.3. Success Factors for Both Approaches9.4. Technical Architecture9.4.1. Technical Architecture Basics9.4.2. Components of Technical Architecture9.4.3. Infrastructure9.4.4. Technical Architecture in Action9.4.4.1. What You Need to Know about Technical Architecture9.5. Navigating the Technology Jungle9.5.1. Weighing Technology Options9.5.1.1. Best of Breed9.5.1.2. End-to-End Solutions9.5.1.3. Deciding Not to Buy a Tool9.5.2. Finding the Right Products9.5.2.1. Requests for Information or Proposals9.5.2.2. Business Participation in the Selection Process9.5.2.3. Understanding Product Genealogy9.5.3. Understanding Value and Evaluating Your Options9.5.3.1. Cutting through the Marketing Hype9.5.4. The Value of References9.6. Making Architecture Work for You9.6.1. Just-In-Time Architecture9.7. In Real Life9.7.1. Architecture at Giant9.7.2. Agile Ignores the Need for Architecture9.8. Summary
10. Implementation: Building the Database
10.1. Extract, Transform, and Load (ETL) Fundamentals10.1.1. What Work Is Being Done?10.1.2. ETL System Functionality10.1.2.1. Extraction10.1.2.2. Transformation10.1.2.3. Load10.2. The Business Role in ETL10.2.1. Why Does the Business Need to Help?10.2.2. Defining Business Rules10.2.3. Defining Expected Results–The Test Plan10.2.4. Development Support10.2.5. Testing the ETL System–Is the Data Right?10.2.6. Why Does It Take So Long and Cost So Much?10.3. Balancing Requirements and Data Reality10.3.1. Discovering the Flaws in Your Current Systems10.3.2. Applying New Business Rules10.3.3. Working Toward Long-Term Solutions10.3.4. Manually Including Business Data10.4. Tracking Progress–Are We There Yet?10.5. What Else Can You Do to Help?10.5.1. Encouragement and Support10.5.2. Ensuring Continued Business Participation10.5.3. Proactive Communication10.6. In Real Life10.6.1. Building the Data Warehouse at Giant, Co.10.6.2. Agile, Inc., Builds a Data Warehouse Quickly10.7. Summary
11. Data Delivery: What you Finally See
11.1. What Is Business Intelligence?11.1.1. Business Intelligence without a DW11.1.2. BI in Action11.1.2.1. Tabular Reports11.1.2.2. Parameter-Driven Reports11.1.2.3. Interactive Reports–Drilling Down and Across11.1.2.4. Exception Reports11.1.2.5. Other BI Capabilities11.1.2.6. Complex Analysis11.2. BI Building Blocks11.2.1. Data Content–Understanding What You Have11.2.2. Navigation–Finding What You Need11.2.3. Presentation–How Do You Want to See Results?11.2.4. Delivery–How Do You Receive the Results?11.3. Supporting Different Levels of Use11.4. Construction of the BI Solution11.4.1. Planning for Business Change11.4.2. Design–What Needs to Be Delivered?11.4.3. Development11.4.4. Testing BI Applications and Validating Data11.4.5. Additional Responsibilities11.4.5.1. Security–Who Can Look at the Data?11.4.5.2. System Controls–Who Can Change What?11.5. Planning a Successful Launch11.5.1. Marketing the Solution11.5.2. Learning to Use the Data without a Technical Degree11.5.2.1. Learning about the Data11.5.2.2. Learning about the BI Tool/Application11.5.3. Ensuring That the Right Help Is Available11.6. In Real Life11.6.1. BI at Giant Company11.6.2. Agile, Inc. Dives into BI11.7. Summary
V. Next Steps–Expanding on Success
12. Managing the Production Data Warehouse
12.1. Finishing the Project12.1.1. Recapping the BI Application Launch12.1.2. Post-Implementation Review12.1.2.1. Looking Back—Did you Accomplish Your Objectives?12.2. Adopting the Solution12.2.1. Tracking Data Warehouse Use12.2.2. Getting the Rest of the Business Community on Board12.2.3. Business Process Change12.2.3.1. Changing How Data Is Used12.2.3.2. Streamlining Business Processes12.2.3.3. Encouraging Change12.3. The Production Data Warehouse12.3.1. Staffing Production Activities12.3.2. Maintaining the Environment12.3.2.1. Keeping Up with Technology12.3.2.1.1. The Importance of Testing12.3.2.1.2. Falling Behind12.3.2.2. Monitoring Performance and Capacity Planning12.3.3. Maintaining the Data Warehouse12.3.3.1. Maintaining the ETL System12.3.3.2. Maintaining the BI Application12.3.4. Tracking Questions and Problems12.3.5. Fixing Bugs12.4. When the Data Warehouse Falls Short12.4.1. Common Causes for a Stalled Warehouse12.4.2. Jump-Starting a Stalled Data Warehouse12.4.2.1. Conducting an Assessment12.4.2.2. Determining What Can Be Salvaged12.4.2.3. Developing a Plan to Move On12.4.2.4. Aligning DW Objectives with Business Goals12.4.2.5. Getting It Right This Time12.4.3. Launching the Improved Data Warehouse and BI Solution12.5. In Real Life12.5.1. Lack of Support for the Production DW at Giant Co.12.5.2. Unleashing BI at Agile, Inc12.6. Summary
13. Achieving Long-Term Success
13.1. Planning for Expansion and Growth13.1.1. Exploring Expansion Opportunities13.1.2. Prioritization of Feedback13.2. Managing Enterprise DW Resources13.2.1. Creating an Enterprise Data Warehouse Team13.2.1.1. The Centralized Enterprise Data Warehouse Team13.2.1.2. The Virtual Enterprise Data Warehouse Team13.2.2. Enterprise DW Team Responsibilities13.2.3. Funding the Enterprise DW Team13.3. Pushing into the Future13.3.1. Embedded Business Intelligence13.3.2. Operational Business Intelligence13.3.3. Real-Time Data Warehousing13.3.4. Unstructured Data13.3.5. Monitoring Industry Innovation13.4. Moving Toward Business Value13.4.1. Measuring Success One Step at a Time13.4.2. Adjusting Expectations to Reality13.4.3. Keeping the Momentum Going13.4.4. Celebrating Progress13.4.5. Success Can Be Attained13.5. Conclusion
Glossary

Content preview from A Manager's Guide to Data Warehousing

Chapter 3. Understanding Where You Are and Finding Your Way

Most organizations already have some mechanism in place to meet their reporting needs. This mechanism may be a set of reports that run directly against the production application systems or reports generated from an existing data warehouse. Many organizations have multiple data warehouse attempts under their belt. Some data warehouses have been successful, while others have never delivered value. In many cases, reports and analysis are not supported in a formal system at all. Data is pulled from wherever it can be found, and manually merged into spreadsheets to create reports and presentations, and used for analysis.

Because you are not starting with a clean slate, it is important to get a realistic understanding of what reporting and/or data warehouse components and resources are available and to what extent they are being used.

Making an honest assessment of what has been accomplished to date is often one of the most difficult things to do. Several years of hard work may have been invested to build the system currently in place. It usually takes longer and costs more than what was originally planned, and the results may have fallen short of expectations. What should be done? Take a deep breath, step back, and take a little time for reflection. This chapter lays out the steps to help:

Understand the current business situation.
Identify how reporting is currently accomplished.
Assess the effectiveness of existing reporting mechanisms. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470176382Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

A Manager's Guide to Data Warehousing

by Laura Reeves

Chapter 3. Understanding Where You Are and Finding Your Way

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.