Assessment of University of Washington Data Warehouse

1. Assessment of University of WashingtonData Warehouse July 15, 2010 Brian Palmer and Laura Reeves CONNECT, The Knowledge Network

2. Agenda • About the Assessment – Scope and Process • Current State of the EDW • Future Vision for the EDW • Moving the EDW Forward • Key Recommendations and Next Steps • Summary

3. About the Assessment

4. Assessment Scope The scope of this effort is to provide the University with an Enterprise Data Warehouse (EDW) Assessment Report to address the following: • Validate the strategy and provide additional recommendations on all aspects of data architecture and information delivery. • Assess current technologies in use and provide recommendations for improvement. • Evaluate the overall program approach including organization and support models, staffing levels, operations and project delivery processes. • Assess program risks to the existing strategy, architecture and technologies. • Assess progress made since the last review of the BI program. • Evaluate proprietary data access control system. • Evaluate the iStrategy feasibility study. The scope of this assessment did not include: • Scoping and costing of specific projects (OPB, RADC, etc.) • Identification of new opportunities or quantification of associated business value.

5. Assessment Process To achieve these goals, CONNECT has gathered insight for this report by: • Interviewing key stakeholders, users and team members of the current data warehouse to determine the business value they are receiving from today’s EDW, and to ascertain an understanding of their future Business Intelligence needs. • Evaluating current state data solutions including; technical environments, data architecture, and processes in use for the delivery of Business Intelligence • Reviewing documentation to understand the history, tactical plans, projects and long term strategy for Information Management placed in the context of the broader business and technical strategies of the university. • Interviewing Business Intelligence leaders from Peer Institutions to provide additional context to the assessment and associated recommendations.

6. The Current State of the EDW

7. Progress and Notable Accomplishments • Staffing • Have assembled a group of quality individuals able to provide a strong foundation for the team needed to move the EDW forward. • EDW Environment • Providing a stable and reliable environment where source system data can be accessed for operational reporting. • Deploying databases which are segregated, maintained, and being upgraded to SQL Server 2008 using new features such as partitioning. • Consolidated reporting technology onto the Microsoft product line. • Providing role-based security in accordance with federal and state data access control and privacy stipulations. • EDW data extracts feeding other data stores supporting additional reporting and analysis. • Progressing on plans for evolving the data architecture of the EDW.

8. Progress and Notable Accomplishments • Reporting • Highly disciplined process to create standard reports • Currently over 1,700 users accessing more than 150 standard reports • Created two financial analytical cubes with critical subsets of data to support flexible access and analysis. • Business Metadata • Metadata concepts are clearly understood as evidenced by the initial work and accountabilities of the Data Management Committee. •  Support • DSS web site to communicate critical details about the EDW with links to DMC web pages with data definitions and security roles. • The site is one of the best that we have seen implemented. • Actively engaged with user issues through the Reporting Services SIG.

9. EDW Business Value to Date Consumers of EDW sourced data provided many positive insights on the value proposition of the existing EDW in terms of the data’s ability to support improved quality and efficiency of reporting for their business areas. Here are several examples that highlight this point: • Paper report replacementresulting in improved operational efficiency, making data available for use sooner, and reducing mainframe reporting overhead. Significant impact identified in the financial management decision support area. • Data mart / cube created to support financial analysis to review what has been identified as taxable (use/sales tax). This resulted in the identification of the potential refund of millions of dollars, per Purchasing and Accounting. • Overcoming operational systems shortcomings through structured reporting. Creating reports using a disciplined processes to ensure report quality, and providing them to users in a well documented reports library. Academic Advisement for Mathematics and Computer Science are examples of areas that have benefited from the EDW structured reporting.

10. Business Perception • While significant progress has been made, the current data warehouse capabilities are not able to meet user expectations. • The biggest challenges we heard from the business users of the EDW stated as simply as possible were: • We are unable to view information across the organization (i.e. lack of integrated data). • It is too hard to use. • It takes too long to get things done. • The source application systems are out-dated. • De-centralized organization and culture make it difficult to know who has what data, and how to get it. • Use of the data warehouse has been for financial and operational reporting rather than analysis. • While some changes are needed from an IT perspective, it is also important for the institution as a whole to: • Begin to shift towards using the DW to drive institutional effectiveness • Take a more analytical approach to decision making

11. Current Business Intelligence Challenge • Highly de-centralized organization and culture creates challenges. • Fragmented data environment with many “stove pipe” data marts. • Lack of integrated data and limited Business Intelligence tool capabilities make it time consuming and technically challenging to get information out. • Gaps exist in foundational components of organization and process. • Limited data definitions and inconsistent hierarchies impact ability to view across the organization. • EDW requires the addition of other data sources to provide a more complete view of the organization. • Fragmented support to users coming from 5 separate decision support teams outside of the EDW. • Campus systems limitation have resulted in a focus on data warehousing as an operational reporting environment, as opposed to focusing on analyzing institutional effectiveness.

12. Current EDW Data Architecture Source Systems Staging Layer Transform & Warehouse User Access Extract Raw Data Subset & Summarize Minimal Transfor-mations ODS (Current Data) HR-Payroll Systems HR Data HR Data Finance FR Data Mart Financial Systems Financial Data Financial Data GL Data Mart Academics Student Admin Sys Academic Data Academic Data Census Day Mart Master Data HR Data Mart Structured Reports Structured Reports Structured Reports UWSDB (Student ) Current Structures Departmental and External Data Stores

13. The Future Vision for the EDW

14. Future Vision of Business Intelligence Criteria for the future EDW data architecture to provide a credible, consistent and reliable data environment: • Integrates data from different source systems as well as departmental data. • Supports different types of historical data including transaction detail, static snapshots (such as census day), etc. • Continues to support the ability for operational inquiry and reporting. • Provides mechanism to integrate departmental data w/core institutional data. • Continues to provide appropriate security in the environment. • Data organized to facilitate ease of use and good query performance. • Is cost effective to use and to enhance (extensible). • Supports timely development of new reports. • Provides users with other flexible methods to access information with an emphasis on “self-service” and ad-hoc analytics. • Employs data quality management practices. • Provides transparency through business Metadata content and presentation. • Has high performing incremental load of source data.

15. EDW Data Architecture Vision Source Systems Staging Layer Integration Layer Integrated Presentation Layer User Access Business IntelligenceApplications ExtractRawData Integrate & Cleanse Transform for User Access HR Data Financial Data Academic Data Other Data HR, Financial, Academic, Facilities, Advancement, Other… HR-Payroll Data HR-Payroll Systems Departmental Data Financial Systems Financial Data Integrated Departmental Data Student Admin Sys Student Data Analytics Dashboards Facilities Data Facilities Serv. Sys Departmental Data Entry Analytics Research Admin Sys Research Adm Data Scorecards Archive Production Data Extracts Advance-ment Sys Advance-ment Data Dept Input Other Sys Data Other Systems Structured Reports Structured Reports ODS (Current Data) Current Structures Departmental and External Data Stores New Structures

16. Business Value: Investing in the Future Vision Many opportunities exist to provide added value to the university: Gaining Greater Control Over Information Assets • The Institution has an obligation to the community and the individuals it serves to protect the information accumulated in the performance of its mission. The EDW can play a significant role in demonstrating the Institution’s commitment to using due diligence in its effort to maintain control of information assets. Improving Business Process through Data Integration & Analytics • Good decision making depends on the availability and use of common, consistent information. Investing in a well designed integrated data environment in the EDW accompanied by strong business metadata could significantly enhance the role of the EDW in moving the organization towards proactive decision making. Managing Costs • Through the Return on Investment associated with EDW related business process improvement initiatives. • Through the elimination of costs associated with the current highly decentralized and redundant information environment.

17. Moving The EDW Forward

18. Short-Term Approach to Business Intelligence • Incorporate recommendations into the current priority project work as first step towards the future vision architecture. • Leave much of the existing environment in place to continue to support any existing users and reports. • Focus on building out the data structures needed to provide flexible integrated institutional data: • Leverage the data as it is being sourced into the warehouse today, however, develop a new integration layer. • Build and Deploy a new integrated presentation layer based on dimensional data structures using conformed dimensions with appropriate history. • Purchase and deploy a “classical” Business Intelligence user interface to enable end-user self-service reporting and ad-hoc analytics (examples include MS ProClarity and Business Objects).

19. Short Term EDW Data Architecture User Access Source Systems Staging Layer Integration Layer Integrated Presentation Layer Business IntelligenceApplications HR Data Financial Data Academic Data ExtractRawData Transform for User Access Integrate Fin, Academic & Select HR Data HR-Payroll Systems HR Data Finance Transform & Warehouse HR Data ODS (Current Data) Financial Systems Financial Data Dashboards Student Data Select HR Data Financial Data Subset& Summarize Analytics Student Admin Sys Academic Data Scorecards Academic Data Production Data Extracts Archive UWSDB (Student ) Structured Reports Structured Reports HR Data Mart Current EDW Structures Departmental and External Data Stores New EDW Structures

20. People and Process Gaining true value from a data warehouse is more than moving data more efficiently. It is highly dependent upon the people and processes to support the entire environment. • Staffing Recommendations • Add specific roles to manage warehouse specific work including metadata management, testing, data modeling, and end-user support. • Fill these roles with people that have data warehouse experience. • Work Intake and Prioritization Recommendations • Recognize different types of work: Project, Report Development, Management of Access and Security, and Maintenance • Track the volume of requests and effort associated with involved with each type of work. • Develop a business prioritization process to set direction, manage priorities, measure the outcomes. • Allocate resources based upon priorities.

21. People and Process • End-User Support / Client Facing Services • End-user support must be recognized as key factor for wide-spread adoption of the data warehouse. • Support resources can report through business or IT. • Develop tiered support approach to meet the full range of departmental needs. • Address the need to integrate user support services across the five decision support teams outside of the EDW. • Training services should be expanded to include new BI tool functionality, data content, and metadata usage. • Several recommendations have also been made to begin addressing data quality and make refinements to the system development lifecycle.

22. Key Recommendations & Next Steps Our recommendations are targeted towards helping UW address these challenges in a realistic way given the current economic environment. The following high level recommendations summarize the assessment report details: • Business Partnership • Business Leadership is necessary for the EDW program to succeed. Clarify business accountabilities and make the commitments. • Establish practices that ensure priorities, expectations and commitments are clearly communicated throughout the organization. • Business constituents must be advocates for the EDW. • Initial Project and Staffing • Allocate resources at the level required to effectively complete data warehouse development projects: • Immediately acquire incremental resources to ensure successful completion of the OPB/ABB project. (Estimated as an additional 4.5 FTE’s) • Fill the critical roles that are currently absent from the EDW team. • Incorporate business expertise (Business Analysts) into project teams. • Concurrent projects would require additional incremental staff & increase risks. • External resources should mentor UW staff to reduce long term dependence.

23. Key Recommendations & Next Steps (2) • Data Architecture • Make it a priority to document and publish institutional data definitions, often called business metadata. • Leverage the data as it is being sourced into the warehouse today. • Develop a new integration layer to prepare the data for the presentation layer. • Extend the EDW data architecture to support self-service reporting and analysis . Specifically build a presentation layer with dimensional data structures that use conformed dimensions and appropriately handles history. • Re-engineer the beginning layers of the EDW data architecture in conjunction with the replacement of the source application systems.

24. Key Recommendations & Next Steps (3) • Data Delivery • Purchase and deploy a “classical” Business Intelligence user interface for the enablement of end-user self-service reporting and ad-hoc analytics. • Build and deploy a suite of starter business intelligence applications. These should provide flexible, parameter driven reports that can be easily modified on the fly to support analysis and decision making. • Deployment and Support • Sufficient resources must be allocated to focus on support and deployment of business intelligence (Client facing services ) provided in combination from business and IT. • Determine tiered support model and method for delivering coordinated support services. • Employ a work intake and prioritization mechanism.

25. Peer Institution Benchmarking Conducted phone interviews and a survey with the following Peer Institutions: University of Michigan Rensselear Polytechnic Institute (RPI) University of Illinois Seattle University Princeton University University of Michigan Highlights Most comparable peer in terms of size and research DW started in 1994 Replaced core systems starting in 1998 (PeopleSoft) Provide reporting and analysis to a large audience Business Objects has 5,500 users M-Reports has 7,000 users Internally developed report delivery interface for “guided analysis” Large EDW Staff with 28.8 FTEs Metadata Presentation Capabilities (delivered with each project) Next generation DW expansion is on the books and in planning phase.

26. iStrategy Evaluation • iStrategy reviewed the UW environment to assess the feasibility, benefits and challenges of implementing their products (June, 2010) • CONNECT reviewed iStrategy assessment, but did not directly participate. • In general we agree with UW observations based on iStrategy’s extremely candid evaluation. • iStrategy provides strong user interface to facilitate reporting & analysis • iStrategy provides a library of higher education metrics which are likely to require extensive customization. • The iStrategy product line does not support an integrated data model • Core benefit of the iStrategy product line is their integration with third party applications. Since UW is not using these packages, this would not be a benefit. • Need to explore technical direction of their product line – currently using SQL Server 2005 and MS ProClarity

27. University of Michigan - iStrategy Experience University of Michigan implemented several iStrategy products. The critical lessons learned from their experience include: • Biggest benefit was to launch the DW team and expand their skill set into dimensional data structures. • Implemented using the University of Michigan developed interface. End users did not know they were using iStrategy. • Would suggest implementation without customizing to get your feet on the ground. Then customize as appropriate. • Are planning to move away from the iStrategy product with the implementation of their new integrated data warehouse architecture. • The vendor provided excellent guidance and consulting during the initial financial implementation project.

28. Summary / Conclusions

29. EDW Assessment Summary The University of Washington has been investing in the data warehouse environment for a number of years. While there has been progress, there are still many needs that are not being met with the current Enterprise Data Warehouse (EDW). • The current EDW does provide value to the institution. • There is a common and realistic understanding across the university of what the current EDW can do, as well as its limitations. • There is also an appreciation for the commitment, quality and work of the current EDW staff. • The DSS Team understands and acknowledges the challenges with the current EDW and has developed plans to address these issues. • The assessment validates that the plans are fundamentally sound and with the additional refinements suggested should move forward.

30. EDW Assessment Summary The University of Washington is poised to make significant strides in how the data warehouse is used across the institution. Critical factors to achieve this success include: • Business Commitment • Make the financial investment to implement the plans. • Gain executive support to ensure organizational alignment around a clearly defined set of priorities and goals. • Determine organizational roles with responsibility, accountability and resources to carry out the work. • IT Commitment • Execute the plans effectively • Facilitate the process to define roles and work associated • Provide the technical and data environment to empower the University. • Partnership – is crucial to achieving good results.