Transcript Data Warehouse Assessments - DAMA

Conducting Data
Warehouse Assessments
Joyce Bischoff
Bischoff Consulting, Inc.
Hockessin, Delaware
302-239-7202
©1999, 2002, Joyce Bischoff, All rights reserved.
Agenda
• Definition
• Reasons for assessments
• Assessment Approach
–
–
–
–
Building the Team
Assembling Information
Using Leading Questions
Interviewing managers, users,
user support, technical staff
• Reporting the findings
– Assessment Topics
• Summary
©1999, 2002, Joyce Bischoff, All rights reserved.
What is it?
©1999, 2002, Joyce Bischoff, All rights reserved.
Data Warehouse Assessments- Why?
To ensure:
• Business objectives are met
• Users are satisfied
• Technical staff is working effectively
• Corporate infrastructure is in place
• DW / DM processes are working effectively
• To ensure that tradeoffs are understood
– Data currency & integrity
– Data availability
– User satisfaction
– Performance
– Joyce
Flexibility
©1999, 2002,
Bischoff, All rights reserved.
Scheduling Assessments and Reviews
ASSESSMENTS
• Readiness Assessment
• In-Development
Assessments
• Post-implementation
Assessment
©1999, 2002, Joyce Bischoff, All rights reserved.
DESIGN REVIEWS
• Architectural Review
• Data Warehouse Data
Model Review
• Data Transformation &
Cleansing Review
• Programming Review
• Code/Utility Review
• Performance Review
• Includes all of the
above
The Approach
Assess the current situation
Gather information
& Identify problems
Investigate
» JAD sessions
» Interviews
» Questionnaires
» Documentation
©1999, 2002, Joyce Bischoff, All rights reserved.
Recommendations
to correct problems
The Approach
Executives &
Managers
Is the data
warehouse
a success?
Users
Technical Staff
©1999, 2002, Joyce Bischoff, All rights reserved.
Building the Team
• Team members
–
–
–
–
–
Need mix of skills to examine Assessment Topics
Must provide objective evaluation
Need interviewing skills and technical skills
Should understand best practices in the industry
Makes recommendations based on best practices
• Each topic needs an assigned leader
• Team may include 2-6 people (average)
What about
politics??
©1999, 2002, Joyce Bischoff, All rights reserved.
Assembling Information
• Project scope & documented requirements
• Business objectives of DW
• Organization chart, including list of data
warehouse staff, key managers, and users
• Roles & responsibilities and how assigned
• Training programs for users and DW team
• DW architecture description, including
data marts, ODS, DW, and relationships
• Standards, guidelines, & procedures
©1999, 2002, Joyce Bischoff, All rights reserved.
Using Leading Questions
• Asking the right questions at the right time is
the basis for successful assessments
• Leading questions provide a basic check list
and should be prepared in advance
• Drill down as far as necessary to obtain
needed information
• DW assessments require more than
a checklist. . . . but a checklist helps!
©1999, 2002, Joyce Bischoff, All rights reserved.
Using Leading Questions
• Questions:
–
–
–
–
–
Must be tailored for your situation
Printed questionnaires; may be anonymous
JAD sessions
One-on-one interviews
Always ask for additional comments
• Leading questions are only a starting point
• Answers point the way to
identifying weaknesses and
proposing improvements
©1999, 2002, Joyce Bischoff, All rights reserved.
?
Assessment Topics
Business
Development
Requirements Methodology
User
Satisfaction
Business
Value
DW Strategy
©1999, 2002, Joyce Bischoff, All rights reserved.
DW
Architecture
Organizational
Issues
Standards &
Guidelines
Vendor
Relationships
Assessment Topics
Future
Planning
Metadata
Strategy
Performance
& Capacity
DW
Administration
Data
Modeling
Data
Management
Data
Access &
Delivery
©1999, 2002, Joyce Bischoff, All rights reserved.
Database
Design
Sample Questions for Executives & Managers
• Is the data warehouse a success?
– Business value
– Return on investment
– Organizational issues
• From your perspective, what are
its strong and weak points?
• Did it meet strategic objectives?
Which ones?
• Future plans for the DW or DM
• Are there particular persons that we should be sure
to talk with (in addition to our present list)?
©1999, 2002, Joyce Bischoff, All rights reserved.
Sample Questions for Technical Staff
• Why was the DW / DM built?
• Discuss the following areas with appropriate
members of the technical staff:
Discuss the process &
– User satisfaction
results of the processes
– Standards & Guidelines
for each technical area
– Metadata strategy
–
–
–
–
–
Data management processes
Performance and capacity planning
Data access and delivery issues
Data modeling & database design
Future planning
©1999, 2002, Joyce Bischoff, All rights reserved.
Sample Questions for Users
• Project starts with user requirements and will be judged
against them
• Why was the data warehouse built?
• Were your special requirements considered?
• Business value realized? Money saved? Improved
efficiency? Most important benefit realized?
• What is the most important question that you cannot ask?
• Technical staff responsive to new requirements?
• Level of satisfaction with DW/DM, ease of
use, end-user tools, availability, etc.?
• Is data easy to use?
©1999, 2002, Joyce Bischoff, All rights reserved.
End User Support Help Desk
•
•
•
•
•
•
•
Adequately staffed?
Types of problems encountered?
Do you have problems addressing problems?
Adequate support from the technical staff?
Users adequately trained in data and tools?
User expectations well managed?
How could it be improved?
©1999, 2002, Joyce Bischoff, All rights reserved.
Building the Report
• Summarize findings of interviews, JAD
sessions, questionnaires, and analysis
of documentation
• Cover each assessment topic that was
addressed during the review
• Following pages summarize sample
issues in each subject area
©1999, 2002, Joyce Bischoff, All rights reserved.
DW Strategy
What is the data
warehouse strategy?
Is it designed to fundamentally change
the way business decisions are made?
Does the strategy allow for
iterative development with increasing
benefits after each cycle?
©1999, 2002, Joyce Bischoff, All rights reserved.
Business Value
How / who sets
priorities for DW
development?
Review procedures
for evaluating ROI
©1999, 2002, Joyce Bischoff, All rights reserved.
Business benefits
from using DW, DM,
or ODS?
Business Requirements
• Review processes for:
– Gathering requirements
– Determining new requirements
– Keeping requirements current
– Identifying gaps between stated
requirements and actual capabilities
• How well are current requirements met?
• Suggest ideas for encouraging creative
use of existing data
©1999, 2002, Joyce Bischoff, All rights reserved.
Organizational Issues: Leading Questions
• Has an “official” strategic alliance been
created between the users and IT staff?
• Is there a high level user sponsor?
• Mission and objectives in line with corporate
business objectives?
• Sufficient budget allocated?
• Organizational roadblocks?
• Who are the users? How will they be
involved?
• How will the users be trained and supported?
• What are the users’ expectations?
©1999, 2002, Joyce Bischoff, All rights reserved.
Organizational Issues: Leading Questions
• Process in place for responding to user complaints
about performance, data quality, data availability,
data integrity, and consistency?
• Does the technical staff have the skills needed to
support the DW?
• Roles and responsibilities defined and assigned?
• Source data documented?
• What is the quality of the source data?
• How will technical personnel track data usage and
changing user requirements?
• End-user tools and development tools selected?
©1999, 2002, Joyce Bischoff, All rights reserved.
DW Methodology
• How are data warehouse projects
planned?
• Does everyone understand definitions
of DW and data warehouse technology?
• Effective methodology in place?
– Are RAD techniques used?
– All phases and activities time-boxed?
– Methodology flexible enough to
accommodate projects of varying
sizes, ODS, DM, DW?
©1999, 2002, Joyce Bischoff, All rights reserved.
DW Architecture
• Review use of DW, DM, ODS and their
relationships
• Review effectiveness of current
hardware / software / DW tools
• Scalability?
• How does the data flow?
©1999, 2002, Joyce Bischoff, All rights reserved.
Data Management
• Do you track data usage? Table level? Column
level? Row level?
• Do you track trends in growth of specific tables?
• How do you allocate space?
• How do you determine what data
needs to be audited?
• Do you know which users use which data?
• Do you know which access paths are needed by
various users? How do you determine this?
• What is your process for implementing new access
paths?
©1999, 2002, Joyce Bischoff, All rights reserved.
Data Management
• Review process of selecting source data, extraction,
cleansing, and transformation
• Review processes for maintaining data quality
• Are there problems with data consistency?
• Identify opportunities for data
integration
• How do you handle requests
for additional data?
• How do you handle backup, recovery,
reorganization, etc.?
©1999, 2002, Joyce Bischoff, All rights reserved.
Performance & Capacity Planning
• How do you measure performance in the DW?
• Do you have service level agreements?
– On what are they based?
• How do you measure user satisfaction with performance?
• How do you measure trends in performance?
• Comparisons of today vs. another day? This week vs. last
week?
• How do you identify hot spots in databases?
• How do you identify candidates for re-partitioning?
• Do you have a scalable environment in terms of hardware
and software?
©1999, 2002, Joyce Bischoff, All rights reserved.
Metadata Strategy
• Review existing metadata strategy
– Locations and consistency between
locations
– Processes for keeping it current
– Accessibility to users and technical
personnel
©1999, 2002, Joyce Bischoff, All rights reserved.
DW Standards, Guidelines, and Procedures
• Review standards, guidelines, and
procedures, if available
–
–
–
–
–
–
–
Requirements gathering and documentation
Architecture guidelines
Data and metadata
Database design, development, administration
Development
Data quality, cleansing, transformation
etc.
• Assess effectiveness
©1999, 2002, Joyce Bischoff, All rights reserved.
Data Access & Delivery
DSS
EIS
Ad Hoc
Data
Mart
Traditional
Data
Mart
Data
Mining
©1999, 2002, Joyce Bischoff, All rights reserved.
Data
Warehouse
Data
Mart
Data Modeling
•
•
•
•
•
•
What types of data modeling are used?
Are they effective?
Compare with best practices
Where are models stored?
Are they integrated and reusable?
Do users have access to data models?
– Which ones?
– How do they use them?
©1999, 2002, Joyce Bischoff, All rights reserved.
Database Design
• Review processes used to design
databases
–
–
–
–
Usage of data models
Selection of DBMS
Design review processes (if applicable)
Design guidelines for specific DBMSs
• Review database designs for selected
subject areas
– Multi-level data architecture?
– Managed data redundancy?
• Compare with best practices
©1999, 2002, Joyce Bischoff, All rights reserved.
DW Assessment Report
• Compile the results of all interviews, JAD
sessions, questionnaires, reviews, and
analysis of documentation
• Include the topic, findings, and
recommendations for each area analyzed
• Level of detail will depend upon concerns
raised by interviewees
– Concerns will require deeper analysis in selected
areas
– Findings will depend upon the
persons interviewed!
©1999, 2002, Joyce Bischoff, All rights reserved.
Summary of the Process
• Business
Value
• ROI
• Design
• Development
• Administration
• Organization
Executives
&
Managers
©1999, 2002, Joyce Bischoff, All rights reserved.
Users
• Management
processes
• Strategy
Technical
Staff