CHAPTER 4: Planning and Project Management

1. CHAPTER 4: Planning and Project Management

2. CHAPTER OBJECTIVES • Review the essentials of planning for a data warehouse. • Distinguish between data warehouse projects and OLTP system projects. • Learn how to adapt the life cycle approach for a data warehouse project. • Discuss project team organization, roles, and responsibilities. • Consider the warning signs and success factors.

3. Planning Your Data Warehouse • Key Issues • Business Requirements, Not Technology • The Data Warehouse Project • How is it Different? • The Life-Cycle Approach • The Development Phases • The Project Team • Organizing the Project Team • Roles and Responsibilities • Skills and Experience Levels • User Participation • Project Management Considerations • Possible scenarios of failure • Warning Signs • Success Factors

4. Planning Your Data Warehouse First, determine if your company really needs a data warehouse. Is it really ready for one? You need to develop criteria for assessing the value expected from your data warehouse. Your company has to decide on the type of data warehouse to be built and where to keep it. You have to ascertain where the data is going to come from and even whether you have all the needed data. You have to establish who will be using the data warehouse, how they will use it, and at what times.

5. Key Issues Planning for your data warehouse begins with a thorough consideration of the key issues. Answers to the key questions are vital for the proper planning and the successful completion of the project. Therefore, let us consider the pertinent issues, one by one.

6. Value and Expectations. Some companies jump into data warehousing without assessing the value to be derived from their proposed data warehouse. • First you have to be sure that, given the culture and the current requirements of your company, a data warehouse is the most viable solution. • After you have established the suitability of this solution, then only can you begin to enumerate the benefits and value propositions. • Will your data warehouse help the executives and managers to do better planning and make better decisions? • Is it going to increase market share? • If so, by how much? • What are the expectations? • What does the management want to accomplish through the data warehouse?

7. Risk Assessment. Planners generally associate project risks with the cost of the project. If the project fails, how much money will go down the drain? But the assessment of risks is more than calculating the loss from the project costs. • What are the risks faced by the company without the benefits derivable from a data warehouse? • What losses are likely to be incurred? • What opportunities are likely to be missed? • Risk assessment is broad and relevant to each business. • Use the culture and business conditions of your company to assess the risks. Include this assessment as part of your planning document.

8. Top-down or Bottom-up. In Chapter 2, we discussed the top-down and bottom-up approaches for building a data warehouse. The top-down approach is to start at the enterprise- wide data warehouse, although possibly build it iteratively. Then data from the overall, large enterprise-wide data warehouse flows into departmental and subject data marts. On the other hand, the bottom-up approach is to start by building individual data marts, one by one. The conglomerate of these data marts will make up the enterprise data warehouse.

9. Build or Buy. This is a major issue for all organizations. No one builds a data warehouse totally from scratch by in-house programming. There is no need to reinvent the wheel every time. A wide and rich range of third-party tools and solutions are available. • The real questions are: • How much of your data marts should you build yourselves? • How much of these may be composed of ready-made solutions? • What type of mix and match must be done? • In a data warehouse, there is a large range of functions. • Do you want to write more in-house programs for data extraction and data transformation? • Do you want to use in-house programs for loading the data warehouse storage? • Do want to use vendor tools completely for information delivery? • On the other hand, the buy option could lead to quick implementation if managed effectively.

10. Single Vendor or Best-of-Breed :Vendors come in a variety of categories. There are multiple vendors and products catering to the many functions of the data warehouse. • So what are the options? • How should you decide? • Two major options are: • Use the products of a single vendor • Use products from more than one vendor, selecting appropriate tools. • Choosing a single vendor solution has a few advantages: • High level of integration among the tools • Constant look and feel • Seamless cooperation among components • Centrally managed information exchange • Overall price negotiable • This approach will naturally enable your data warehouse to be well integrated and • function coherently. • Reviewing this specific option further, here are the major advantages of the best-of-breedsolution that combines products from multiple vendors: • Could build an environment to fit your organization • No need to compromise between database and support tools • Select products best suited for the function

11. Business Requirements, Not Technology Data warehousing is not about technology, it is about solving users’ need for strategic information. Do not plan to build the data warehouse before understanding the requirements. Start by focusing on what information is needed and not on how to provide the information. The basic structure and the architecture to support the user requirements are more important a preliminary survey of requirements. The outcome of this preliminary survey: 1- Will help you formulate the overall plan. 2- Will be crucial to set the scope of the project. 3- Will assist you in prioritizing and determining the rollout plan for individual data marts.

12. What types of information must you gather in the preliminary survey? At a minimum, obtain general information on the following from each group of users: • Mission and functions of each user group • Computer systems used by the group • Key performance indicators • Factors affecting success of the user group • Who the customers are and how they are classified • Types of data tracked for the customers, individually and groups • Products manufactured or sold • Categorization of products and services • Locations where business is conducted • Levels at which profits are measured—per customer, per product, per district • Levels of cost details and revenue • Current queries and reports for strategic information

13. The Data Warehouse Project Data warehouse projects are different from projects building the transaction processing systems. If you are new to data warehousing, your first data warehouse project will reveal the major differences.

14. How is it Different? Figure 4-2 lists the differences between Data Warehouse Project and OLTP System Project Data Warehouse: Distinctive Features ad Challenges for Project Management Figure 4-2 How a data warehouse project is different.

15. The Life-Cycle Approach As an IT professional you are all too familiar with the traditional system development life cycle (SDLC). You know how to begin with a project plan, move into the requirements analysis phase, then into the design, construction, and testing phases, and finally into the implementation phase. The life cycle approach accomplishes all the major objectives in the system development process. The life cycle methodology breaks down the project complexity and removes any ambiguity with regard to the responsibilities of project team members. A data warehouse project is complex in terms of tasks, technologies, and team member roles. But a one-size fits- all life cycle approach will not work for a data warehouse project. Adapt the life cycle approach to the special needs of your data warehouse project. Remember that the broad functional components of a data warehouse are data acquisition, data storage, and information delivery. Make sure the phases of your development life cycle wrap around these functional components. Figure 4-3 shows how to relate the functional components to SDLC.

16. Figure 4-3 DW functional components and SDLC.

17. Figure 4-4 Data warehouse project plan: sample outline.

18. The Development Phases In the previous section, we again referred to the overall functional components of a data warehouse as data acquisition, data storage, and information delivery. Therefore, when we formulate the development phases in the life cycle, we have to ensure that the phases include tasks relating to the three components. The phases must also include tasks to define the architecture as composed of the three components and to establish the underlying infrastructure to support the architecture. The design and construction phase for these three components may run somewhat in parallel. Refer to Figure 4-5 and notice the three tracks of the development phases. In the development of every data warehouse, these tracks are present with varying sets of tasks. You may change and adapt the tasks to suit your specific requirements. You may want to emphasize one track more than the others. If data quality is a problem in your company, you need to pay special attention to the related phase. The figure shows the broad division of the project life cycle into the traditional phases:

19. Figure 4-5 Data warehouse development phases.

20. The Project Team A data warehouse project is similar to other software projects in that it is human-intensive. It takes several trained and specially skilled persons to form the project team. Two things can break a project: complexity overload and responsibility ambiguity. In a life cycle approach, the project team minimizes the complexity of the effort by sharing and performing. When the right person on the team with the right type of skills and with the right level of experience does an individual task, this person is really resolving the complexity issue. In a properly constituted project team, each person is given specific responsibilities of a particular role based on his or her skill and experience level. In such a team, there is no confusion or ambiguity about responsibilities.

21. Organizing the Project Team Organizing a project team involves putting the right person in the right job. You would need specialized skills in the areas of project management, requirements analysis, application design, database design, and application testing. But a data warehouse project calls for many other roles. How then do you fill all these varied roles? A good starting point is to list all the project challenges and specialized skills needed. Your list may run like this: planning, defining data requirements, defining types of queries, data modeling, tools selection, physical database design, source data extraction, data validation and quality control, setting up the metadata framework, and so on. Once you have a list of roles, you are ready to assign individual persons to the team roles. In this personnel allocation process, remember that the user representatives must also be considered as members of the project team. Do not fail to recognize the users as part of the team and to assign them to suitable roles.

22. Rolesand Responsibilities • Data warehousing authors and practitioners tend to classify the roles or job titles in various ways. They first come up with broad classifications and then include individual job titles within these classifications. • Here are some of the classifications of the roles: • Classifications: Staffing for initial development, Staffing for testing, Staffing for ongoing maintenance, Staffing for data warehouse management • Broad classifications: IT and End-Users, then sub-classifications within each of the two broad classifications, followed by further sub-classifications • Classifications: Front Office roles, Back Office roles • Classifications: Coaches, Regular lineup, Special teams • Classifications: Management, Development, Support • Classifications: Administration, Data Acquisition, Data Storage, Information • Delivery

23. Despite the absence of a standard set of roles, we would suggest a basic set of team roles: • Executive Sponsor • Project Manager • User Liaison Manager • Lead Architect • Infrastructure Specialist • Business Analyst • Data Modeler • Data Warehouse Administrator • Data Transformation Specialist • Quality Assurance Analyst • Testing Coordinator • End-User Applications Specialist • Development Programmer • Lead Trainer

24. Figure 4-7 lists the usual responsibilities attached to the suggested set of roles. Figure 4-7 Data warehouse project team: roles and responsibilities.

25. Skills and Experience Levels Figure 4-8 describes the skills and experience levels for our sample set of team roles. Figure 4-8 Data warehouse project team: skills and experience levels.

26. User Participation In a typical OLTP application, the users interact with the system through GUI screens. They use the screens for data input and for retrieving information. The users receive any additional information through reports produced by the system at periodic intervals. If the users need special reports, they have to get IT involved to write ad hoc programs that are not part of the regular application. User interaction with a data warehouse is direct and intimate. When the implementation is complete, your users will begin to use the data warehouse directly with no mediation from IT. There is no predictability in the types of queries they will be running, the types of reports they will be requesting, or the types of analysis they will be performing. Your data warehouse project will succeed only: 1- If appropriate members of the user community are accepted as team members with specific roles. 2- Make use of their expertise and knowledge of the business. Figure 4-9 illustrates how and where in the development process users must be made to participate. Review each development phase and clearly decide how and where your users need to participate. This figure relates user participation to stages in the development process.

27. Figure 4-9 Data warehouse development: user participation.

28. Here is a list of a few team roles that users can assume to participate in the development: • Project Sponsor—executive responsible for supporting the project effort all the way • User Department Liaison Representatives—help IT to coordinate meetings and review sessions; ensure active participation by the user departments • Subject Area Experts—provide guidance in the requirements of the users in specific subject areas; clarify semantic meanings of business terms used in the enterprise • Data Review Specialists—review the data models prepared by IT; confirm the data elements and data relationships • Information Delivery Consultants—examine and test information delivery tools; assist in the tool selection • User Support Technicians—act as the first-level, front-line support for the users in • their respective departments

29. Project Management Considerations Effective project management is critical to the success of a data warehouse project. Figure 4-10 Possible scenarios of failure.

30. Warning Signs Figure 4-11 Data warehouse project: warning signs.

31. Success Factors • There are some such indications of success that can be observed within a short time after implementation. The following happenings generally indicate success: • Queries and reports—rapid increase in the number of queries and reports requested by the users directly from the data warehouse • Query types—queries becoming more sophisticated • Active users—steady increase in the number of users • Usage—users spending more and more time in the data warehouse looking for solutions • Turnaround times—marked decrease in the times required for obtaining strategic information

32. CHAPTER SUMMARY • While planning for your data warehouse, key issues to be considered include: setting proper expectations, assessing risks, deciding between top-down or bottom-up approaches, choosing from vendor solutions. • Business requirements, not technology, must drive your project. • A data warehouse project without the full support of the top management and without a strong and enthusiastic executive sponsor is doomed to failure from day one. • Benefits from a data warehouse accrue only after the users put it to full use. Justification through stiff ROI calculations is not always easy. Some data warehouses are justified and the projects started by just reviewing the potential benefits. • A data warehouse project is much different from a typical OLTP system project. The traditional life cycle approach of application development must be changed and adapted for the data warehouse project. • Standards for organization and assignment of team roles are still in the experimental stage in many projects. Modify the roles to match what is important for your project. • Participation of the users is mandatory for success of the data warehouse project. Users can participate in a variety of ways. • Consider the warning signs and success factors; in the final analysis, adopt a practical approach to build a successful data warehouse.