Project Methodology for Building and Enhancing a University Data Warehouse

1. 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

2. Objectives • UI Background • Methodology • Resource Management • Project Reporting • Lessons Learned • Summary

3. Objectives Learn ways to….. • Reduce project time by using a methodology that works for DW • Increase resource efficiency without sacrificing collaboration • Provide high-value outcomes to users

4. “You can have it good … You can have it fast … You can have it cheap ... Pick any two.” Red Adair SCOPE Meets User Needs – Creates Value High Level of Communication Users are Equipped Minimal Errors: “Production Ready” Good Fast Cheap TIMELINE Meet Critical Dates Provide Early Access for Report Developers Other Key Dates BUDGET Efficient Use of Resources Cost of Producing Deliverables Productivity

5. 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 …..

6. Objectives • UI Background • Methodology • Resource Management • Project Reporting • Lessons Learned • Summary

7. UI Background • History, Mission and Focus of the University of Illinois Data Warehouse and Decision Support • Staffing • Products, Services and Uses • Post ERP Organization

8. 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

9. 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.

10. 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

11. Staff Levels Peaked During 5-Year ERP Related Project

12. Finance Human Resources/ Payroll Biographic Demographic Financial Aid Records &Registration Recruiting &Admissions Catalog & Schedule Today, the Data Warehouse is Established and Supported “Library” of all data Special-purpose “data marts” UndergraduateAdmissions 699 tables 15,022 fields 145 GB total database size 470 MB daily increase GraduateAdmissions Registration Census RecruitingContacts Financial Awards

13. Services are User-Centric Campus practice labs Daily question answering service Nightly integration of new data Self-service data education Data documentation(metadata) Regular schedule ofdata enhancements Standard Report Directory Query Clearinghouse Listservannouncements Query tool training Monthly user group sessions Data views thatmake connections

14. 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 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 • Class rosters • Legislative reports • Instructor lists • Insurance eligibility • Applicant counts and profiles • Instructor list system • Activity reporting • Graduate admissions information Items on this slide are samples of current uses and planned uses as reported by users.

15. Hierarchy of Needs Value Add Traditionally Analytical (Fewer #s) Traditionally Operational (Larger #s)

16. Decision Support Organization Structure for FY 06 Decision Support’s post-project organization combines business, data and technical positions in an integrated structure that facilitates close coordination among staff.

17. Objectives • UI Background • Methodology • Resource Management • Project Reporting • Lessons Learned • Summary

18. 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

19. 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? Leave Adjustment Transaction Leave balances need to be monitored and adjusted to reflect leave accrued and/or taken for the department vs. • Staff Retention • Department level data incorporating changes over time • 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 • Department level data for current period only • Leave balances for active staff only in the current period • Report output for monitoring • Application form for adjustments vs. Analysis Requirements Are Broader Than Applications HR Analysis HR Transaction What is the task? What is needed?

20. 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.

21. 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.

22. Work Planning Business Requirements Functional Design Technical Design Development Quality Assurance Deploy to Users Specified Iterative Specified Specified Iterative Specified Business Expertise Business Expertise Data/Technical Expertise Metadata Development Implementation Coordination Data Warehouse Development Process Involves Users with both Business Staff and Data/Technical Staff Begins with user’s business priorities Business & Data Experts work with users to create design Business & Data Experts work with users to test design

23. 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 foriterative 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.

24. Work Planning Business Requirements Functional Design Technical Design Development Quality Assurance Deploy to Users Specified Iterative Specified Specified Iterative Specified Business Expertise Business Expertise Data/Technical Expertise Metadata Development Implementation Coordination 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 Created working prototype of data model and users provided feedback through several iterations User-developed queries from functional design step became test cases in for final data structures and universe

25. 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

26. Data Project Process: Multiple Stages of 90-120 Day Cycles Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Revise WP Revise WP Revise WP Revise WP A B 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 CLOSE Stage3: Additional Functionality Review Points A = Strategic Scope Review 1 = Design Scope Review 3 = QA2: Technical Review B = Statement of Work 2 = QA1: Technical Review 4 = Go/No-Go Decision

27. Data Project Short Cycle Methodology: 90-120 Day Cycle per Stage (High Level) Repeated Each Stage Once Per Project Feasibility Analysis Work Planning Requirements& Functional Design Technical Design Development QA & Delivery Business Case Project Statement Of Work Prototype Requirements +Data Design Plan • Developed • Code Testing Strategic Scope Review Statement of Work Approved Design Scope Review QA1:Technical Review* QA2:Technical Review** Go/No-Go Decision • Project • Initiation Project Plan Functional Specification Technical Specification Code Modifications Rollout Review Point Output Input *QA1: Does the technical design meet the requirements? **QA2: Does the developed code meet the technical specification?

28. Data Project Short Cycle Methodology: 90-120 Day Cycle per Stage (Detail) Once Per Project Repeated Each Stage Feasibility Analysis Work Planning Requirements & Functional Design Technical Design Development QA & Delivery Business Case FAC/RMC and Stakeholders • Project SOW • Deliverables • Timeframe • Work Estimate • Project Mgr • Prototype • Business Needs • Data Behavior • Iterations • DA/DM/RA/FAC • Design Plan • Logical/Physical • Data Model • Design Specification • DM/DA/TA • Developed Code • ETL with Validation • Universe (unit tested) • ETL/BOS • Testing • System • Integration • UAT • Regression • FAC/RA/DA/PM Strategic Scope Review for fit & commitment Director Statement Of Work Approved BAM, DAM, TAM Project Coordinator Design Scope Review BAM, DAM, Services Mgr & Stakeholders QA1:Technical Review BAM, DAM, TAM QA2:Technical Review BAM, DAM, TAM Go/No-Go Decision DS Management & Stakeholders • Project Initiation • Schedule • Assign Project Mgr • Identify Resources • Project • Coordinator • Project Plan • Deliverables, Tasks, & Subtasks • Work Assignments • Milestones • Project Mgr • Functional • Specification • Logical Data Model • Universe Mock-Up • RA/DA/DM • Technical • Specification • Source to Target Maps • Universe Specification DA/BOS • Code Modifications • Revised Specs • ETL with Validation • Universe (unit tested) • DA/ETL/BOS • Rollout • Training • Post-Deployment QA • FAC/RA/DA/PM FAC – Functional Area Coordinator DA – Data Analyst TAM – Technical Architecture Manager RMC – Requirements & Metadata Coordinator DM – Data Modeler TA – Technical Analyst BAM – Business Architecture Manager ETL – ETL Developer DAM – Data Architecture Manager BOS – Bus Obj Specialist Review Point Output Input

29. 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

30. Implement the Methodology with Key Project Documents • Statement of Work • Business Case (Need, Audience, Outcome) • High-Level Timeline • Resources • Risks • Project Plan • Phases • Deliverables • Tasks • Resources • Issue Log • Description • Impact • Actions • Owners and Resolution Date • Communication Plan • Groups and Individuals • Type of Communication (Presentation, Review, etc.) • Frequency and/or Dates • Contacts • Owners

31. 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

32. 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

33. UI Sample Structure for Collaboration Decision Support Build Databases and Plan Focus Groups to Determine # of Groups and # of Participants; Design and confirm Metadata; Resolve Data Issues; Validate Data; Conduct Focus Groups, Collect Requirements; Design and Confirm Physical Model(s) and Create Business Objects Test and Train Logical Model(s) Data Staging universe ; Unit Test; Plan Training Business Reqs Work Plan Rev. System Test User Test Detailed Analysis Work Analysis Design Construction Integration Testing Planning Analysis Implementation DS-ERP joint meetings Weekly status meetings to coordinate test phases & test data availability Collaborate to Plan: Confirm Banner data model; Informal Resolve Test plan development & phase integration SCT training for DS ? Overview of Business Objects & Contact Issues GO LIVE Production quality data available to pull from Banner environment ERPDS and BO ? EDW; Between DS Overview Consensus on DW Reports; and ERP team DS CRP attendance ? Report walkthrough - ERP reports ERP team participation ? sourced from DS Integration Testing End User Testing in DS Focus Groups Build and Unit Test Roll Design Components System Testing out Design Plan Component Design Plan System Testing Mock Conversions, Data Purifications Prepare and Conduct CRPs Phase Impl. Work Planning Develop Conversion Plan Org'n phase Implementation Design Design, Build, and Unit Test Iterative full volume mock conversions and dress rehearsals ; catch ERP sends file Finalize "TO BE" Interfaces, ? Document "AS IS" process and fix data exceptions locations to process Conversions, ? Plan "To Be" process DS: Business Objects ? UI2 informs DS about Universe, Iteratively execute scripted test cycles, sub cycles and business Current ? Banner mods, ? Banner Reports ? processes using prepared data elements Reports Security roles, ? Interfaces ? Privacy concerns, ? Banner ? Conversion plan ? Tables Conversion of History plan, ? Current ? Banner interfaces ? systems Completion of report ? to convert functional design specification ERP sends DS ERP Teams Report specifications, ? Reporting Needs Inventory ?

34. Objectives • UI Background • Methodology • Resource Management • Project Reporting • Lessons Learned • Summary

35. Resource Management • Project roles • Gaining ongoing commitment from owners of shared resources • Estimation of resource time • Tracking resource time

36. Functional Design Technical Design • What specific data is needed from the source system? • How does it need to be related? What is the technical road map from the source to the EDW? What definitions and business rules apply? Project Roles Require a Mix of Business and Technical Implementation Coordination PC IC Iterative Iterative Work Planning Business Requirements Development Quality Assurance Deploy to Users Roles BA FAC CS FAC BA FAC DD BA DD DA ETL BIS DD BA Ensures that development activities are driven by needs of users TA • What is the business problem and what data solution is needed? • Build code to Extract, Transform and Load data • Build Business Objects Universe or Application • 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 Metadata BA DD BA Business Analyst Data Designer ETL Developer Project Coordinator Functional Area Coordinator Data Architect Business Intel. Spec. Technical Analyst Implementation Coordinator Communication Specialist DD ETL PC IC FAC DA BIS TA CS

37. 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

38. 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)

39. 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!!!

40. 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

41. Objectives • Background • Methodology • Resource Management • Project Reporting • Lessons Learned • Summary

42. Project Reporting • Must Be Clear • Sample of Project Documents • Integrated Project Deliverable Status • Project Work Investment Distribution • Implementation Timeline • Issues and Risks Summary

43. 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

44. 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.

45. 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.

46. Implementation Timeline The timeline shows the major events scheduled as part of the implementation phase of the projects, for a three month window.

47. 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.

48. Objectives • Background • Methodology • Resource Management • Project Reporting • Lessons Learned • Summary

49. Lessons Learned A. Management B. Partners and Procedures C. Cultural D. Reporting E. Post Go-Live F. If We Could Start Over, We Would….

50. 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.