Data Warehousing Architecture and Implementation

by Humphries, Mark W.; Hawkins, Michael W.; Dy, Michelle C.

• ISBN13: 9780130809025
• ISBN10: 0130809020
• Edition: 1st
• Format: Paperback
• Copyright: 1998-12-30
• Publisher: Prentice Hall

Summary

Preface
This book is intended for Information Technology (IT) professionals who have been hearing about or have been tasked to evaluate, learn or implement data warehousing technologies.

Far from being just a passing fad, data warehousing technology has grown much in scale and reputation in the past few years, as evidenced by the increasing number of products, vendors, organizations, and yes, even books, devoted to the subject. Enterprises that have successfully implemented data warehouses find it strategic and often wonder how they ever managed to survive without it in the past.

As early as 1995, a Gartner Group survey of Fortune 500 IT managers found that 90 percent of all organizations had planned to implement data warehouses by 1998. Virtually all Top-100 US banks will actively use a data warehouse-based profitability application by 1998. Nearly 30 percent of companies that actively pursue this technology have created a permanent or semipermanent unit to plan, create, maintain, promote, and support the data warehouse.

If you are an IT professional who has been tasked with planning, managing, designing, implementing, supporting, or maintaining your organization's data warehouse, then this book is intended for you. The first section introduces the Enterprise Architecture and Data Warehouse concepts, the basis of the reasons for writing this book.

The second section of this book focuses on three of the key People in any data warehousing initiative: the Project Sponsor, the CIO, and the Project Manager. This section is devoted to addressing the primary concerns of these individuals.

The third section presents a Process for planning and implementing a data warehouse and provides guidelines that will prove extremely helpful for both first-time and experienced warehouse developers. The fourth section of this book focuses on the Technology aspect of data warehousing. It lends order to the dizzying array of technology components that you may use to build your data warehouse. The fifth section of this book opens a window to the future of data warehousing.

This book also comes with a CD-ROM that contains two software products. Please refer to the readme.txt file on the CD-ROM for any last minute changes and updates.

The enclosed software products are:

• R/olapXL -- R/olapXL is a powerful query and reporting tool that allows users to draw data directly into Microsoft Excel spreadsheets from any dimensional data mart or data warehouse that resides on an ODBC-compliant database. Once the data are in Microsoft Excel, you are free to use any of Excel's standard features to analyze, report, or graph the retrieved data.
• Warehouse Designer -- Warehouse Designer is a tool that generates DDL statements for creating dimensional data warehouse or data mart tables. Users specify the required data structure through a GUI front-end. The tool generates statements to create primary keys, foreign keys, indexes, constraints, and table structures. It recognizes key dimensional modeling concepts such as fact and dimension tables, core and custom schemas, as well as base and aggregate schemas.

Also enclosed is a License Agreement that you must read and agree to before using any of the software provided on the disk. Manuals for both products are included as appendices in this book. The latest information on these products is available at the website of Intranet Business Systems, Inc. The URL is http://www.intranetsys.com.

Author Biography

Mark Humphries is President/CEO of Intranet Business Systems, Inc., The Philippines. He has 13 years experience in management and consulting throughout Europe, North America, and Asia, and led major warehousing projects for two U.S. utilities.

Michelle Dy, Business Analyst at Intranet Business Systems, Inc., specializes in methods consulting, BPR, planning, analysis, and design.

Table of Contents

Preface	xvii
Part 1 Introduction	1	(48)
Chapter 1 The Enterprise IT Architecture	3	(28)
The Past: Evolution of Enterprise Architectures	3	(1)
The Present: The IT Professional's Responsibility	4	(2)
Business Perspective	6	(2)
Technology Perspective	8	(6)
Architecture Migration Scenarios	14	(11)
Migration Strategy: How Do We Move Forward?	25	(3)
In Summary	28	(3)
Chapter 2 Data Warehouse Concepts	31	(18)
Gradual Changes in Computing Focus	31	(3)
The Data Warehouse Defined	34	(3)
The Dynamic, Ad Hoc Report	37	(1)
The Purposes of a Data Warehouse	38	(3)
A Word about Data Marts	41	(1)
A Word about Operational Data Stores	42	(3)
Data Warehouse Cost-Benefit Analysis / Return On Investment	45	(3)
In Summary	48	(1)
Part 2 People	49	(72)
Chapter 3 The Project Sponsor	51	(22)
How Will a Data Warehouse Affect our Decision-Making Processes?	51	(2)
How Does a Data Warehouse Improve My Financial Processes? Marketing? Operations?	53	(2)
When Is a Data Warehouse Project Justified?	55	(2)
What Expenses Are Involved?	57	(3)
What Are the Risks?	60	(7)
Risk-Mitigating Approaches	67	(1)
Is My Organization Ready for a Data Warehouse?	68	(2)
How Do I Measure the Results?	70	(2)
In Summary	72	(1)
Chapter 4 The CIO	73	(30)
How Do I Support the Data Warehouse?	73	(2)
How Will My Data Warehouse Evolve?	75	(2)
Who Should Be Involved in a Data Warehouse Project?	77	(5)
What Is the Team Structure Like?	82	(2)
What New Skills Will My People Need?	84	(2)
How Does Data Warehousing Fit into My IT Architecture?	86	(1)
How Many Vendors Do I Need to Talk To?	87	(2)
What Should I Look for in a Data Warehouse Vendor?	89	(5)
How Does Data Warehousing Affect My Existing Systems?	94	(1)
Data Warehousing and its Impact on Other Enterprise Initiatives	95	(2)
When Is a Data Warehouse Not Appropriate?	97	(2)
How Do I Manage or Control a Data Warehouse Initiative?	99	(2)
In Summary	101	(2)
Chapter 5 The Project Manager	103	(18)
How Do I Roll Out a Data Warehouse Initiative?	103	(4)
How Important Is the Hardware Platform?	107	(2)
What Technologies Are Involved?	109	(1)
Do I Still Use Relational Databases for Data Warehousing?	110	(6)
How Long Does a Data Warehousing Project Last?	116	(1)
How Is a Data Warehouse Different from Other IT Projects?	117	(1)
What Are the Critical Success Factors of a Data Warehousing Project?	118	(2)
In Summary	120	(1)
Part 3 Process	121	(68)
Chapter 6 Warehousing Strategy	123	(12)
Strategy Components	123	(1)
Determine Organizational Context	124	(1)
Conduct Preliminary Survey of Requirements	125	(3)
Conduct Preliminary Source System Audit	128	(1)
Identify External Data Sources (If Applicable)	129	(1)
Define Warehouse Rollouts (Phased Implementation)	130	(1)
Define Preliminary Data Warehouse Architecture	131	(1)
Evaluate Development and Production Environments and Tools	132	(1)
In Summary	133	(2)
Chapter 7 Warehouse Management and Support Processes	135	(8)
Define Issue Tracking and Resolution Process	135	(2)
Perform Capacity Planning	137	(1)
Define Warehouse Purging Rules	138	(1)
Define Security Measures	139	(1)
Define Backup and Recovery Strategy	139	(1)
Set Up Collection of Warehouse Usage Statistics	140	(1)
In Summary	141	(2)
Chapter 8 Data Warehouse Planning	143	(22)
Assemble and Orient Team	144	(1)
Conduct Decisional Requirements Analysis	144	(2)
Conduct Decisional Source System Audit	146	(4)
Design Logical and Physical Warehouse Schema	150	(1)
Produce Source-to-Target Field Mapping	151	(2)
Select Development and Production Environment and Tools	153	(1)
Create Prototype for This Rollout	154	(1)
Create Implementation Plan for this Rollout	155	(3)
Warehouse Planning Tips and Caveats	158	(4)
In Summary	162	(3)
Chapter 9 Data Warehouse Implementation	165	(24)
Acquire and Set Up Development Environment	166	(1)
Obtain Copies of Operational Tables	166	(1)
Finalize Physical Warehouse Schema Design	167	(1)
Build or Configure Extraction and Transformation Subsystems	168	(3)
Build or Configure Data Quality Subsystem	171	(4)
Build Warehouse Load Subsystem	175	(4)
Set-up Data Warehouse Schema	179	(1)
Set Up Data Warehouse Metadata	180	(1)
Set Up Data Access and Retrieval Tools	181	(2)
Perform the Production Warehouse Load	183	(1)
Conduct User Training	184	(2)
Conduct User Testing and Acceptance	186	(1)
In Summary	187	(2)
Part 4 Technology	189	(58)
Chapter 10 Hardware and Operating Systems	191	(4)
Parallel Hardware Technology	191	(1)
Hardware Selection Criteria	192	(2)
In Summary	194	(1)
Chapter 11 Warehousing Software	195	(16)
Overview	195	(1)
Middleware and Connectivity Tools	196	(1)
Extraction Tools	197	(2)
Transformation Tools	199	(2)
Data Quality Tools	201	(1)
Data Loaders	202	(1)
Database Management Systems	202	(2)
Metadata Repository	204	(1)
Data Access and Retrieval Tools	204	(4)
Data Modeling Tools	208	(1)
Warehouse Management Tools	209	(1)
Source Systems	209	(1)
In Summary	210	(1)
Chapter 12 Warehouse Schema Design	211	(18)
OLTP Systems Use Normalized Data Structures	211	(3)
Dimensional Modeling for Decisional Systems	214	(1)
Two Types of Tables: Facts and Dimensions	214	(2)
A Schema Is a Fact Table and Its Related Dimension Tables	216	(1)
Facts Are Fully Normalized, Dimensions Are Denormalized	216	(1)
Dimensional Hierarchies and Hierarchical Drilling	217	(2)
The Time Dimension	219	(1)
The Grain of the Fact Table	220	(1)
The Fact Table Key Is the Concatenation of Dimension Keys	220	(1)
Aggregates or Summaries	221	(3)
Dimensional Attributes	224	(1)
Multiple Star Schemas	225	(1)
Core and Custom Tables	225	(1)
In Summary	226	(3)
Chapter 13 Warehouse Metadata	229	(10)
Metadata Are a Form of Abstraction	229	(1)
Why Are Metadata Important?	230	(3)
Metadata Types	233	(3)
Versioning	236	(1)
Metadata as the Basis for Automating Warehousing Tasks	237	(1)
In Summary	237	(2)
Chapter 14 Warehousing Applications	239	(8)
The Early Adoptors	239	(1)
Types of Warehousing Applications	240	(2)
Specialized Applications of Warehousing Technology	242	(3)
In Summary	245	(2)
Part 5 Where to Now?	247	(20)
Chapter 15 Warehouse Maintenance and Evolution	249	(12)
Regular Warehouse Loads	249	(1)
Warehouse Statistics Collection	250	(1)
Warehouse User Profiles	251	(1)
Security and Access Profiles	252	(1)
Data Quality	252	(1)
Data Growth	253	(1)
Updates to Warehouse Subsystems	254	(1)
Database Optimization and Tuning	255	(1)
Data Warehouse Staffing	256	(1)
Warehouse Staff and User Training	257	(1)
Subsequent Warehouse Rollouts	258	(1)
Chargeback Schemes	258	(1)
Disaster Recovery	259	(1)
In Summary	259	(2)
Chapter 16 Warehousing Trends	261	(6)
Continued Growth of the Data Warehouse Industry	261	(1)
Increased Adoption of Warehousing Technology by More Industries	262	(1)
Increased Maturity of Data Mining Technologies	263	(1)
Emergence and Use of Metadata Interchange Standards	263	(1)
Increased Availability of Web-Enabled Solutions	263	(1)
Popularity of Windows NT for Data Mart Projects	264	(1)
Availability of Warehousing Modules for Application Packages	264	(1)
More Mergers and Acquisitions Among Warehouse Players	265	(1)
In Summary	265	(2)
Part 6 Appendices	267	(86)
Appendix A R/olapXL(R) User's Guide	269	(54)
Welcome to R/OLAPXL!	269	(4)
Installation	273	(2)
Tutorial	275	(4)
User's Guide	279	(2)
Working with R/OLAPXL Columns	281	(11)
Setting R/OLAPXL Options	292	(11)
The R/OLAPXL Toolbars	303	(2)
Macro Programming	305	(10)
R/OLAPXL Messages	315	(8)
Appendix B Warehouse Designer(R) User's Manual	323	(16)
Basic Concepts	327	(1)
The Warehouse Designer Toolbars	328	(2)
Applications	330	(1)
Dimensions	331	(3)
Schemas	334	(1)
Custom Dimensions	335	(1)
Custom Schemas	336	(1)
Aggregate Dimensions	336	(1)
Aggregate Schemas	337	(2)
Appendix C Online Data Warehousing Resources	339	(2)
Appendix D Tool and Vendor Inventory	341	(8)
Appendix E Software License Agreement	349	(4)
Part 7 References & Further Reading	353

Excerpts

Preface This book is intended for Information Technology (IT) professionals who have been hearing about or have been tasked to evaluate, learn or implement data warehousing technologies. Far from being just a passing fad, data warehousing technology has grown much in scale and reputation in the past few years, as evidenced by the increasing number of products, vendors, organizations, and yes, even books, devoted to the subject. Enterprises that have successfully implemented data warehouses find it strategic and often wonder how they ever managed to survive without it in the past. As early as 1995, a Gartner Group survey of Fortune 500 IT managers found that 90 percent of all organizations had planned to implement data warehouses by 1998. Virtually all Top-100 US banks will actively use a data warehouse-based profitability application by 1998. Nearly 30 percent of companies that actively pursue this technology have created a permanent or semipermanent unit to plan, create, maintain, promote, and support the data warehouse. If you are an IT professional who has been tasked with planning, managing, designing, implementing, supporting, or maintaining your organization's data warehouse, then this book is intended for you. The first section introduces the Enterprise Architecture and Data Warehouse concepts, the basis of the reasons for writing this book. The second section of this book focuses on three of the key People in any data warehousing initiative: the Project Sponsor, the CIO, and the Project Manager. This section is devoted to addressing the primary concerns of these individuals. The third section presents a Process for planning and implementing a data warehouse and provides guidelines that will prove extremely helpful for both first-time and experienced warehouse developers. The fourth section of this book focuses on the Technology aspect of data warehousing. It lends order to the dizzying array of technology components that you may use to build your data warehouse. The fifth section of this book opens a window to the future of data warehousing. This book also comes with a CD-ROM that contains two software products. Please refer to the readme.txt file on the CD-ROM for any last minute changes and updates. The enclosed software products are: R/olapXL -- R/olapXL is a powerful query and reporting tool that allows users to draw data directly into Microsoft Excel spreadsheets from any dimensional data mart or data warehouse that resides on an ODBC-compliant database. Once the data are in Microsoft Excel, you are free to use any of Excel's standard features to analyze, report, or graph the retrieved data. Warehouse Designer -- Warehouse Designer is a tool that generates DDL statements for creating dimensional data warehouse or data mart tables. Users specify the required data structure through a GUI front-end. The tool generates statements to create primary keys, foreign keys, indexes, constraints, and table structures. It recognizes key dimensional modeling concepts such as fact and dimension tables, core and custom schemas, as well as base and aggregate schemas. Also enclosed is a License Agreement that you must read and agree to before using any of the software provided on the disk. Manuals for both products are included as appendices in this book. The latest information on these products is available at the website of Intranet Business Systems, Inc. The URL is http://www.intranetsys.com .