Impossible Data Warehouse Situations

1
Impossible Data Warehouse Situations

• Sid Adelman
• sid_at_sidadelman.com
• [phone]

2
Introduction

• Data Warehouse problems with no solutions?
• Taken from real situations
• DMReview Ask the Experts
• Data Warehouse Project Management Classes
• No names given
• Problems are rarely unique
• Problems are rarely industry specific
• You will experience many of them
• There is hope, there are solutions

3
Your Role

• You can identify the problem before it hurts the
  project
• You can weigh the options
• You can take action to keep the impossible
  situation from causing a disaster

4
Management Situations

• Management Issues
• Changing Requirements Objectives
• Justification Budget
• Organization Staffing
• User Issues
• Team Issues
• Project Planning Scheduling

5
Technical Situations

• Data Warehouse Standards
• Data Warehouse Tools
• Data Quality
• Integration
• Performance

6
Case 1.1 DW has a record of failure

• This is the third attempt at a data warehouse.
  The first two failed, and the general feeling is
  that this one will also fail. What can the
  project manager do to dispel the negative
  conventional wisdom about the data warehouse?

7
Solution 1.1 DW has a record of failure

• Read post-implementation reviews was there a
  post-implementation review?
• Understand the causes of failure
• Address the failures and show how they will not
  cause a failure this time
• Quick delivery
• Deliver in phases
• Show value
• Communicate often with management
• Strong sponsor

8
Case 1.2 IT is Unresponsive

• The IT people on the operational/transaction side
  are not cooperating with those on the data
  warehouse side. The IT people on the operational
  side were unresponsive to requests for resources
  and information about the operational systems,
  and now theyre not responding to requests for
  source data that will go into the
  extract/transform/load process. What should the
  project manager do?

9
Solution 1.2 IT is Unresponsive

• CIO must be convinced of the value of DW
• Form a DW advisory committee with CIO as its
  chairman
• Get the business on your side and have a
  high-level business manager take the problem to
  the CIO
• Have lunch with the IT managers who are not
  cooperating (one at a time) and determine why and
  try to find something in the DW that will benefit
  them.

10
Case 1.3 Management Constantly Changes

• A very dynamic organization is constantly
  changing management. It is unlikely that the
  corporate sponsor will remain for the duration of
  the project. What should the project manager do?

11
Solution 1.3 Management Constantly Changing

• Get backup business sponsors
• Prepare a project agreement and communicate the
  agreements to multiple managers.
• Deliver incremental solutions
• Measure results
• Communicate regularly with many in the business
  community.

12
Case 1.4 The Pilot Must be Perfect

• Management thinks the data warehouse
  implementation must be perfect as a result, the
  scheduled implementation date has already passed.
  The managers expect the quality assurance (QA) on
  the pilot to be at the same level as that of a
  production system. What can the project
  management team do to convince management that a
  pilot system and a production system should have
  different levels of QA?

13
Solution 1.4 The Pilot Must be Perfect

• Set expectations about the role of a pilot and
  how it differs from a production application.
• Promote the role of a pilot to uncover
  deficiencies.
• Uncover concerns that the pilot will be the final
  product

14
Case 1.5 Users Dont Want to Share Data

• The feeling seems to beBy giving another
  department access to our data, we will be giving
  them the ability to criticize us or even take
  over our jobs. Department heads sometimes
  pretend they support the idea of sharing data,
  but in the case of the data in their department,
  they maintain that no other group has the
  expertise to accurately analyze their data. They
  want to be in control of who sees what data and
  when. How can the data warehouse team get
  department heads to share access to their data?

15
Solution 1.5 User Department Doesnt Want to
Share Data

• Satisfy the users that they will still have a
  job.
• Give the user department first rights to see the
  information.
• Attempt to get a corporate policy for data
  sharing who owns the data?
• Sell the idea that data is a corporate asset
  along with other assets.

16
Case 1.6 Senior management does not know what the
DW Team does

• A newly built data warehouse meets almost all the
  measures of success. The response from upper
  management has not been supportive. It seems
  management does not recognize the success of the
  project. What should the data warehouse team do?

17
Solution 1.6 Senior Management Doesnt Know What
the DW Team Does

• Cost/justify the DW
• Identify business benefits, tangible and
  intangible
• Calculate ROI
• Measure user satisfaction
• Ask business manager for a testimonial
• Give senior management some new information
  capability

18
2. Changing Requirements Objectives

• If one does not know to which port one is
  sailing, no wind is favorable.
• Seneca

19
Case 2.1 Operational System is Changing

• The data warehouse project has been in
  development for six months. Right in the middle
  of the project the team discovers that the
  operational source systems are being rewritten,
  with the new systems expected to be up and
  running in eight months. What should the data
  warehouse team do?

20
Solution 2.1 Operational System is Changing

• Focus on what might not change
• Wait on ETL processes
• Use the logical model for impact analysis in the
  changing operational system
• Meet frequently with the operational team
• Explain the problem to management
• Renegotiate the schedule and budget
• Keep the DW team together and motivated
• Deliver a proof of concept (POC)

21
Case 2.2 Source Systems Constantly Change

• The source system is being built at the same time
  as the data warehouse. Although the design of the
  source system is supposed to be frozen, it is
  constantly changing. The source system
  development team doesnt communicate the
  changesthe data warehouse team discovers changes
  during testing when the system fails. What should
  the data warehouse team do?

22
Solution 2.2 Source Systems Constantly Change

• Document the problems associated with the lack of
  communication
• Make management, the business sponsor and IT,
  aware of the problem and tell them how the
  problem should be fixed
• Develop relationship with those responsible for
  the source systems
• Provide incentives for better communication

23
Case 2.3 DW Vision is Blurred

• When the data warehouse project began, the
  organization searched out the best practices in
  the industry and developed a strong set of data
  warehouse objectives. However, over the months
  and years, decisions were made and actions were
  taken that went counter to the initial
  objectives. Most of these decisions and actions
  were considered practical and sometimes
  recognized as temporary, with the goal to
  eventually return to the initial objectives. What
  should this organization do?

24
Solution 2.3 DW Vision is Blurred

• Revisit the original objectives and best
  practices, validate and attempt to reinstate them
• Document how the standards and best practices
  were overridden and why
• Recruit a high-level IT person (perhaps the CTO)
  to sponsor the best practices.
• Establish an advisory group to oversee DW
  practices and procedures
• Review the results of the DW implementations and
  identify how the lack of best practices hurt
• Develop a plan to replace temporary fixes

25
Case 2.4 Objectives are Misunderstood

• The objectives for the data warehouse were never
  properly identified. The project is well under
  way but there is no method for judging whether
  the project will be successful. What should the
  organization do?

26
Solution 2.4 Objectives are Misunderstood

• Steering committee should establish realistic and
  measurable objectives that support the objectives
  of the organization
• Document the objectives and provide these to all
  the DW Team members and to stakeholders
• Steering committee should establish measures of
  success
• As applications are implemented, determine if
  objectives were met and why

27
Case 2.5 Prototype Becomes Production

• A company developed a prototype so that the users
  and management could get a sense of what they
  would be getting. The prototype was never
  intended to become a production system, so the
  team gave little effort to cleaning the data,
  producing a workable database design, testing the
  prototype, or performing all that is necessary to
  deliver a high-quality product. Management said,
  Great, lets give it to all the users now. How
  should the developers convince management that a
  prototype is not ready for production?

28
Solution 2.5 Prototype Becomes Production

• Educate the business on the role and limitations
  of a prototype before the prototype is built
• Data quality problems
• Doesnt support user requirements
• Poor performance
• Incomplete data
• Lack of documentation
• Incomplete testing
• Poor database designs
• Lack of scalability
• Inability to support service level agreements
  (SLAs)
• Manage expectations

29
Case 2.6 Management Does Not Recognize the
Success of the DW Project

• A data warehouse manager has now implemented
  three data marts but has been unable to convince
  management of the success of these efforts. What
  should the data warehouse manager do to show how
  successful these data marts are?

30
Solution 2.6 Management Does Not Recognize
Success of the DW Project

• Be sure that the DW is for the business, not just
  IT
• Determine how managers are measured
• Provide metrics from the DW based on manager
  measurements
• Provide monthly metrics on usage and value
• Compare results to baselines of what it was like
  before the DW
• Measure tangible and intangible benefits
• Ask for testimonials from the business sponsor

31
3. Justification and Budget

• Sometimes the accounting people act as if they
  think the organization exists so they can keep
  books on it.
• Karl Albrecht and Ron Zemke

32
Case 3.1 User Productivity Justification not
Allowed

• An organization will only fund and support a data
  warehouse if it can be justified by increases in
  revenue or decreases in cost. Management will not
  sign off on productivity improvement cost savings
  since the company has a no layoff policy and
  any personnel will have to be retained even with
  no work to do. How do you justify a project
  along with the tools and other costs associated
  with it?

33
Solution 3.1 User Productivity Justification not
Allowed

• Consider benefits of not having to hire more
  staff when people quit
• Measure user benefits other than productivity
  (speed, quality, understandability of results)
• Find other tangible benefits

34
Case 3.2 Identify Infrastructure Benefits

• A committee was formed to make recommendations
  for the infrastructure that will be needed for
  the data warehouse. They were asked to justify
  any expenditures for data warehouse tools (ETL,
  BI, etc.). All the benefits they were able to
  identify have been intangible. What should they
  do?

35
Solution 3.2 Identify Infrastructure Benefits

• Without the infrastructure, there is no DW
• Start small, show benefits, grow
• Work with existing products
• Evaluate tool buy versus build cost

36
Case 3.4 How Can Costs be Fairly Allocated?

• The sponsor of the first data warehouse project
  absorbed the entire cost of the data warehouse
  infrastructure. Now there are additional
  divisions in the company that will be using the
  data warehouse these divisions do not want to
  pay for any part of the infrastructure. How
  should the costs be allocated and what should the
  sponsor do?

37
Solution 3.4 How Can Costs be Fairly Allocated?

• Do you have charge-backs? Charge-backs can reduce
  usage
• Separate budget for infrastructure allocate
  across enterprise
• Data mart allocated to owning department
• Volume of data
• Number of users
• Activity of users

38
Case 3.5 Historical Data Must be Justified

• Users are asking for ten years of historical
  data. Not only will this require much more
  hardware, but we now have to reconcile all code
  changes for the last ten years. How can all this
  historical data be justified?

39
Solution 3.5 Historical Data Must be Justified

• Historical data often needs work to clean,
  integrate, and reconcile with current data
• Is detail data needed?
• What is the cost justification of historical
  data?
• Ask the people who want the historical data to
  pay for it. They may choose not to
• Monitor historical usage and provide feedback
• Historical data may be mandated by law or it may
  be a regulatory requirement
• Consider lower cost storage for older data

40
4. Organization and Staffing

• If youre riding ahead of the herd, take a look
  every now and then to make sure its still
  there.
• Will Rogers

41
Case 4.1 To Whom Should the DW Team Report?

• A data warehouse manager has responsibility for
  the three data warehouse projects that are under
  development and the two that have already been
  rolled out to the users. She is fighting to keep
  the function as a separate entity reporting to
  the CIO. However, a powerful application
  development manager feels that the data warehouse
  should report to him and he is making a case for
  him to take control of the DW. Where do you feel
  the data warehouse manager should report and what
  recommendations would you give the data warehouse
  manager?

42
Solution 4.1 To Whom Should the DW Team Report?

• As high as possible in IT, the CIO
• Depends on CIO perspective but should not report
  to Application Development
• Should the Team report to the Business and where
  in the Business hierarchy?
• Chief Operating Officer (COO)
• Chief Financial Officer (CFO)
• Chief Knowledge Officer (CKO)

43
Case 4.2 Organization Uses Matrix Management

• A company has created multiple core competency
  groups and loans out these skills as needed. One
  such group is the database administration group.
  The data warehouse project manager is not allowed
  to hire a DBA for the project but must rely on
  the goodwill of the DBA Manager to provide a
  skilled DBA when needed. This has not worked in
  the past as multiple DBAs were assigned during
  the course of development and the project lost
  continuity. In addition, the DBAs were not always
  available when they were needed. The data
  warehouse project manager is about to begin
  another project. What should she do about this
  matrix organization?

44
Solution 4.2 Organization Uses Matrix Management

• Core team is required with full time commitments
• Matrix management does not work for the DW
• Extended schedules
• Productivity loss
• Loss of continuity
• Learning curve problems

45
Case 4.3 Project Has no Consistent Business
Sponsor

• The organization has a policy of constantly
  rotating managers. The business sponsor who
  started with the project has been reassigned and
  now has no interest in the project. The new
  business sponsor is not familiar with the data
  warehouse or the project. She also has different
  views about what is important. This will probably
  lead to changes in the scope of the project. What
  should the project manager do?

46
Solution 4.3 Project has no Consistent Business
Sponsor

• Project agreement is critical
• Project justification is critical
• Steering committee should provide some
  consistency
• Sell the project throughout the sponsoring
  division
• Deliver in smaller phases and measure the
  benefits

47
Case 4.4 Should a LOB Own its Own Data Mart?

• A sales unit needs the capability of a data mart.
  They have asked IT to build it for them but the
  request is low on ITs priority list. The Sales
  line-of-business has neither the expertise nor
  the inclination to build a data mart on its own,
  but it does have the budget. What should the
  Sales organization do?

48
Solution 4.4 Should a Line of Business (LOB) Own
its Own Data Mart?

• Depends on its power and budget they may not
  ask your permission
• IT should be involved in sourcing the data,
  enterprise standards, metadata, tools, training,
  support, and looking at sharing, integration, and
  MDM opportunities
• If the LOB engages a consultant, IT should
  monitor plans and activities

49
Case 4.5 Project has no Dedicated Staff

• A data warehouse project manager has been tasked
  to manage, develop and support an enterprise data
  warehouse that crosses multiple divisions. This
  manager has almost no dedicated staff, but must
  rely on pulling business and IT people from each
  line of business as they are needed on the
  project. The people he needs are often not
  available especially when they are needed.
  Important meetings are unproductive when key
  personnel are not in attendance. This has caused
  major delays and wasted time for those who did
  attend the meetings. Sign-offs have not taken
  place on time and many decisions have had to be
  delayed. What should this data warehouse project
  manager do?

50
Solution 4.5 Project has no Dedicated Staff

• Is the project worth doing?
• Is there any justification?
• Has the project been sold?
• Is there any support in the business or in IT?
• If the answer is No abort the project

51
Case 4.6 Project Manager has a Bad Reputation

• The data warehouse project manager reports to the
  data warehouse manager. The project manager comes
  from the IT side of the organization and is not
  well liked or respected by the business. The
  business people do not answer his phone calls or
  respond to his email messages. What should the
  data warehouse manager do?

52
Solution 4.6 Project Manager has a Bad Reputation

• Project Manager with a bad reputation will
• Will not get cooperation
• Will not be able to recruit and retain good
  people
• Will not be respected by his or her team
• Will not be trusted to deliver
• Replace the project manager
• Project manager should be highly respected by
  business and IT

53
Case 4.8 Organization is Not Ready for a DW

• The organization is not ready for the data
  warehouse. This lack of readiness extends from
  technical skills, availability of staff, lack of
  motivation, political infighting, assassins, a
  CIO ready to retire who doesnt want to take
  any risks, the business that neither wants the
  data warehouse nor has the money or the
  inclination to participate in any data warehouse
  endeavor. What should the newly appointed data
  warehouse manager do?

54
Solution 4.8 Organization is not Ready for a DW

• Find a powerful business sponsor
• Implement small reporting improvements
• Improve data quality
• Measure and report on the small successes
• DW Manager should probably find another job

55
5. User Issues

• With the customer as the reference point,
  priorities become easier to set.
• Mary Walton

56
Case 5.1 All the Users Want it All Now

• An organization has a corporate culture that
  fosters siloized business units that have IT
  implementations that are not integrated with each
  other. However the multiple business unit
  sponsors want their data in the data warehouse
  and they all want it now. Its obvious that not
  all the sponsors can be satisfied at once. What
  should be done so that none of the business unit
  sponsors is angered or develops his or her own
  data warehouse?

57
Solution 5.1 All the Users Want it All Now

• Establish an advisory board/steering committee to
  prioritize projects
• Use cost justification to aid in prioritization
• Build a project time line showing when each
  users project should begin and complete
• Sell the idea of an enterprise DW and discourage
  departments from building their own showing the
  costs and problems of doing so

58
Case 5.2 Business Does Not Support the Project

• A consulting organization is hired by IT to build
  a data warehouse. The business is not supportive
  of the project but IT tells the consultant to
  keep working even though the business side is
  making plans to terminate them. What should the
  consulting company do?

59
Solution 5.2 Business Does Not Support the Project

• Determine why the business is not supportive
• Determine if there is a business case for the DW
• Try to sell the business on the value of the DW
• If none of this works, the project should be
  killed

60
Case 5.4 Users Have High Data Quality Expectations

• Somehow the business users have been led to
  believe that the data they will be seeing will be
  complete, accurate and very timely. They came to
  that conclusion since no one at the time
  indicated anything to the contrary. What should
  be done to reset their expectations to the
  reality of what they will be getting?

61
Solution 5.4 Users Have High Data Quality
Expectations

• Educate users on the various dimensions of data
  quality
• Understand users requirements for data quality
  not just wishes
• Set user expectations about data quality early
  and often
• Profile the data and report findings to the users
• Capture quality metrics in the ETL process
• Keep data quality indicators in metadata
• Involve the users in your data quality effort

62
Case 5.5 Users Dont Know What They Want

• An organization with unforgiving users is
  attempting a data warehouse. Its become very
  difficult to get the users to articulate what
  they want or even why they would want a data
  warehouse. What should the data warehouse team do?

63
Solution 5.5 Users Dont Know What They Want

• Does the DW Team have a solution looking for a
  problem?
• Develop a proof of concept (POC) or a prototype
  and use it to help the users understand what they
  can receive
• Provide the users with stories of the DW in their
  industry
• Identify opportunities and present them to the
  users
• Be sure you are using business terminology in
  your discussions with the users

64
6. Team Issues

• The greater the loyalty of the members of a
  group toward the group, the greater is the
  motivation among the members to achieve the goals
  of the group, and the greater the probability
  that the group will achieve its goals.
• Rensis Likert

65
Previous Experience

• The previous experience each member has had with
  the other team members is probably more important
  than any other single factor in predicting how
  well the people on the team will interact.

66
Case 6.2 Management has Assigned a Dysfunctional
Team Member to the Project

• A project manager has been given a team that is
  unskilled, unmotivated and generally the worst of
  what other managers did not want on their team.
  The project manager has been asked to do the best
  she can. What should she do?

67
Solution 6.2 Management has Assigned a
Dysfunctional Team Member to the Project

• Management needs to understand that with this
  unskilled and unmotivated team, the project will
  fail
• Pair the team members with experienced
  consultants
• Bonuses should motivate the team but if they
  dont, remove those who are unmotivated
• Provide time and training to learn the necessary
  skills

68
Case 6.7 Consultants are in Charge

• The new CIO came from one of the big consulting
  organizations and brought in three of his
  lieutenants with him. These lieutenants now hold
  the important positions in the IT organization.
  The data warehouse reports to one of these
  managers. This manager has contracted with his
  old organization for consulting help for the new
  data warehouse project. The project manager has
  been asked to work with these consultants who
  seem to have great power and influence over the
  data warehouse project. In fact, the views and
  positions of the project manager have been
  seriously undermined. The charges are large and
  the project looks like it will be way over
  budget. What should the project manager do?

69
Solution 6.7 The Consultants are in Charge

• Project manager should insist on having authority
  for the project
• Find an ally on the business side
• Document all decisions made or overturned by the
  consultants
• Failing to get authority, the project manager
  should quit the project

70
Case 6.8 The Consultants Have Fled

• A data warehouse was built three years ago. None
  of the contractors who developed the system have
  remained and the documentation is poor and out of
  date. The data is dirty and there are no controls
  for data integrity. The users are unhappy with
  the existing data warehouse. A new manager has
  been given responsibility for all the data
  warehouse activity in the organization. What
  should this manager do?

71
Solution 6.8 The Consultants have Fled

• Asses the existing DW. Can it be salvaged? If it
  can, create a plan, estimate budget, resources,
  cost justification, and schedule. If it cant,
  give management the bad news and develop a new DW
  and give it a new name.
• Are original requirements being satisfied?
• What pieces of the design are appropriate and
  effective?
• Whats the status of the documentation?
• Whats the state of the quality of the data?
• What are the skills and experience of the DW
  Team?

72
Case 6.9 Knowledge Transfer is Not Happening

• An organization planned to bring in a data
  warehouse consultant to help with the first
  implementation. They then planned to continue
  developing using their own staff who should have
  been trained by the consultant. The consultant
  assured the client that knowledge transfer was
  part of their work but as the schedule became
  tight, the consultant did not have the time to
  transfer their knowledge to the organizations
  employees. The employees were only performing
  simple tasks and they learned very little from
  the first implementation. How can an organization
  assure itself that knowledge transfer does take
  place?

73
Solution 6.9 Knowledge Transfer is not Happening

• The contract with the consultant should have
  included measurable knowledge transfer
• Payment to the consultant should include metrics
  for the deliverables of knowledge transfer
• The project plan should have included knowledge
  transfer activities including seminars, classes,
  and mentoring sessions
• Pair up consultants with employees
• Phase 1 consultant does most of the major work,
  employee watches and learns
• Phase 2 consultant and employee trade major
  tasks
• Phase 3 employee performs most of the major
  work, consultant reviews and advises

74
Case 6.10 How to Best Use Consultants

• A manager has been given the assignment to design
  and build a data warehouse infrastructure
  complete with standards, methodology and tools.
  He was given the budget and the mandate to bring
  in new tools along with consultants and
  contractors as needed. He does not have an
  unlimited budget. How should he bring in
  consultants, for which jobs and for how long? How
  should he most effectively use consultants and
  contractors?

75
Solution 6.10 How to Best Use Consultants

• Differentiate between contractors and consultants
• When specific skills and expertise are lacking
  use contractors
• When high-level advice is required use
  consultant
• To assess the organizations plans, progress,
  organization, use of tools, - use consultant
• When the organization is unwilling to fill full
  time positions use contractor
• As a mentor to the DW Team use contractor and
  consultant
• One-time-only tasks use contractor
• To convince management use consultant

76
Case 6.11 Management Wants to Outsource

• A company is making some major changes. They will
  be outsourcing their operational systems to an
  application service provider (ASP) and they are
  considering outsourcing some or all of their data
  warehouse activities. The new focus is on the
  customer and they are planning significant
  customer relationship management (CRM)
  capability. They have some minor data warehouse
  capability today, but with this major change,
  should they even use any of the existing DW? How
  can the organization be sure the outsourcing
  organization will deliver the functions and the
  capabilities needed? What recommendations do you
  have for how they should proceed?

77
Solution 6.11 Management Wants to Outsource

• Understand the reasons for outsourcing
• Understand the costs, risks, delays, and effort
  to outsource
• Understand how much intellectual capital is lost
  with outsourcing
• Determine which activities and roles could be
  outsourced and which should remain in house
• Understand the costs and problems if the work has
  to be brought back in (insourced)

78
7. Project Planning and Scheduling

• The reason we dont have the time to fix it
  today is that we didnt take the time to do it
  right yesterday.
• H. James Harrington

79
Case 7.2 IT Management Sets Unrealistic Deadlines

• IT has missed deadline after deadline and has a
  reputation for never bringing in a project on
  time. This time they really dont want to miss.
  IT management has already made commitments to
  their bosses for a schedule that is unrealistic
  but they are counting on the data warehouse
  project manager to come through and deliver on
  time. Management has made it clear to the project
  manager that her reputation and career within the
  company depend on meeting the schedule. What
  should she do?

80
Solution 7.2 IT Management Sets Unrealistic
Deadlines

• Make management aware of what is realistic and
  what is not and give management a best estimate
  of when the project will be completed
• Ask management if they want the bad new now or
  later when the schedule slips
• Negotiate to phase the deliverables implementing
  a few that meet the schedule and deferring others
  to a later phase
• Share the project plan (at a high level) with
  management
• Do not sacrifice quality
• Adding more people will probably slip the
  schedule even more
• Do not ask your team to work 12 hour days and
  work weekends

81
Case 7.3 Sponsor Changes Scopes but Doesnt Want
to Change Schedule

• The project manager had a project agreement,
  developed a project plan and allowed an
  additional 20 time, effort and budget for
  unanticipated contingencies. The sponsor and a
  few others in the sponsors department had been
  asking for some minor additional function that
  the project manager accepted. However, just
  recently, the sponsor made major requests for new
  data. Additionally, he did not want the schedule
  to change. What should the project manager do?

82
Solution 7.3 Sponsor Changes Scope but does not
want to Change Schedule

• Go back to your project agreement
• Use your organizations change control processes
• How important is the original schedule?
• Do not agree to the changes without other
  concessions
• Do not say No
• Negotiate using the project agreement and your
  project plan
• Defer some functions/deliverables to future phases

83
Case 7.4 Users Want First DW Delivered to Include
Everything

• The users are afraid that if they dont ask for
  everything in the first release, they may never
  see it at all so they are asking for all the
  functions they will eventually need and much
  more. This nullifies one of the benefits of a
  data warehouse implementation the ability to
  phase deliveries. What approach should the data
  warehouse project manager take to convince
  management that trying to put it all in the
  initial release is a sure formula for either
  failure or for a very long delivery schedule?

84
Solution 7.4 Users Want First DW Delivered to
Include Everything

• Sell the ability of the DW to nicely phase
  projects
• Explain the value of learning from each phase
• Highlight the risk with a very large project
• Give a choice of everything in four years or a
  set of phased deliverables they always select
  the phased approach
• Show a plan that does include everything but
  delivers in phases three to five months is a
  reasonable interlude for phases

85
Case 7.5 Project Manager Severely Underestimates
the Schedule

• A project manager has decades of experience and
  is very competent in a number of computer
  languages and operating systems. He has extensive
  experience in his industry and knows who to call
  and where to find whatever he is looking for. On
  top of this he is an eternal optimist, believing
  everything will progress perfectly with no delays
  or false starts. He bases his teams work
  estimates on how long it would take him to
  perform the task. What should his manager do?

86
Solution 7.5 Project Manager Severely
Underestimates the Schedule

• The estimates should be calculated by those who
  will be doing the work with reviews
• Ask the team members for three estimates for each
  of their tasks, the best, the worst, and their
  best guess. Take the best guess and add 20 - 30
• Ask the team members for predecessors and
  dependencies
• Ask the team members for risks and assumptions in
  their estimates and factor those in

87
Technical Situations

• Data Warehouse Tools
• Data Quality
• Integration
• Data Warehouse Architecture
• Performance

88
9. Tools and Vendors

• Praise from a salesman, in my humble opinion, is
  one of lifes less convincing complements.
• Peter Mayle

89
Case 9.1 Best Practices for Writing a Request for
Proposal

• A non-profit organization is considering a data
  warehouse to keep track of their membership and
  solicitation activity. Any project this large
  requires a request for proposal/price (RFP). Of
  course they do not want the RFP process to
  significantly slow them down. What
  recommendations would you give them?

90
Solution 9.1 Best Practices for Writing a Request
for Proposal

• As simple and short as possible
• Include only characteristics used for comparison
• Include only mandatory and highly desirable
  requirements
• Include a glossary
• Give vendors your criteria for judging
• Give vendors rules for responding

91
Case 9.2 Users Dont Like the Query and Reporting
Tool

• A company was sorry to learn that only 5 of the
  users who went through training used the data
  warehouse regularly. On further investigation,
  they learned that those who did not use the data
  warehouse were uncomfortable with the
  query/reporting tool and reluctant to use it.
  What should be done?

92
Solution 9.2 Users Dont Like the Query and
Reporting Tool

• Investigate why they dont like the tool
• If the tool has a bad reputation, get a new one
• The tool and facilities of the tool should match
  the capabilities, interests, and work activities
  of the users
• Training should be tailored to users
  capabilities and interests
• Give the casual users access to a query and
  report library and only train them on accessing
  the library
• Provide mentors during the training workshops
• Teach the users about the data
• Evaluate the effectiveness of the training
• Users should only have access to the tool when
  they have completed the training
• User support should be sensitive to problems with
  the tool
• Measure user satisfaction

93
Case 9.3 IT has Already Chosen the Tool

• Even though a tool selection committee was formed
  and supposedly has the authority to chose data
  warehouse tools, some powerful people in the IT
  department already know what they want and will
  resist any recommendations that do not correspond
  to their choice. Some members of the tool
  selection committee are concerned that the chosen
  tools wont perform. What should the selection
  committee do?

94
Solution 9.3 IT Has Already Chosen the Tool

• Determine if the selection committee has or does
  not have the authority to make the decisions. If
  they dont have the authority, the committee
  should be disbanded.
• Determine if the tool will perform
• Determine if the tool has the functions and
  capabilities needed
• Document the business requirements and match each
  product against the requirements along with the
  metrics and weighting for comparisons
• Document the evaluations including the reasons
  for applying the grades

95
Case 9.4 Will the Tools Perform Well?

• A company expects to have a data warehouse with
  over 50 million records within two years. They
  would like to use an extract/transform/load (ETL)
  tool but they are concerned about the ability of
  the tool to perform. They know these tools are
  expensive and they do not want to get started
  with a product that will have to be abandoned as
  the volumes increase. How can they be sure that
  the tools they are considering will perform up to
  their expectations?

96
Solution 9.4 Will the Tools Perform Well?

• Pay no attention to theoretical volumes
• Talk to references with volumes as large as those
  you are expecting and with similar levels of ETL
  complexity and with as many queries with the
  same or greater level of complexity as you are
  expecting
• The references should be on the same platform
• Ask the references if they had to take
  extraordinary actions to make the tool perform or
  if special skills were needed
• Ask the vendors to run benchmarks

97
Case 9.5 The Vendor is Trying to Sell His
Complete Suite of Products

• A data warehouse vendor has a suite of data
  warehouse products, some of which are excellent
  but others are far from best-of-breed. The vendor
  is recommending the entire suite making it clear
  that the only way to get top-level support is to
  buy the complete package. What should the
  organizations response be?

98
Solution 9.5 The Vendor is Trying to Sell His
Complete Suite

• Do not be influenced by threats of reduced
  support if the entire suite is not purchased.
  Push back. Tell the vendor that if he cant
  guarantee good support for the products you do
  purchase, his products will not be chosen
• There are benefits to getting everything from the
  same vendor
• If portions of the suite are substandard,
  purchase a better tool and integrate it with
  those of the vendors

99
Case 9.7 Vendors Acquiring Company Provides Poor
Support

• An organization was happy with the query tool it
  was using but the vendor was in financial trouble
  and sold out. The acquiring company fired most of
  the developers and the support staff. Needless to
  say, support is terrible and based on the
  reputation of the acquiring vendor, support is
  not expected to improve. What should the company
  do?

100
Solution 9.7 Vendors Acquiring Company Provides
Poor Support

• Try to determine if support will be improved and
  how long that will take
• All new development should be with a tool that
  does have good support
• Consider switching to a vendor with good support
• Research the possibility that a third party might
  provide support

101
11. Data Quality

• There is no manual that deals with the real
  business of motorcycle maintenance, the most
  important aspect of all. Caring about what youre
  doing is considered unimportant or taken for
  granted.
• Robert Pirsig

102
Case 11.2 Redundant Data Needs to be Eliminated

• A telecommunication company has a data warehouse
  containing 14 terabytes of data. It has been
  estimated that more than ten terabytes is
  probably redundant. The company has no naming
  conventions and only 20 of the data have
  associated meta data. How can they identify and
  eliminate unneeded redundant data?

103
Solution 11.2 Redundant Data Needs to be
Eliminated

• Research the sources of the redundant data how
  is it getting here? Determine which tables are
  based on the same sources
• Are there business reasons for the redundancy?
• Research identical data elements with different
  names
• Metadata should play a role in this process
• What is the cost justification for eliminating
  the redundant data?
• Develop a standard naming convention for the
  business terms and the physical data names
• Identify tables with redundant data and track
  their sources

104
Case 11.3 Management Underestimated the Amount of
Dirty Data

• Management has never recognized just how dirty
  the data is in the operational systems. They are
  unaware of the degree of redundancy, how
  incomplete many of the records are, the use of
  inappropriate defaults, the data that does not
  conform to the valid values, the lack of
  referential integrity and the data that is just
  inaccurate. As the data warehouse has been
  piloted, feedback from the project team and from
  the users has made it clear that the quality of
  the data is not acceptable to allow the project
  to proceed. Cleaning up the data will take
  significant time, and that time has not been
  allocated in the project schedule. What should
  the project manager do?

105
Solution 11.3 Management Underestimated the
Amount of Dirty Data

• Provide a monthly report card to IT management
  and to the data owners based on profiling the
  data
• Give a presentation on data quality that
  incorporates the result of the profiling and
  estimates of the cost of dirty data and what
  steps should be taken to improve data quality
• Include the time and effort to clean up the data
  in the project plan
• Do not deliver dirty data to the users
• Determine which data elements must be clean and
  which are not as important

106
Case 11.4 Managements does not Recognize the
Value of Data Quality

• No one is sure just how dirty the data is but
  its pretty clear that the level of quality will
  not be acceptable for the data warehouse. It is
  also clear that the cleansing process will be
  costly and will take dedicated staff. Management
  is not even aware of the data quality problems
  the data seems to be working just fine for the
  operational systems. Furthermore, management is
  not inclined to spend money or resources fixing
  the very dirty data. What should be done to
  convince management of the need to clean the data?

107
Solution 11.4 Management Does Not Recognize the
Value of Data Quality

• Capture internal stories of problems resulting
  from poor quality data and try to quantify the
  costs or problems associated with bad data
• Research stories from other companies, especially
  those in your industry
• Data quality vendors have metrics on data quality
  justification
• Identify potential regulatory fines, problems,
  and embarrassments from poor data quality
• Identify potential bad business decisions that
  could result

108
12. Integration

• Few things are less productive than duplication
  of effort and the resulting need for
  reconciliation of inconsistent data.
• - Repository Data Model Strategy Paper

109
Case 12.4 Reports From the DW and the Operational
System Dont Match

• The data warehouse manager is responsible for all
  the data warehouse initiatives in the company. He
  recognizes that he will have credibility problems
  if the reports and queries that come from the
  data warehouse do not correspond to those of the
  operational systems. He also knows that much of
  the operational data is dirty and must be
  cleansed to satisfy the needs of the analysts who
  will be the primary users of the data warehouse.
  He knows that if he transforms and cleans up the
  data as he brings it into the data warehouse, the
  report results will not correspond to those of
  the operational systems and the validity of the
  data warehouse will be questioned. What should he
  do?

110
Solution 12.4 Reports From the DW and the
Operational System Dont Match

• Educate the users on the reasons the DW is
  different than the operational reports.
• Different timing
• Different controls
• Different edits
• Data quality improvements
• Demonstrate the DW improvements
• Convince users to rely on the DW reports
• Convince users that matching to the penny is
  not a strategic requirement
• Involve the users early on and in testing so they
  will anticipate and understand the differences

111
Case 12.5 Should the DW Team Fix a Problem
Operational System?

• It has become clear that the operational system
  that feeds the data warehouse is inadequate and
  management believes that a part of the job of the
  data warehouse project is to fix the operational
  system. Should this be attempted?

112
Solution 12.5 Should the DW Team Fix a Problem
Operational System?

• The DW Team cannot and should not have
  responsibility for fixing a broken operational
  system
• The DW Team should provide feedback and
  information on what needs fixing to those
  responsible for the operational system including
  the business owners
• Continued contact with the operational team
  should minimize problems when the operational
  system is fixed
• The DW Team must identify operational problems
  that would cause the DW project to fail
• However, the rule always is that it is cheaper
  and easier to fix the data as close to its source
  as possible

113
Case 13.3 Management Wants to Develop a DW
Simultaneously with a New Operational System

• A manufacturing company is in the midst of
  implementing a new operational system with the
  normal problems of any new system. Management is
  pushing to install a data warehouse with this new
  operational systems data as the source. Is there
  anything they can do now or should they wait
  until all the bugs are out before they start
  their work?

114
Solution 13.3 Management Wants to Develop a DW
Simultaneously with a New Operational System

• Alert management to the extra time, effort and
  money that will be required
• Start with user information requirements
• Traditional project management activities can
  begin before the operational system is in place
• Hardware and DW software can be installed and
  tested
• IT personnel can be trained and given DW tasks to
  perform
• Data models can be developed recognizing that
  changes will be required
• Stable data sources can be profiled for data
  quality and ETL processes against these sources
  can be developed and tested
• Avoid physical DW and ETL design until the
  operational system is stabilized
• DW Team can build prototypes and show them to the
  users
• But make the DW schedule dependent on the
  operational system completion

115
Case 13.4 The DW Gets Assigned the Role of a
Reporting System

• The team for a large bank knew early in the
  project that they could deliver a web based
  reporting system on time if it was done in
  moderation. Additional reports were to be added
  iteratively after the initial implementation as
  enhancements. This strategy was completely
  abandoned and the reporting requirements started
  to increase as the more realistic team members
  were no longer able to make decisions. The
  Advisory Group that made the decisions to include
  the total reporting capability failed to
  understand the meaning of a data warehouse - to
  them it means only a reporting system. What can
  be done to set the Advisory Group on the right
  path?

116
Solution 13.4 The DW Gets Assigned the Role of a
Reporting System

• Educate management on the opportunities that go
  far beyond reporting
• Demonstrate queries, analysis, data mining,
  predictive analytics
• Give the users new ways of receiving their
  reports (web, PDAs, email)

117
14. Performance

• The ultimate test of management is performance.
• Peter Drucker

118
Case 14.1 Software Does Not Perform Properly

• An organization made a major financial, training
  and implementation commitment to a software
  product. It now appears that the software will
  not perform. What should the company do?

119
Solution 14.1 Software Does Not Perform Properly

• Software conversions are expensive are they
  necessary?
• Are you properly using the software?
• Does the contract with the vendor have any
  performance guarantees?
• Does the vendor have plans for improvement?
• Be sure any new product will perform how can
  you be sure?

120
Case14.2 DW Grows Faster Than the Source Data

• The data warehouse is growing much faster than
  the source data that feeds it. The costs for the
  hardware are already over budget and there
  appears to be no end in sight. Management is
  concerned and is asking some embarrassing
  questions. Should the data warehouse grow
  disproportionately to the source data? If not,
  what can be done to stem the growth?

121
Solution 14.2 DW Grows Faster Than the Source Data

• Are new applications cost justified?
• Are new requests for more data cost justified?
• Improve requirements gathering process
• Measure usage so you know what data is being
  accessed
• Evaluate the need to maintain detail data versus
  summarized data
• Understand the need for how frequently the data
  is stored in the DW
• Develop an archive strategy to place rarely used
  and older data on less expensive medium
• Plan to minimize redundant data making use of
  metadata and the means to share data
• Consider a chargeback scheme where the user pays
  for the data they want stored
• Perform a design review

122
Summary

• So many problems
• There are solutions to most of those problems and
  impossible situations
• Dont kid yourself
• Identify the problems early
• Take the steps necessary to be successful