Impossible Data Warehouse Situations

1
Impossible Data Warehouse Situations

• Portland DAMA
• Sid Adelman
• 818 783 9634
• sidadelman_at_aol.com

2
Taken From Impossible Data Warehouse Situations
with Solutions from the Experts

• Sid Adelman
• Joyce Bischoff
• Jill Dyche
• Doug Hackney
• Chuck Kelley
• Sean Ivoghli
• Dave Marco
• Larissa Moss
• Clay Rehm

3
Impossible Situations

• Each organization, must wrestle with many of the
  very difficult situations that have confounded
  other organizations.
• The same impossible situations continue to raise
  their ugly heads,
• You are not alone and your problems are not
  unique.
• Give hope to the perplexed who see no obvious
  solution to their problems.
•  

4
Impossible Situations

• Some of the situations should resonate with those
  of you planning to enhance your data warehouse,
  to add new data, additional users, and new
  applications.
• Impossible situation may not yet emerged, but you
  definitely see it just around the bend.
• You should be able to avoid the situation rather
  than having it develop and then needing to fix
  it.  

5
Categories of Impossible Situations

• Management Issues
• Changing requirements/objectives
• Justification/budget
• Organization and Staffing
• User issues
• Team issues
• Project planning/schedules

6
Categories of Impossible Situations

• Data warehouse standards
• Tools/vendors
• Security
• Data Quality
• Integration
• Data warehouse architecture
• Performance

7
Data Warehouse has a record of failure

• Telling everyone how good its going to be will
  be a wasted effort.
• The only thing that will convince this
  organization is a successful implementation.
• The project manager should deliver something of
  value and deliver it quickly.
• The projects business sponsor should be asked to
  tout the success of the project

8
Management turnover expected

• Get a backup sponsor
• Solicit a few high ranking sponsors who will be
  able to support the project even if the initial
  sponsor leaves.
• politically powerful
• substantial interest in the success of the
  project
• are accepting of problems
• sponsors with a long-term and short-term
  perspective of what needs to be accomplished.

9
User Departments Unwilling to Share Data

• May not be able to get department managers to
  share data.
• Fear of criticism or that they may be
  micromanaged.
• Want opportunity to put spin on results
• Few organizations have real sharing.
• The CEO makes clear that everyone is to share
  data and is not allowed hold anything back.

10
The Operational System is Changing

• Put the development of the data warehouse on hold
  until the operational source is reasonably
  stable.
• Look for commonality between the new and the old
  operational systems.
• User requirements may be somewhat different.
• The logical modeling will probably require some
  changes
• Many of the reports and queries will be the same.
  
• No need to change AA or ETL software.

11
DW Objectives Misunderstood

• Develop measures of success, for example
• The data warehouse is useful
• Performance is acceptable
• The data warehouse is cost justified.
• Management is able to get more timely answers to
  their questions.
• The data is significantly cleaner.

12
How to Demonstrate Success

• Start to measure the things that are of interest
  to management.
• Measure the number of queries/day run by each
  analyst.
• Measure timeliness (freshness).
• Measure integrity and completeness
• Report each measurement to management monthly
  highlighting the differences pre and post data
  warehouse implementation.

13
User Productivity Justification not Allowed

• Find other areas of benefit like better decisions
  for
• marketing,
• distribution,
• inventory control,
• quality control,
• supply chain opportunities
• more effective customer control
• Revenue increase
• Cost decrease

14
Fair Cost Allocation

• Initial sponsor is entitled to recoup some costs
  and allocate part of these costs to the other
  divisions.
• A fair distribution of the cost could incorporate
  costs based on
• volume of data,
• number of users
• activity of the users

15
Matrix Management?

• Sell the idea of a core team,.
• Problems of the lack of continuity when different
  people were assigned
• resulting loss of productivity,
• learning curve
• extensions in the schedules
• increased costs.
• Best practices - teams that have direct reports
  of DBAs, data administrators, ETL jockeys,
  business analysts and query tool administrators.

16
Low Level of Readiness

• No technical skills,
• Staff unavailable
• Lack of motivation
• Political infighting,
• Assassins,
• CIO ready to retire and doesnt want to take
  risks
• The business that neither wants the data
  warehouse nor has the money or the inclination to
  participate in any data warehouse endeavor.

17
Multiple Sponsors Want their Data Warehouse Now

• Cant satisfy all sponsors at once.
• Dont want them to run off and develop their own
  data warehouse
• Establish a business advisory board/steering
  committee.
• Representatives from the business, not from IT.
• They would make the high-profile business
  decisions
• They determine which applications have the most
  value to the business and which should be
  developed first.
• Takes the heat off the data warehouse manager.

18
Unrealistic User Expectations

• Set user expectations early and often for
• Schedule,
• Function,
• Performance,
• Availability,
• Data quality,
• Freshness.

19
Users Dont Know What They Want

• Develop a proof-of-concept  
• Compile stories about what other organizations in
  this industry are doing with the data warehouse.
  This will often get the users interested and
  focused on specific applications and
  capabilities.

20
No Team Consensus

• Team members who disagree
• Delayed making decisions and moving ahead with
  the project.
• Total consensus not appropriate
• Someone needs to be given the authority to make
  decisions.
• Decisions should be made after consultation with
  specific team members
• Delivering a high quality product on time and
  within budget is the goal.

21
Consultant Will Solve All Your Problems

• Be skeptical of any such representation
• Ask the consultant how they plan to go about
  fixing all your problems
• Evaluate their solutions, their capabilities and
  commitment to your organization.
• Ask the consultant to back up their proposal with
  specific and substantial written guarantees in
  the event they are unable to solve all your data
  warehouse problems.

22
Contractors are Gone

• 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.  
• The manager must determine if the existing system
  can and should be salvaged.

23
Contractors are Gone

• There are a few questions that must be answered
• Are the users using the existing system?
• Are these users dependent on the system?
• How dirty is the data and what are the problems
  related to data quality?
• Are there any pieces of the system that can be
  salvaged?
• Where are the problems, what is their impact?
• Are the problems political, perceptual, or
  technical?

24
Contractors are Gone

• Generate a plan of action and to create a set of
  recommendations to management to either fix the
  system or to discard it and start over.
• If the users are not using the system and are not
  dependent on it, throw the system away.

25
Knowledge Transfer Not Happening

• Knowledge transfer must be in the contract with
  identifiable measurements
• Project plan should have included tasks for
  knowledge transfer and should have allowed time
  in the schedule for knowledge transfer for the
  tasks where the consultant had the primary
  responsibility and where the client was supposed
  to learn how to perform the task.

26
How to Best Use Consultants

• Consultants should be brought in to help
• build an architecture,
• establish data warehouse infrastructure
• create standards if none exist,
• recommend a data warehouse methodology,
• develop a project plan,
• develop a project agreement
• select tools.
• Make sure you have people in place to be catchers
  for the skills transfer

27
Outsource the Data Warehouse?

• In an outsourced situation, contracts are very
  clear about what will and what wont be included.
  
• In DW, users are never able to articulate all
  their requirements upfront. Each new request will
  require renegotiations and contract changes.
• By the time these details are worked out, the
  opportunity will be lost.
• The data warehouse should not be outsourced!

28
Management Doesnt Believe Your Estimates

• Learn from people who have implemented similar
  projects
• Obtain project plans from others
• Understand the skill levels of those who
  performed the tasks.
• Consultants can also be provide input but dont
  have the same level of credibility.

29
Management has Committed to an Unrealistic
Deadline

• Do you want to tell upper management the bad
  news now or later?
• Bring in selected contractors with specific
  skills who can shorten some of the major
  activities,
• Move some of the major functions or source files
  or user groups to subsequent phases and deliver a
  subset of the originally agreed upon function.

30
The Scope is Expanding

• Do not throw additional people on the project.
• Do not agree to the changes without other
  concessions.
• Do not say No. IT has a terrible reputation for
  being unresponsive to the user community.

31
An RFP will Slow us Down

• Keep the RFP as simple and short as possible.
• Only include points you will use for comparing
  the vendors. This means only including
  mandatory and highly desirable features.
• Do not include nice to have and definitely do
  not include any blue-sky requirements.

32
Only 5 of the intended user set are using the DW

• The training needs to be geared to the users
  level of interest and capability.
• Give them pre-defined queries and reports and
  teach them how to launch those queries and
  reports.
• Teach them about the data and sell them on their
  new-found capabilities.  
• Evaluate the effectiveness of the training with
  course evaluations both immediately after the
  class and two-months after the class is
  completed.

33
The tools have already been chosen

• If those tools wont perform or are otherwise
  unacceptable, the selection committee must reject
  the decisions.
• First determine if the tools will perform.
• See these tools in action at other installations.
  
• Determine if the tools are running the volume and
  complexity of your intended workload
• Determine if they are running the data volumes
  you anticipate.

34
Vendor Goes Over Your Head

• Have a written agreement with the vendor before
  the selection process starts.
• The vendor needs to know there will be no
  reconsideration and if they pursue their course
  of action, the vendor will be blackballed from
  any further marketing at this company.
• Meet with management and explain the evaluation
  process, explain how and why the decision was
  made.

35
How to Eliminate Redundant Data

• Capture the meta data that will help to identify
  the redundant data.  
• Need cost justification for eliminating redundant
  data. The company should determine the cost of
  creating, storing and maintaining redundant data.
  
• The cost of eliminating or at least controlling
  the redundant data is high.
• Use a triage approach to determine which
  redundant data to eliminate and which to leave
  alone.

36
Dirty Data Underestimated

• Get a report card on the data quality and provide
  that report card to management
• missing values,
• missing values in mandatory fields,
• non-unique values in a field where the values
  should be unique,
• violation of business rules (e.g., a negative
  number of dependents, year of birth greater than
  todays date),
• invalid data types (e.g., character that should
  be packed decimal).

37
Want to Avoid Stovepipes

• A stovepipe cannot integrate with any other
  system.
• No common keys to allow joining of data across
  these stovepipes.
• Multiple versions of the same data, violating the
  idea of a single version of the truth.
• Need someone in a position of authority
  overseeing all of these data marts to assure the
  commonality that will allow joining across the
  data marts as well as a unified vision of the
  corporate approach to data marts.

38
Business Sponsor Wants Real-Time Updates

• Is this an operational system?
• If so, put it on its own track.
• Decision support needs point-in-time.
• Designs for decision support and operational
  systems are very different.

39
DW Reports Dont Match those in Operational
Systems

• Should they? Whats different?
• Data cleanliness
• Timing
• Absent or added data
• Meaning of the data
• Education of differences

40
Inadequate Architecture

• Can the architecture be salvaged?
• Architecture must be able to scale.
• Database size
• Source files
• Number of concurrent users
• Complexity of queries/reports

41
Develop a DW Simultaneously with Developing
Source System

• Wait until the operational system is reasonably
  stable before working on ETL
• Some tasks that can be accomplished in parallel
• Build the infrastructure
• Train and work with the tools
• Build prototypes with sample data
• Develop project plans
• Write project scope agreements
• Create the cost justification
• Analyze risk and plan for risk mitigation

42
Data Warehouse is Growing Too Fast

• Implement a cost justification methodology.
  Charge the users for the data they request.
• Instruct users on the costs and problems of
  frivolous requests for data.
• With the users, evaluate the necessity of keeping
  atomic data as well as summarized data.
• Carefully consider the need to replicate data.
• Understand the need for freshness
• Allow sharing of data rather than each user
  having their own copy of data warehouse data.

43
Summary

• The data warehouse will have impossible
  situations
• They arent really impossible, just difficult
• Learn from what others have done
• Search out best practices
• Non corrburundum illegitimi