The Data Warehouse Life Cycle Toolkit

1 I ii The Data Warehouse Lifecycle Toolkit Table of Contents Chapter 1 - The Chess Pieces Section 1 - Project Management and Requirements Chapter 2 - The business Dimensional Lifecycle Chapter 3 - Project Planning and Management Chapter 4 - Collecting the Requirements Section 2 - Data Design Chapter 5 - A First Course on Dimensional Modeling Chapter 6 - A Graduate Course on Dimensional Modeling Chapter 7 - Building Dimensional Models Section 3 - Architecture Chapter 8 - Introducing Data Warehouse Architecture Chapter 9 - Back Room Technical Architecture Chapter 10 - Architecture for the Front Room Chapter 11 - Infrastructure and Metadata Chapter 12 - A Graduate Course on the Internet and Security Chapter 13 - Creating the Architecture Plan and Selecting Products Section 4 Implementation Chapter 14 - A Graduate Course on Aggregates Chapter 15 - Completing the Physical Design Chapter 16 - Data Staging Chapter 17 - Building End User Applications Section 5 - Deployment and Growth Chapter 18 - Planning the Deployment Chapter 19 - Maintaining and Growing the Data Warehouse iii The Purpose of Each Chapter 1.

2 The Chess Pieces. As of the writing of this book, a lot of vague terminology wasbeing tossed around in the data Warehouse marketplace. Even the term data Warehouse has lost its precision. Some people are even trying to define the datawarehouse as a nonqueryable data resource! We are not foolish enough to think we can settle all the terminology disputes in these pages, but within this book we willstick to a very specific set of meanings. This chapter briefly defines all the importantterms used in data warehousing in a consistent way. Perhaps this is something like studying all the chess pieces and what they can do before attempting to play a chessgame.

3 We think we are pretty close to the mainstream with these definitions. Section 1: Project Management and Requirements 2. The business Dimensional Lifecycle. We define the complete business Dimensional Lifecycle from 50,000 feet. We briefly discuss each step and give perspective on the lifecycle as a whole. 3. Project Planning and Management. In this chapter, we define the project and talk about setting its scope within your environment. We talk extensively about thevarious project roles and responsibilities. You won t necessarily need a full headcountequivalent for each of these roles, but you will need to fill them in almost any imaginable project.

4 This is a chapter for managers. 4. Collecting the Requirements. Collecting the business and data requirements is the foundation of the entire data Warehouse effort or at least it should be. Collecting the requirements is an art form, and it is one of the least natural activities for an ISorganization. We give you techniques to make this job easier and hope to impress upon you the necessity of spending quality time on this step. Section 2: Data Design 5. A First Course on Dimensional Modeling. We start with an energetic argument for the value of dimensional modeling. We want you to understand the depth of ourcommitment to this approach.

5 After performing hundreds of data Warehouse designsand installations over the last 15 years, we think this is the only approach you can use to achieve the twin goals of understandability and performance. We then revealthe central secret for combining multiple dimensional models together into a coherentwhole. This secret is called conformed dimensions and conformed facts. We call this approach the Data Warehouse Bus Architecture. your computer has a backbone,called the computer bus, that everything connects to, and your data Warehouse has abackbone, called the data Warehouse bus, that everything connects to.

6 The remainder of this chapter is a self-contained introduction to the science of dimensional modeling for data warehouses. This introduction can be viewed as anappendix to the full treatment of this subject in Ralph Kimball s earlier book, The Data Warehouse Toolkit . 6. A Graduate Course on Dimensional Modeling. Here we collect all the hardest dimensional modeling situations we can think of. Most of these examples come fromspecific business situations, such as dealing with a monster customer list. 7. Building Dimensional Models. In this chapter we tackle the issue of how to create the right model within your organization.

7 You start with a matrix of data marts anddimensions, and then you design each fact table in each data mart according to the techniques described in Chapter 5. The last half of this chapter describes the real-life management issues in applying this methodology and building all the dimensional models needed in each data mart. Section 3: Architecture 8. Introducing Data Warehouse Architecture. In this chapter we introduce all the components of the technical architecture at a medium level of detail. This paints theoverall picture. The remaining five chapters in this section go into the specific areasof detail. We divide the discussion into data architecture, application architecture, and infrastructure.

8 If you follow the Data Warehouse Bus Architecture we developed inChapter 5, you will be able to develop your data marts one at a time, and you will end iv up with a flexible, coherent overall data Warehouse . But we didn t say it would beeasy. 9. Technical Back Room Architecture. We introduce you to the system components in the back room: the source systems, the reporting instance, the data staging area,the base level data Warehouse , and the business process data marts. We tell you what happened to the operational data store (ODS). We also talk about all theservices you must provide in the back room to get the data ready to load into yourdata mart presentation server.

9 10. Architecture for the Front Room. The front room is your publishing operation. You make the data available and provide an array of tools for different user needs. We give you a comprehensive view of the many requirements you must support in thefront room. 11. Infrastructure and Metadata. Infrastructure is the glue that holds the data Warehouse together. This chapter covers the nuts and bolts. We deal with the detailwe think every data Warehouse designer and manager need to know abouthardware, software, communications, and especially metadata. 12. A Graduate Course on the Internet and Security.

10 The Internet has a potentially huge impact on the life of the data Warehouse manager, but many data Warehouse managers are either not aware of the true impact of the Internet or they are avoidingthe issues. This chapter will expose you to the current state of the art on Internet-based data warehouses and security issues and give you a list of immediate actions to take to protect your installation. The examples throughout this chapter are slantedtoward the exposures and challenges faced by the data Warehouse owner. 13. Creating the Architecture Plan and Selecting Products. Now that you are a software, hardware, and infrastructure expert, you are ready to commit to a specificarchitecture plan for your organization and to choose specific products.