Transcript Document

Project Methodology for Building and
Enhancing a University Data Warehouse
Prepared for Northwestern University Forum
Best Practices in Data Warehousing in Higher Education
by
Andrea Ballinger, Director of Data Warehousing
and
Lisa Courtney, Project Coordinator
University of Illinois
April 19, 2005
1
© 2005, The Board of Trustees of the University of Illinois







Objectives
UI Background
Methodology
Resource Management
Project Reporting
Lessons Learned
Summary
2
© 2005, The Board of Trustees of the University of Illinois
Objectives
Learn ways to…..
1. Reduce project time by using a methodology
that works for DW
2. Increase resource efficiency without sacrificing
collaboration
3. Provide high-value outcomes to users
3
© 2005, The Board of Trustees of the University of Illinois
“You can have it good … You can have it fast …
You can have it cheap ... Pick any two.” Red Adair
SCOPE
Good
Meets User Needs – Creates Value
High Level of Communication
Users are Equipped
Minimal Errors: “Production Ready”
Fast
Cheap
TIMELINE
BUDGET
Meet Critical Dates
Efficient Use of Resources
Provide Early Access for Report
Developers
Cost of Producing Deliverables
Other Key Dates
© 2005, The Board of Trustees of the University of Illinois
Productivity
4
Fire Prevention Guidelines for Project
Coordination
 Know your criteria for success before you begin
 If you don’t know where you are, where you’ve been,
and where you’re going, you’re out of control
 Incorporate formal project check points
 Planning is cheap - reacting is expensive
 Start with a baseline for duration, cost, and
performance; it’s all about choices …..
5
© 2005, The Board of Trustees of the University of Illinois







Objectives
UI Background
Methodology
Resource Management
Project Reporting
Lessons Learned
Summary
6
© 2005, The Board of Trustees of the University of Illinois
UI Background
• History, Mission and Focus of the University
of Illinois Data Warehouse and Decision
Support
• Staffing
• Products, Services and Uses
• Post ERP Organization
7
© 2005, The Board of Trustees of the University of Illinois
History of Data Warehousing at University of Illinois
 UI history of decentralized data management
 Past warehouse-like projects with limited or no success
 UI-Integrate implementation of ERP system - SCT Banner started in
2000
 ERP planning team told they must have a way to “get the data out
of the ERP”
 Decision Support team launched with intention of building a
permanent unit
 Parallel deployment and development of SCT Banner
 Common Attitudes:
•
•
•
•
Mistrust of central projects - Give me the data and get out of the way!
Minimal experience with data warehouses or data marts - A what mart?
Operationally and report focused - Who will replace my reports?
Expectations of extensive data clean up - Give me access now; I have a lot to
do.
 Intense growth of the group since 2000
8
© 2005, The Board of Trustees of the University of Illinois
UI Decision Support Mission
To support integrated and secured management
reporting, analysis and decision making by
providing the University with the infrastructure
and services to access data and metadata.
9
© 2005, The Board of Trustees of the University of Illinois
UI Decision Support Focus During the Project Years
(FY01-FY05)
 Develop a Data Warehouse - a new component of the University’s information
management architecture
•
•
5 year project concurrent with ERP implementation
$17.7 M budget allocated for staff, software, and data projects
 Major steps
•
•
•
•
•
Build the technical infrastructure
Create the processes to move data from the production systems to the EDW
Create the basic data model
Populate with data collected from Banner as each module of ERP implementation was completed
Create education and training materials and process for users
 Features
•
•
•
•
•
Single integrated source for analysis and ad-hoc reporting
No longer have separate systems for data on students, staff, finances, etc.
Maintain daily history for trend reporting
Format data for ease of use
Support use of data with documentation and training
10
© 2005, The Board of Trustees of the University of Illinois
© 2005, The Board of Trustees of the University of Illinois
Nov-04
Oct-04
Sep-04
Aug-04
Jul-04
Jun-04
May-04
Apr-04
Mar-04
Feb-04
Jan-04
Dec-03
Nov-03
Oct-03
Sep-03
Aug-03
Jul-03
Jun-03
May-03
Apr-03
Mar-03
Feb-03
Jan-03
Dec-02
Nov-02
Oct-02
Sep-02
Aug-02
Jul-02
Staff Levels Peaked During 5-Year ERP Related Project
80
70
60
50
40
Project FTE
Actual FTE
30
20
10
0
11
Today, the Data Warehouse is Established and Supported
Special-purpose
“data marts”
“Library” of all data
699 tables
15,022 fields
145 GB total database size
470 MB daily increase
Undergraduate
Admissions
Graduate
Admissions
Finance
Financial
Aid
Human
Resources/
Payroll
Biographic
Demographic
Recruiting &
Admissions
Registration
Census
Recruiting
Contacts
Records &
Registration
Financial Awards
Catalog &
Schedule
12
© 2005, The Board of Trustees of the University of Illinois
Services are User-Centric
Daily question
answering service
Campus
practice labs
Nightly integration
of new data
Self-service
data education
Data documentation
(metadata)
Regular schedule of
data enhancements
Standard
Report Directory
Query
Clearinghouse
Listserv
announcements
Query tool training
Data views that
make connections
Monthly user
group sessions
13
© 2005, The Board of Trustees of the University of Illinois
Business Intelligence Uses are Evolving
Reports (4,300 users of Business Objects Reports from multiple data sources)
–
–
–
–
–
–
General ledger statements
Grant summaries
Encumbrance listings
Payroll transactions
Employee directories and staffing reports
Course listings
–
–
–
–
–
Class rosters
Legislative reports
Instructor lists
Insurance eligibility
Applicant counts and profiles
–
–
–
Instructor list system
Activity reporting
Graduate admissions information
Download warehouse data for applications
–
Campus staff directory
–
Federal SEVIS compliance program
–
Tuition assessment monitoring system
–
Grants monitoring
Detailed analysis (currently 2,000 data warehouse accounts with 600 active users)

Facilitate student recruitment with analysis of recruitment strategies and their effectiveness by student category

Increase timeliness of requisition processing by analysis of time required at each stage

Disbursement analysis and compliance reviews for grant funding

Support faculty retention and success of faculty teaching and research with a “whole person view” of courses, students,
grants, compensation & benefits, leave & sabbatical data

Salary analysis
Items on this slide are samples of current uses and planned uses as reported by users.
14
© 2005, The Board of Trustees of the University of Illinois
Hierarchy of Needs
Maslow's Hierarchy of Needs
Hierarchy of Data/Reporting
SelfActualization
Sharing
Queries
Value Add
Esteem
Creating
Own Queries
Love
Accessing Reports
Dynamically
Safety
Physiological
Downloading Current
Data via Reports
Traditionally
Analytical
(Fewer #s)
Traditionally
Operational
(Larger #s)
Replacing Static Legacy Reports
15
© 2005, The Board of Trustees of the University of Illinois
Decision Support Organization Structure for FY 06
Decision Support’s postproject organization
combines business, data
and technical positions
in an integrated
structure that facilitates
close coordination
among staff.
16
© 2005, The Board of Trustees of the University of Illinois







Objectives
UI Background
Methodology
Resource Management
Project Reporting
Lessons Learned
Summary
17
© 2005, The Board of Trustees of the University of Illinois
Methodology
• How DW project methodology differs from
application development methodology
• Iteration vs. specification at key points in
the process
• Project structure and key deliverables
• Using checkpoints during design to reduce
rework in the build phase
18
© 2005, The Board of Trustees of the University of Illinois
Analysis Requirements Are Broader Than Applications
What is needed?
What is the task?
HR Analysis
Staff Retention
Is there a higher retention rate for
department staff that take leave on
a regular basis compared to those
that carry over leave balances from
year-to-year? Is this an anomaly or a
trend?
HR Transaction
vs.
Staff Retention
Leave Adjustment Transaction
• Department level data for current
• Department level data incorporating
changes over time
period only
• Leave balances by multiple staff
types, over several time periods
• Length of stay by multiple staff types
• Repeatable comparative analysis
output (graphs, charts) to identify
trends
Leave Adjustment Transaction
Leave balances need to be monitored
and adjusted to reflect leave accrued
and/or taken for the department
• Leave balances for active staff only
vs.
in the current period
• Report output for monitoring
• Application form for adjustments
19
© 2005, The Board of Trustees of the University of Illinois
Data Warehouse Development is Highly Iterative
Traditional Application Development:
 Because there are a limited number of use
cases, specifications can used to
communicate requirements and test cases.
Data Warehouse Development:
 Uses prototyping and collaborative reviews
to refine the requirements for the most
common queries, without limiting the
possible analyses. Common queries are
used for test cases, but open testing is also
used.
© 2005, The Board of Trustees of the University of Illinois
20
Data Warehouse Development Focuses on Data
Relationships
Analysis Requires Relationships:
The Data Warehouse relates data
among faculty, staff, students, alumni
and vendors, enabling proactive
management of constituent
relationships.
Designed with Data Relationships:
Data relationships in the Data Warehouse reflect the real
influences and impacts that occur in the University. Unlike
“workflow” relationships needed to support transactions, data
relationships can change as new data is added or new
relationships occur in real life.
21
© 2005, The Board of Trustees of the University of Illinois
Data Warehouse Development Process Involves Users
with both Business Staff and Data/Technical Staff
Begins with user’s
business priorities
Work
Planning
Business
Requirements
Specified
Functional
Design
Iterative
Technical
Design
Specified
Development
Specified
Quality
Assurance
Iterative
Business Expertise
Deploy to
Users
Specified
Business Expertise
Data/Technical Expertise
Metadata Development
Implementation Coordination
Business & Data
Experts work with
users to create
design
© 2005, The Board of Trustees of the University of Illinois
Business & Data
Experts work with
users to test design
22
Positive Outcome Example: Budget Operating Statements
Project – Stage 1 – Fall, 2004
 Objective: Relate data from HR Salary Planner and Finance
Budget with existing data in the EDW, so that users - primarily
business managers in colleges & departments - could develop
comparative reports of budgeted dollars, FTEs and accounts with
actual dollars, FTEs and accounts grouped by college and department,
including detail at the job level
 Approach: Prototyping in the design step enabled users to
interact with the data and share with both the business and technical
staff feedback regarding data relationships, business rules, and
granularity. User/business/technical team allowed for iterative
design process, and actual user queries to be used in testing.
 Outcome: Achieved high user satisfaction, with less work effort
than anticipated. Users had only a few minor adjustments in the User
Acceptance Testing process, and there were no post-implementation
corrections.
23
© 2005, The Board of Trustees of the University of Illinois
Iterative Design Process Required Team of Business &
Technical Staff Collaborating Face-to-Face with Users for
Positive Outcome on Budget Operating Statements
Need to compare HR and Finance
Budget data in detail
Work
Planning
Business
Requirements
Specified
Functional
Design
Iterative
Technical
Design
Specified
Development
Specified
Quality
Assurance
Iterative
Business Expertise
Deploy to
Users
Specified
Business Expertise
Data/Technical Expertise
Metadata Development
Implementation Coordination
Created working prototype of data
model and users provided feedback
through several iterations
© 2005, The Board of Trustees of the University of Illinois
User-developed queries from
functional design step became
test cases in for final data
structures and universe
24
Goals for Methodology
 Management and Stakeholder “Ownership”:
Gained through planned review points
 Business Solution Driven: Clearly identify the
business tasks, business purpose and functional roles
at project inception.
 Rapid Delivery of User Value: Release new
functionality in 90 to 120-day development stages
 Reduce Development Rework:
• Clear definition of project scope
• Iterative functional design using prototyping
• Implement change control on technical design
25
© 2005, The Board of Trustees of the University of Illinois
Data Project Process: Multiple Stages of 90-120 Day Cycles
Month 1
A
Month 2
Month 3
Month 4
Revise
WP
B
Month 5
Month 6
Revise
WP
Month 7
Revise
WP
Month 8
Revise
WP
Entire Project
Feasibility
& Work Planning
1
2
3
4
Stage 1: Initial Functionality
1
2
3
4
Stage 2: Additional Functionality
1
2
3
4
Stage 3: Additional Functionality
CLOSE
Review Points
A = Strategic Scope Review
1 = Design Scope Review
B = Statement of Work
2 = QA1: Technical Review 4 = Go/No-Go Decision
© 2005, The Board of Trustees of the University of Illinois
3 = QA2: Technical Review
26
Data Project Short Cycle Methodology: 90-120 Day Cycle per Stage (High Level)
Repeated Each Stage
Once Per Project
Feasibility
Analysis
Business Case
Strategic
Scope
Review
Requirements
& Functional
Design
Project
Statement
Of Work
Prototype
Statement of
Work Approved
Project
Initiation
Input
Work Planning
Review
Point
Project Plan
Technical
Design
Development
Design Plan
Developed
Code
Design Scope
Review
QA1:Technical
Review*
QA2:Technical
Review**
Functional
Specification
Technical
Specification
Code
Modification
s
Requirements
+Data
Output
© 2005, The Board of Trustees of the University of Illinois
QA & Delivery
Testing
Go/No-Go
Decision
Rollout
*QA1: Does the technical design meet the requirements?
**QA2: Does the developed code meet the technical specification? 27
Data Project Short Cycle Methodology: 90-120 Day Cycle per Stage (Detail)
Once Per Project
Feasibility
Analysis
Work Planning
Business Case
Project SOW
• Deliverables
FAC/RMC and
Stakeholders
• Timeframe
• Work Estimate
Project Mgr
Strategic
Scope
Review
for fit & commitment
Director
Statement
Of Work
Approved
BAM, DAM, TAM
Project
Coordinator
Project Initiation
• Schedule
• Assign Project Mgr
• Identify Resources
Project
Coordinator
Input
Review Output
Point
Project Plan
• Deliverables,
Tasks, & Subtasks
• Work
Assignments
• Milestones
Project Mgr
Repeated Each Stage
Requirements
& Functional
Design
Prototype
•Business Needs
•Data Behavior
• Iterations
DA/DM/RA/FAC
Design Scope
Review
BAM, DAM,
Services Mgr
& Stakeholders
Functional
Specification
• Logical Data
Model
• Universe Mock-Up
RA/DA/DM
FAC – Functional Area Coordinator
RMC – Requirements & Metadata Coordinator
BAM – Business Architecture Manager
DAM – Data Architecture Manager
© 2005, The Board of Trustees of the University of Illinois
Technical
Design
Design Plan
•Logical/Physical
Data Model
• Design Specification
Development
Testing
Developed Code
• ETL with Validation
• Universe (unit tested)
ETL/BOS
DM/DA/TA
QA1:Technical
Review
QA2:Technical
Review
BAM, DAM, TAM
BAM, DAM, TAM
Technical
Specification
• Source to Target
Maps
• Universe
Specification
DA/BOS
QA & Delivery
Code
Modifications
• Revised Specs
•ETL with Validation
• Universe (unit
tested)
DA/ETL/BOS
• System
•Integration
•UAT
•Regression
FAC/RA/DA/PM
Go/No-Go
Decision
DS Management
& Stakeholders
Rollout
• Training
• Post-Deployment
QA
FAC/RA/DA/P
M
DA – Data Analyst
TAM – Technical Architecture Manager
DM – Data Modeler
TA – Technical Analyst
ETL – ETL Developer
BOS – Bus Obj Specialist
28
Transition to Production is an Essential Project Phase
• There will be changes after go-live
• Plan the transition into the project plan – 30-60
days after go-live and retain project resources
• Involve production support in the transition
• Manage production work (post go-live) in a
similar manner as project work, i.e., Change
Management
• If there is a large amount of work, group it into
post go-live releases
29
© 2005, The Board of Trustees of the University of Illinois
Implement the Methodology with Key Project Documents
• Statement of Work
–
–
–
–
Business Case (Need, Audience, Outcome)
High-Level Timeline
Resources
Risks
–
–
–
–
Phases
Deliverables
Tasks
Resources
–
–
–
–
Description
Impact
Actions
Owners and Resolution Date
–
–
–
–
–
Groups and Individuals
Type of Communication (Presentation, Review, etc.)
Frequency and/or Dates
Contacts
Owners
• Project Plan
• Issue Log
• Communication Plan
30
© 2005, The Board of Trustees of the University of Illinois
Implement the Methodology with Key Project Documents
• Functional Specification
–
–
–
–
–
High-Level Requirements
Line Item Requirements (Data Elements & Business Rules)
Sourcing Analysis
Security Requirements
Application Specification
• Technical Specification
– Data Model
– Load-Flows & Source to Target Mappings
– Application Structure
• Testing and Change Control
– Test Cases
– Queries
– Change Control Document
• Production Turnover
– Change Control Document or Production Change Request
– Log of Changes and/or Release Plan
31
© 2005, The Board of Trustees of the University of Illinois
Keys to Building Effective Collaboration With
Users Into Data Warehouse Development
 Determine the mix of users for the products you are developing
• College & Department Users: Data Analysts, Business Managers, etc.
• Functional Offices: Admissions, Business & Financial Services, HR, Payroll, etc.
• Data Offices: Institutional Researchers, Planning, Budgeting
 Invite these users into the process by providing input and review
options
•
•
•
•
•
Focus Groups
Interviews
Specification Documents Review
Model and Application Prototype Reviews
Large Group Information Session Presentations (eg., Business Managers Group)
 Identify Obstacles and Seek Input
• Address objections early
• Request participation
 Involve the Same Users in Requirements Definition and Testing
• User Acceptance Testing – Hands-on
• Preview with Power Users
32
© 2005, The Board of Trustees of the University of Illinois
UI Sample Structure for Collaboration
Decision Support
Plan Focus Groups to Determine # of Groups and # of Participants;
Conduct Focus Groups, Collect Requirements; Design and Confirm
Logical Model(s)
Work
Planning
Business Reqs
Analysis
Work Plan Rev.
Build Databases and
Metadata;
Create Business Objects
universe ;
Unit Test;
Plan Training
Design and confirm
Physical Model(s) and
Data Staging
Resolve Data Issues; Validate Data;
Test and Train
System Test
Detailed Analysis
Design
Construction
User Test
Integration Testing
Implementation
Analysis
DS-ERP joint meetings
Collaborate to Plan:
? SCT training for DS
? ERPDS and BO
Overview
? DS CRP attendance
? ERP team participation
in DS Focus Groups
Confirm Banner data model;
Overview of Business Objects &
EDW;
Consensus on DW Reports;
Report walkthrough - ERP reports
sourced from DS
Weekly status meetings to coordinate test phases & test data availability
Test plan development & phase integration
Production quality data available to pull from Banner environment
Build and Unit Test
Components
Design
Work Planning
Design
Phase
Org'n
Prepare and Conduct CRPs
Component Design
Develop Conversion Plan
Plan
Impl.
phase
Plan System Testing
?
?
?
?
Current
Reports
Interfaces
Banner
Tables
Current
systems
to convert
Resolve
Issues
End User Testing
System Testing
Roll
out
Mock Conversions, Data Purifications
Implementation
Design
ERP sends file
locations to
DS:
Integration Testing
GO LIVE
Informal
Contact
Between DS
and ERP team
Finalize "TO BE"
process
Document "AS IS" process
Plan "To Be" process
UI2
?
?
?
?
?
?
?
informs DS about
Banner mods,
Security roles,
Privacy concerns,
Conversion plan
Conversion of History plan,
Banner interfaces
Completion of report
functional design
specification
ERP sends DS
? Report specifications,
? Reporting Needs Inventory
© 2005, The Board of Trustees of the University of Illinois
Design, Build, and Unit Test
Interfaces,
?
Conversions,
?
Business Objects
?
Universe,
Banner Reports
?
ERP Teams
Iterative full volume mock conversions
and dress rehearsals ; catch
and fix data exceptions
Iteratively execute scripted test cycles, sub cycles and business
processes using prepared data elements
33







Objectives
UI Background
Methodology
Resource Management
Project Reporting
Lessons Learned
Summary
34
© 2005, The Board of Trustees of the University of Illinois
Resource Management
• Project roles
• Gaining ongoing commitment from owners
of shared resources
• Estimation of resource time
• Tracking resource time
35
© 2005, The Board of Trustees of the University of Illinois
Project Roles Require a Mix of Business and Technical
PC
Implementation Coordination
IC
Iterative
Business
Requirements
Work
Planning
BA
FAC
Roles
BA
Iterative
Functional
Design
FAC
DD
Technical
Design
BA
DD
DA
Development
ETL
Quality
Assurance
BIS
DD
BA
Deploy to
Users
CS
FAC
TA
Ensures that
development
activities are
driven by needs
of users
•What is the
business
problem and
what data
solution is
needed?
•What specific
data is needed
from the source
system?
What is the
technical road
map from the
source to the
EDW?
What definitions
and business
rules apply?
•How does it
need to be
related?
• Build code to
Extract,
Transform and
Load data
• Build Business
Objects
Universe or
Application
Metadata
BA Business Analyst
FAC
CS
Functional Area Coordinator
DD Data Designer ETL ETL Developer
DA
Data Architect
BIS
Business Intel. Spec.
BA
•Load data and
validate with
counts and
comparisons
•Test the
Business Objects
Universe
Explain how to
use the data and
the Business
Objects Universe
Enable security
and access
DD
PC Project Coordinator
TA
Technical Analyst
IC
Implementation Coordinator
Communication Specialist
36
© 2005, The Board of Trustees of the University of Illinois
Project Roles Used by Decision Support

Business/Functional:
• Functional Area Coordinator (FAC): Knows the data and how it is used; understands
the functional uses and potential uses for the data
• Business Analyst: Defines high-level business case, refining to detailed data elements
and business rules; writes test queries and refines with users; Coordinates metadata
definition and publishing
• Communications Specialist: Creates messages to users regarding availability and
usability guidelines

Data/Technical:
•
•
•
•
•

Data Designer: Determines data structures, sourcing and relationships
Data Architect: Reviews for consistency with design standards and quality
ETL Developer: Develops ETL and validation scripts
Business Intelligence Specialist: Develops application layer
Technical Architect: Reviews for optimization within technical environment
Coordination and Support:
• Project Manager: Plans and manages work, issues and risks
• Implementation Coordinator: Coordinates environments and migrations; works with
Project Manager to transition to production
37
© 2005, The Board of Trustees of the University of Illinois
Gaining Commitment from Resource Owners
“Matrix Managed” Projects – The Good, The Bad & The Ugly
• Resources supervised by an external manager who is responsible for work quality
• Work is assigned by a Project Manager for a specific project with a view of the project
goals only
• Resources often assigned to more than one project, causing conflicting priorities and
animosity between managers and staff
Planning and Communication is the Key to Success
• Project Manager plans work assignments and gains buy-in from external manager,
reviewing periodically, not just once
• Work quality issues are communicated to external manager with impact statement to
help the external manager determine what action to take
• Cross-project assignments must be monitored on a resource plan, which ideally
includes other, ongoing work as well (ex., customer support responsibilities)
38
© 2005, The Board of Trustees of the University of Illinois
Resource Estimates are Always Subject to Revision
 Start with a planning estimate based on your typical history
• For example, on the average, it takes x number of hours to develop an ETL mapping
 Adjust the planning estimate based on complexity factors
• Clearly defined scope vs. Planning exercises involving many people
• Requirements are clear across user groups vs. Multiple high-level requirement
groupings
• Small, knowledgeable group of users involved vs. Many users with different jobs and
priorities
• Data relationships clear vs. Multiple sources or subjects not previously related
• Modification of existing structures vs. New structures
• Minimal testing required vs. Full cycle with full regression testing
• Modification to existing training vs. New training materials
• Modification to existing metadata vs. Detailed new definitions, model and STT maps
to be published
 When more detail is known – REVISE!!!
39
© 2005, The Board of Trustees of the University of Illinois
Use a Resource Plan to Track Assignments by Person
 Use the resource feature in MS Project (or whatever tool you are using)
to assign resources to tasks – sweating the details is well worth the
effort
• Don’t assign work to a role, unless it is a temporary placeholder
• Estimate both hours and duration reasonably
• Remember all entries are estimates until the work is done
 Update progress on tasks weekly
 Review resource plans every 1-2 weeks with team members and with
resource owners, focusing on a 4-6 week window
 Focus on remaining work – Estimate to Complete (ETC) is the most
useful information
 Use a Resource Pool if you are managing resources across multiple
projects
 Put the day-to-day work in a project plan so that the real demands on
time can be determined
40
© 2005, The Board of Trustees of the University of Illinois







Objectives
Background
Methodology
Resource Management
Project Reporting
Lessons Learned
Summary
41
© 2005, The Board of Trustees of the University of Illinois
Project Reporting
• Must Be Clear
• Sample of Project Documents
–
–
–
–
Integrated Project Deliverable Status
Project Work Investment Distribution
Implementation Timeline
Issues and Risks Summary
42
© 2005, The Board of Trustees of the University of Illinois
Project Reporting Must Be Clear – Challenge is in Rollup
• Focus on the key points that management needs
to know
–
–
–
–
–
Integrated Project Deliverables Status
Project Work Investment Distribution
Implementation Timeline (Short-Term)
Issues and Risk Summary
Scope Overview
43
© 2005, The Board of Trustees of the University of Illinois
Integrated Project Deliverable Status
This report shows the deliverables (major work products) completed for the project, or in
process at the time of the report. Scheduled start and finish dates are shown for each
deliverable. Work effort, in hours, is indicated for both the total work invested in each
deliverable, and the remaining work for deliverables in process. The percent of work complete
for each deliverable shows the progress to date.
ID
1
2
28
33
44
56
63
69
1 35
1 63
1 68
1 69
2 58
2
1
73
1 99
2 83
2 86
2 97
3 10
3 31
3 67
4 32
3
St art
Mon 3/31/03
W ed 1 /2 1/04
W ed 2 /4 /0 4
Mo n 3/22 /0 4
W ed 2 /4 /0 4
Mo n 4/12 /0 4
Mo n 4/12 /0 4
W ed 5 /1 2/04
Mo n 4/12 /0 4
Mo n 5/24 /0 4
Mo n 3/31 /0 3
T hu 5 /2 7/04
Mo n 3/31 /0 3
Mon 12/30/02
T ue 4 /1 /0 3
Mo n 3/24 /0 3
W ed 9 /1 0/03
Mo n 2/2/04
Mo n 2/2/04
Mo n 2/9/04
Mo n 3/22 /0 4
T hu 6 /3 /0 4
Mo n 3/1/04
T hu 4 /1 /0 4
Thu 4/1/04
F in is h
F ri 4/9/04
F ri 7/23 /0 4
T ue 3 /2 3/04
F ri 4/9/04
W ed 6 /1 6/04
F ri 6/11 /0 4
F ri 5/28 /0 4
F ri 7/23 /0 4
Mo n 5/24 /0 4
F ri 10 /1 5/04
Mo n 6/28 /0 4
F ri 7/2/04
P3. D e sign
P4. C o ns t ruc t io n
D 4 .2 Phy sica l En viro nme nt
D 4 .3 U n iv ers e D es ign Spe cif ic at ion s
D 4 .5 ETL B u ild
D 4 .4 U n iv ers e B uilds
D 4 .5 Sys t em Te st in g
D 4 .6 Sec urit y Imp le me nt a t io n
D 4 .7 Impleme nt a t io n Prep ara t io n
P.5 Impleme nt a t io n
D 5 .1 Int e gra t io n Te s t in g
D 5 .4 F in al F ix es & A p pwo rx se t u p & Ot he r
Fri 12/10/04 Student Records
Mo n 10 /2 0/03
T hu 3 /4 /0 4
F ri 7/23 /0 4
F ri 9/17 /0 4
T ue 2 /3 /0 4
T ue 5 /1 8/04
W ed 6 /2 /0 4
Mo n 6/28 /0 4
T ue 8 /1 7/04
T ue 7 /6 /0 4
P1. W ork Pla nn in g
P2. A n alys is
P3. D e sign
P4. C o ns t ruc t io n
D 4 .2 Phy sica l En viro nme nt B uild
D 4 .3 ETL B u ild
D 4 .4 U n iv ers e B uilds
D 4 .6 Sys t em Te st in g
D 4 .7 Sec urit y Imp le me nt a t io n
D 4 .1 0 C on st ru ct io n Wra p up
Mon 3/14/05 HR Finance
13
14
15
17
19
21
27
28
4
T hu 4 /1 /0 4
Mo n 4/12 /0 4
Mo n 4/12 /0 4
Mo n 4/19 /0 4
F ri 5/14 /0 4
Mo n 4/19 /0 4
Mo n 5/17 /0 4
Mo n 5/17 /0 4
W ed 9 /1 /0 4
T ue 7 /6 /0 4
F ri 4/16 /0 4
W ed 4 /2 8/04
T ue 5 /2 5/04
F ri 5/14 /0 4
F ri 7/23 /0 4
T ue 7 /6 /0 4
Mon 1/5/04
Thu 9/16/04
2
32
33
34
37
45
70
71
77
83
85
91
92
95
1 02
6
Mo n 1/5/04
W ed 2 /1 8/04
W ed 2 /1 8/04
T hu 2 /2 6/04
W ed 2 /1 8/04
W ed 2 /2 5/04
W ed 2 /2 5/04
Mo n 3/29 /0 4
T ue 3 /2 /0 4
Mo n 4/19 /0 4
W ed 2 /2 5/04
T ue 4 /2 7/04
T ue 4 /2 7/04
W ed 5 /1 2/04
Mo n 5/24 /0 4
T ue 2 /2 4/04
W ed 9 /1 5/04
W ed 9 /1 5/04
F ri 3/12 /0 4
T hu 3 /2 5/04
W ed 9 /1 5/04
F ri 8/27 /0 4
T hu 5 /2 0/04
T hu 3 /1 8/04
W ed 4 /2 1/04
T ue 6 /1 /0 4
F ri 8/27 /0 4
T ue 5 /2 5/04
T hu 6 /2 4/04
F ri 8/27 /0 4
1 02
1 03
1 04
1 07
Mo n 6/7/04
Mo n 6/7/04
Mo n 6/7/04
Mo n 6/14 /0 4
Mon 4/5/04
D e live rab le N a me
Fri 10/15/04 Registration Census
S1. ED W B ud ge t T a bles
P2. A n alys is /F un ct io na l D es ig n ( ED W B ud ge t T ab le s)
D 2 .1 U s er Pro f ile
D 2 .2 Int e rview R ec o rds
D 2 .3 R e qu ire men t s Ma t rix
D 2 .4 ED W Pro t ot y p e
P3. T ec hn ic al D e sig n ( ED W B u dg et T ab le s)
D 3 .1 B a se line D a t a Mo de l ( Lo gica l/Phy sica l)
Standard Reports
P1. W ork Pla nn in g
P2. St an da rd R ep o rt Env iro nme nt
D 2 .1 St an da rd R ep o rt Pub lish in g Pro c ed ure s
SD 2.1.1 Mod el Serv ice L ev el A g ree me nt
SD 2.1.2 Pro ce ss es an d Pro ce du res
SD 2.1.3 C ommu nic at ion s/Tra in in g/D e mo Plan ning
D 2 .2 St an da rd R ep o rt Inv en t ory W e b A pp lica t io n
SD 2.2.1 D es ig n Sp e cif ic at ion f o r R e p ort In ve nt o ry We b A p plic at ion
SD 2.2.2 Pilo t R e po rt In ve nt o ry We b A p plic at ion
SD 2.2.3 In t eg rat ion R eq uireme nt s w it h Exist ing A p plic a t io ns
SD 2.2.4 Pre pa re Te c hn ic al Env iro nm en t
SD 2.2.5 D ev elop R ep ort In ve nt o ry W eb A p plic at ion
SD 2.2.5.1 D ev elop D at a ba se
SD 2.2.5.2 D ev elop We b In t erf a ce - St ag e 1
SD 2.2.5.3 D ev elop We b In t erf a ce - St ag e 2
Fri 7/1/05 Data Integrity - ETL
F ri 9/10 /0 4
F ri 7/23 /0 4
F ri 6/11 /0 4
F ri 6/18 /0 4
S2. STT & ET L St a n da rdiza t io n & R e v ie w Proc es s
P2. A n alys is /F un ct io na l D es ig n
D 2 .1 D I - D ef ine St a nd ard STT Spe cif ica t io n Pro ce ss
D 2 .2 D I - D ef ine St a nd ard STT D e ve lop men t C h ec klis t
© 2005, The Board of Trustees of the University of Illinois
% Wo rk C omp le t e
T ot a l Wo rk
R e maining W ork
50%
1,704.25 hrs
844 hrs
1 00 %
8 9%
1 00 %
1 00 %
1 00 %
1 00 %
1 00 %
5 6%
1 00 %
2 4%
8 4%
4 4%
6 7.35 h rs
6 20 .8 7 hrs
2 4.5 hrs
1 0 hrs
1 62 .7 5 hrs
4 1.67 h rs
1 67 .8 8 hrs
1 50 .2 h rs
0 .8 h rs
1 ,0 16 .0 3 hrs
2 13 .1 8 hrs
1 36 .1 h rs
0 h rs
6 6.8 hrs
0 h rs
0 h rs
0 h rs
0 h rs
0 h rs
6 6.8 hrs
0 h rs
7 77 .2 h rs
3 4.17 h rs
7 6.3 hrs
57%
5,158.5 hrs
2,236.32 hrs
1 00 %
1 00 %
1 00 %
6 9%
1 00 %
1 00 %
1 00 %
5 6%
4 8%
4%
5 84 .8 8 hrs
6 05 .5 3 hrs
9 69 .0 3 hrs
1 ,0 99 .3 3 hrs
1 0 hrs
4 43 .0 3 hrs
8 5.43 h rs
6 9.8 hrs
3 38 .1 h rs
5 1 hrs
0 h rs
0 h rs
0 h rs
3 36 .6 h rs
0 h rs
0 h rs
0 h rs
3 0.5 hrs
1 75 .1 h rs
4 9 hrs
15%
2,547.2 hrs
2,169.7 hrs
3 5%
8 5%
1 00 %
1 00 %
8 0%
1 00 %
3 9%
7 7%
8 18 .6 7 hrs
1 88 h rs
2 0 hrs
3 5 hrs
3 0 hrs
8 0 hrs
3 16 .6 7 hrs
1 62 .6 7 hrs
5 34 .6 7 hrs
2 9 hrs
0 h rs
0 h rs
6 h rs
0 h rs
1 91 .6 7 hrs
3 7.67 h rs
68%
1,340.33 hrs
432.2 hrs
1 00 %
6 2%
7 6%
1 00 %
1 00 %
1 6%
5 5%
1 00 %
1 00 %
1 00 %
1 00 %
3 7%
1 00 %
8 2%
1 5%
1 91 h rs
1 ,1 49 .3 3 hrs
4 56 .2 3 hrs
1 15 .1 2 hrs
2 13 .0 2 hrs
1 28 .0 8 hrs
6 72 .2 7 hrs
1 46 .6 8 hrs
8 .5 7 hrs
7 .9 2 hrs
2 6 hrs
4 83 .1 h rs
2 3.08 h rs
1 32 .0 2 hrs
3 28 h rs
0 h rs
4 32 .2 h rs
1 08 h rs
0 h rs
0 h rs
1 08 h rs
3 04 .3 3 hrs
0 h rs
0 h rs
0 h rs
0 h rs
3 04 .3 3 hrs
0 h rs
2 4.33 h rs
2 80 h rs
2%
5,867.4 hrs
5,758.05 hrs
1 1%
3 6%
1 00 %
1 00 %
8 2 hrs
2 1 hrs
3 h rs
3 h rs
7 3 hrs
1 3.5 hrs
0 h rs
0 h rs
44
Project Work Investment Distribution
This chart shows how work hours are invested across the active projects at the time of the
report. Total Work Investment is the planned hours of work required to complete the project.
Work Completed is the work already invested in the project, and Work Remaining is the
remaining hours expected to be invested in each project.
45
© 2005, The Board of Trustees of the University of Illinois
Implementation Timeline
The timeline shows the major events scheduled as part of the
implementation phase of the projects, for a three month window.
46
© 2005, The Board of Trustees of the University of Illinois
Issues and Risks Summary
These are issues or risks that impact more than one project, or have been deemed by the
project manager to be of high impact or high likelihood to occur.
Risk or Issue
Project(s)
Description
Impact
Mitigation
ETL Resource
Shortage
HR Finance
and Data
Integrity-ETL
Two ETL
resources have
left the
organization in the
past month. New
staff will need time
to be trained
before
deployment to
project work.
Source data in
Banner may not
have the detail
necessary to
provide a clean
integration
between HR and
Finance data.
HR Finance Stage 1
Deployment has been
postponed for one month.
Postponement of HR Finance
Stage 1 is not critical, since final
budget numbers from Banner
Salary Planner will not be available
until at least 9/1/04.
Data Integration
Between HR and
Finance
HR Finance
Replication of
Production
Environment for
Testing of Census
Dates
Registration
Census
Census dates are
built on timing and
batch parameters
that can not be
replicated in a test
environment, so
triggers cannot be
fully tested prior to
go live.
Data Integrity – ETL
project is on hold until a
full time ETL resource can
be obtained.
May not be able to deliver
a cleanly integrated HR to
Finance solution at the
levels of aggregation
users will want.
Census will not truly be
tested until first nine
census dates have
actually occurred.
Data Integrity –ETL should be able
to make significant progress once a
resource is assigned full-time.
Perform requirements analysis for
stage 2 early, then assess HR and
Finance data for feasibility
Contriving dates for test pulls as
well as setting up "fake" batch
trigger runs
47
© 2005, The Board of Trustees of the University of Illinois







Objectives
Background
Methodology
Resource Management
Project Reporting
Lessons Learned
Summary
48
© 2005, The Board of Trustees of the University of Illinois
Lessons Learned
A. Management
B. Partners and Procedures
C. Cultural
D. Reporting
E. Post Go-Live
F. If We Could Start Over, We Would….
49
© 2005, The Board of Trustees of the University of Illinois
A. Management
 To be most successful, matrix management should be accompanied by
documentation, particularly when everyone is busy.
 Staffing agreements for assigned staff should be negotiated up front,
including personnel issues, then revisited as the situation evolves.
 Working partner agreements might need to include some very
practical issues: rumor management, agreements on staff movement
from one group to another, agreeing to share in service opportunities,
etc.
 Project partners must make basic decisions about external
communication, such as that to campus constituencies.
50
© 2005, The Board of Trustees of the University of Illinois
B. Partners and Procedures
 Project partners must understand each others’ project plans (milestones,
terminology, critical path) and project plans must be more than task lists.
 Coordinated project planning must be reinforced by strong team lead
relationships, at multiple levels.
 Project partners must understand each others’ methodology at a high level,
particularly at the outset.
 For all partners: when similar sounding methods are different, e.g.,
requirements or modeling, there must be an understanding of those
differences.
 External technical partners must have a chance to hear and understand
internal approach to technical issues.
 Existing technical procedures may not be documented or formal. Intrusion
into this process may be perceived as problematic.
51
© 2005, The Board of Trustees of the University of Illinois
C. Cultural
 Assumptions must be aired regularly and relentlessly.
 Understand the culture and expectations of each partner. Where there
is miscommunication about culture or expectations, a lot of time is
wasted in seemingly small matters.
 Shared celebrations and congratulations help. Frequent interaction
solving problems together helps more.
52
© 2005, The Board of Trustees of the University of Illinois
D. Reporting
 Report development has a life cycle of its own, with requirements, sign
offs, negotiations, assignment of roles, metadata, deployment issues, etc.
that are in addition to those involved in deploying an ERP or a Data
Warehouse
 In the press of building a Data Warehouse and/or implementing an ERP,
it’s easy to subsume this life cycle under other tasks
 Inadequate attention to reporting means you don’t fully appreciate its
importance to users and are putting project at risk
 Best to have an owner who is accountable to users for report success
53
© 2005, The Board of Trustees of the University of Illinois
E. Post Go-Live
 Designate a production team that can take over the maintenance of the
product after 60 days. Shared development and production staff on a
long-term basis does not work.
 It is important to show the users that you designed a flexible environment
to accommodate their changing needs.
 There are many guides for development but few for 60 days after.
54
© 2005, The Board of Trustees of the University of Illinois
F. If We Could Start Over, We Would….
 Have organized the department the same way but designate staff as
development or production
 Still be led by and focus on business need
 Still create a flexible architecture as a baseline
 Negotiated interlocking timelines between the Data Warehousing and ERP
efforts. Preferably offset warehouse release from ERP release to allow
breathing room for the ERP to settle
 Focus on operational reporting for the initial phase (concurrent with ERP
deployment)
 Have a recurring IT contract for staffing from the beginning
55
© 2005, The Board of Trustees of the University of Illinois
And…
 We would have formally defined our relationship with ERP partners,
rather than from the bottom up.
 In the early days it might have been good to do a small project using
legacy data. This could have been used to formalize procedures and
work out internal relationships and issues.
 We would have taken a more critical look at how technical skills of staff
like DBAs and modelers are different in a warehouse environment than in
a general IT environment. We could have helped the IT staff to a
smoother transition.
 We would have secured critical staff early in the process (Data Architect
and Technical Architect)
 We would have recommended greater recognition of the report
development process as an independent effort, possibly with separate
leadership.
56
© 2005, The Board of Trustees of the University of Illinois






Objectives
Background
Methodology
Resource Management
Techniques, Tools and Lessons
Summary
57
© 2005, The Board of Trustees of the University of Illinois
Questions? Discussion?
Thank You!
For more information visit our site at: http://www.ds.uillinois.edu
58
© 2005, The Board of Trustees of the University of Illinois