Enterprise Business Intelligence
and Reporting Solution
Draft Project Funding
Proposal
October 22, 2004
Project Funding Proposal
for
Information Technology
Governance Board
Table Of Contents
Project Sponsor ... Project Team ... Signatures of Acceptance/Approval ... Executive Summary ... Project Description ... Background ... Project Need ...To support Hennepin Results Strategic Management ... Provide all levels of county staff an easy-to-use solution for self-service reports ... Assist with performance measurement reporting ... Create better understanding of and accessibility to the data ... Extend the useful life of the county’s legacy systems ... Workforce planning issues/knowledge transfer ... Future e-government initiatives ... Conclusion ... Project Scope ... Objectives ... Benefits ... Deliverables ... Exclusions ... Assumptions ... Constraints ... Risks ... Critical success factors ...
Time and Schedule Estimates ... Preliminary Cost Estimates ... Next Steps ... 1 1 1 2 3 4 5 5 6 8 9 9 9 10 11 12 12 13 14 14 14 14 15 17 16 17 17
Project Funding Proposal
October 22, 2004Project Sponsor
1Project Team
Signatures of Acceptance/Approval
Richard Johnson, Deputy County Administrator
Technology Steering Committee
Larry Bush, Public Works Pam Haats, Human Resources Don Holmberg, Human Services Ann Houghton, Information Technology Steve Jutila, Information Technology
Karen Marquardt, Office of Budget and Finance Karen Piet, Office of the CIO
Glen Teske, Information Technology
Tom Walkington, Office of Planning and Development Steve Zorn, Human Services
Customer
All county departments
This project is a countywide initiative. Representatives from all departments assisted in requirements gathering and vendor selection.
Richard Johnson Deputy County Administrator [Date]
Toni Jelinek Hennepin County Chief
Executive Summary
The purpose of this project proposal is to request approval to use $1,329,039 of the county’s Information Technology Incentive Fund to purchase and install business intelligence software for gathering, storing, analyzing and providing access to data to help managers and staff make better business decisions, and to provide information for consistent, accurate reporting of results to managers, policy makers and the people of Hennepin County.
What is Business Intelligence?
Business intelligence (BI) is a broad category of applications and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions. BI applications include decision support systems, query and reporting functions, online analytical processing (OLAP), statistical analysis, forecasting and data mining.
(From searchCRM.com)
The cost of installing and using an enterprise-wide business intelligence system is offset by savings from a reduction in the number of separate systems that need to be maintained, redirection of staff resources and movement to a data-driven decision-making culture over time.
The scope of this project is threefold:
Implement an enterprise-wide business intelligence and reporting solution, rolling the initial business solutions out to county employees in a planned way.
Construct the infrastructure necessary to enable consistent, quality reporting from county and county business partner information systems.
Create a proposed roadmap and plan for ongoing development and use of the solution.
1.
2.
The benefits of an enterprise-wide business intelligence system include:
The ability to extract and aggregate information from disparate county systems – especially FARS and the Payroll/Personnel System,
The standardization of data presentation and the compilation of information on a user-friendly, flexible system to enhance quality, consistency and comparisons,
The provision of consistent data views across the organization that can be rolled up into an enterprise-wide view,
The ease of use by county management and staff members,
The ability to align goals across the enterprise, The ability to leverage technology to provide more timely information, and
The ability to leverage the use of legacy systems, thus alleviating some of the pressure to invest in replacements.
The enterprise-wide business intelligence and reporting solution will be phased in over a two-year time period. A project manager, who reports directly to County Administration, will guide a cross-organizational implementation team. The intended long-term result of this project is the creation of a results-based, customer-focused culture leading to improved outcomes for the direct beneficiaries of our services.
Project Funding Proposal
October 22, 2004
Project Description
3
Implementation of an enterprise-wide business intelligence and reporting solution aligns with countywide strategic goals of improving data-driven decision-making and results assessment, redesigning systems and improving systems coordination.1 Data-driven decision-making requires that county executives, managers
and other professionals have the kind of information they need, when they need it. However, in order for county decision-makers to have this information, they need a means to quickly and easily gather and analyze business data with minimal technical assistance.
The purpose of this document is to request approval to use $1,329,039 of the county’s Information
Technology Incentive Fund to move forward with a project to purchase and install a business intelligence and reporting software solution designed to extract and present knowledge from the many and diverse sources of data available to county managers and staff.
1 Hennepin County’s Countywide Strategy Map 2Richard Johnson Easy to Use, Offers Executives and Managers Self-Service Reports Enhanced Report Capabilities for Analysts and Other
Professionals Assistance with Balanced Scorecard and Performance Measurement Tools Business Intelligence and Reporting Tool Solution Data Definition, Transformation and Cleansing
Diverse Data Sources
(Note: Some graphics and other materials used in this document were derived from materials provided by vendors.) The purpose of this project is,
“to provide managers and other professionals with the tools they need to gather, analyze and report performance data with minimal technical assistance.”2
Project Description
The beneficiaries of this solution will be managers and staff members who need data or information to make decisions that will help the county improve its services or increase its efficiency. The organization as a whole will also benefit as better decision-making leads to improved outcomes.
The technical infrastructure that links current Hennepin County core information systems with the business intelligence solution will be developed by power users or technical staff who have business knowledge about the data. Information tools that county departments currently use will continue to be supported as needed after implementation of the business intelligence solution. The business intelligence software will also support results-based planning, performance measurement and Balanced Scorecard activities.
Project Description
Background
Data gathering and information systems at Hennepin County have evolved over the last 20 years. Centralized databases such as the aging Financial Accounting and Reporting System (FARS) and the Payroll/Personnel System contain data essential to the operation of virtually all departments. In addition, each department maintains its own set of business systems used to support its day-to-day activities. When county leaders need information from these systems, it is difficult to obtain and they often need technical assistance from data experts.
As budgets and staff levels shrink, yet more services need to be delivered, county leaders will need efficient access to clean and accurate data to use in their decision-making processes. With few exceptions, the data infrastructure in Hennepin County has been built to serve departmental interests. It can be extremely difficult and costly to create reports with data that crosses departmental boundaries or that links business data with financial data (to determine cost per unit of service, for example).
1) Operate 2) Consolidate 3) Integrate 4) Optimize 5) Innovate Five Steps of Information Evolution
Hennepin County Desired Level3
Hennepin County Current Level1.5
To understand the county’s level of maturity in its ability to use information successfully, it is helpful to look at an Information
Evolution Model. As an organization moves through the levels in the Information Evolution Model, its capacity to deliver
increased business value through the use of information expands.
Hennepin County is currently at level 1.5 in the Information Evolution Model. Departmental goals and objectives continue to drive data collection and analysis, while enterprise goals are secondary. We are striving to attain level three, in which staff come to rely on cross-enterprise information for making decisions and there is a commitment to perform at an organizational level. Focus on personal information Focus on department information Focus on enterprise information Focus on collaboration and interdependency Focus on service enhancements through creativity
Project Funding Proposal
October 22, 2004
Project Need
To support Hennepin Results Strategic Management
5
Planning, budgeting and decision-making for results within the Hennepin Results Strategic Management Framework means that the county is going to need to be able to measure its results by collecting, analyzing and reporting data from departments and programs across the county, and from different countywide systems. As has already been discussed, it is exceedingly difficult to access the information in many county legacy systems without an advanced level of technical knowledge and even more difficult to create a single report incorporating data obtained from different county systems or that compares data across systems.
Project Need
Provide all levels of county staff an easy to use solution
for self-service reports
In fall 2003, two projects converged to begin researching a solution to a long-standing and increasing need for enhanced reporting capabilities from the county’s disparate data sources.
Performance Measurement Advisory Team Technology Subcommittee
The Performance Measurement Advisory Team Technology Subcommittee’s Interim report found that, “virtually every department interviewed expressed the need for assistance to become more efficient at using data and relating it to their business process. Retrieving, analyzing and presenting data from these [legacy] systems in the current environment is time consuming, and creating ad hoc reports requires a high level of skill.”
Advanced Leadership Academy Financial Reporting Project
The Advanced Leadership Academy (ALMA) Financial Reporting Project Proposal states, “While FARS meets many financial reporting needs, the county has grown in size and complexity since its installation. Departments have developed additional financial reporting tools to meet varied and unique requirements. Technology has improved, as has the expectation for increased access to information that can be presented in various ways to facilitate management analysis and decisions at departmental and countywide levels.”
Discussions with managers and staff at all levels clearly indicate that the county should provide the tools and education necessary to promote effective business intelligence activities.
System requirements identified by county executives and managers
Straightforward and easy to use interface giving access to accurate information Ad hoc report capabilities
Drill down from summary reports to more detailed data if desired Provide hard copy summaries to be used in meetings
Interactive capabilities to enter comments and questions within the report Ability to send alerts by e-mail
Graphical representation of trends and benchmark data Guide to available data and reports
Project Funding Proposal
October 22, 2004
Project Need
Provide all levels of county staff an easy to use solution
for self-service reports (continued)
7 System requirements identified by
analysts
Ability to extract and clean data from county data sources Ability to export to existing desktop analytical tools such as Excel Advanced analytical capabilities
Data dictionary for business and technical use Ability to reuse existing data sources and reports
Integration with the county’s Geographical Information System
Access to data external to the county (State of Minnesota Department of Human Services and other data) Documented, replicable processes for creation of information
Disparate Data Source Disparate Data Source Disparate Data Source Business Users BusinessUsers Business Users
Data Access Via Business Intelligence and Reporting Solution All Business Users All Data Sources Current Efforts to Access Data Proposed Efforts to Access Data
Project Need
Provide all levels of county staff an easy to use solution
for self-service reports (continued)
200 Families Project
In 1997, a team of analysts were asked to identify the county’s 200 most costly family clients. Many hours (roughly estimated at 500 person hours over three and a half months) were spent learning what data to use, and then gathering and analyzing client and cost information for families across the multitude of data systems. The goal of the project was to identify ways to serve these families better and at lower cost. The difficulty and expense involved in collecting the information in order to identify the families has precluded repeating this analysis. The Phase 1 report of this project documented the difficulty of gathering data across departments.
Project Need
Assist with performance measurement reporting
“A countywide performance measurement system is a critical component of the county’s Strategic Framework that will link strategic planning, program planning, and priority setting and budgeting with day-to-day decision-making. Such a system creates better results and more informed decision-making.” Focusing on Results: From Measurement to Management at Hennepin County Government, March 15, 2002
The Performance Measurement Advisory Team (PMAT) held a series of focus groups in March 2003 to determine the kind of information and assistance staff need to report results for measures on their Balanced Scorecards. The 23 staff members who participated in the focus groups were from departments that had already begun gathering data to report results for measures on their Balanced Scorecards. Focus group participants identified several needs related to information and data gathering for Balanced Scorecard reporting:
User-friendly, efficient, flexible, accurate, electronic way of extracting and aggregating information from disparate county systems – especially FARS and the Payroll/Personnel System
Standardization on a user-friendly, flexible system for data presentation and compilation to enhance quality, consistency and comparisons
Universal access to tools and templates for all county departments Identify and leverage common data needs
A collection of historical data for baselining and trend analysis
Help with training, promoting and presenting concepts and measures to staff Help with best practices in developing the best, most useful and consistent BSC
The report says, “it is possible to develop an infrastructure to support a routine identification of high cost cases. This would require, however, a data system that can combine data from various systems, add credible cost data and protect the privacy of the clients in compliance with state and federal laws.” Implementing a business intelligence and performance reporting solution will allow the county to complete projects, like the 200 Families Project, more easily in the future.
Business data at the county is housed in innumerable small systems and Lotus Notes databases, as well as approximately 100 different business systems. County staff members use at least 20 different software tools to access and create reports from the data contained in these systems.
Project Funding Proposal
October 22, 2004
9
Project Need
Create better understanding of and accessibility to the data
In order to support management self-service queries and reporting, raw transactional data often needs to be cleansed and integrated into a business view of the data. The tools of a business intelligence platform provide an essential metadata layer where consistent business definitions of the data and sources are created for accessibility and understanding in an enterprise context. The result is quality data that is readily available enterprise-wide for analysis and transformation into information, knowledge and wisdom.
Wisdom Knowledge Information Raw Data Business Intelligence Goals
Project Need
Extend the useful life of the county’s legacy systems
Systems such as FARS and the Payroll/Personnel System — while they perform their job of capturing and storing detailed transaction information very well — do not allow users to easily and quickly create reports in up-to-date, readable formats. It has been estimated that replacing just these two systems could cost 10 times the price of the business intelligence solution. Investing in a business intelligence and reporting solution will enable the county to efficiently derive more useful information from these existing systems thus alleviating some of the pressure to invest in replacements.
What is Metadata?
Information about data and data sources – how it was derived, formatting and access authorizations, as well as business rules.
Project Need
Workforce planning issues/knowledge transfer
The current information infrastructure at the county is reliant upon the availability of key data experts to fulfill requests for management reports. In many cases the technology used by these individuals to obtain report information is aging and poorly documented.
Building the information infrastructure layer behind the business intelligence reporting solution will create dictionaries of data, describing its business meaning and proper usage.
Building self-service management reports decreases the dependence on key individuals and disseminates the ability to generate high quality reports among a wider base of county staff.
Within Five Years...
By 2008, about 20 percent of county staff will be eligible for retirement. Few individuals coming to the county are familiar with the older technologies often used in legacy systems, some of which pre-date the use of personal computers in the business world.
Project Need
Future e-government initiatives
Using the selected business intelligence solution, self-service report generation can be extended to allow county residents to make queries at the county’s Web site. This would save time and money as county
customers would be able to receive answers to their questions immediately and find them on their own, rather than having to call a county staff member.
County business partners could also log on to a secure Web site and generate simple reports about their business with the county. One example of this kind of application is the State of Minnesota’s Vital Statistics Interactive Queries or I.Q. Web site.
The Minnesota Vital Statistics Interactive Queries (I.Q.) site allows the user to customize online reports of birth, death and population data. It produces data tables, charts and maps at a county, regional and statewide level.
Project Funding Proposal
October 22, 2004
11
Project Need
Conclusion
County management has embraced data-driven decision-making and performance measurement principles.
County staff members have long recognized the need for more efficient ways to create useful information from our disparate sources of data.
As the county’s reporting needs have become more pronounced, recent software advances have given us the ability to extract data from our existing legacy systems, transform it into useable and recyclable information, and make use of Web browser technology for the presentation and delivery of this information.
This new software will help the county satisfy both old and new needs to create better, more cost-effective business information.
County citizens and business partners benefit from and expect to have easy access to data and information both from county employees and through self-service Web sites.
Project Scope
Implement a business intelligence and reporting solution
Roll out a solution that has easy to use reporting and analysis functions and that meets our business needs for consistent use of information enterprise-wide
Design projects that will demonstrate early success to executives, managers, analysts and operational staff
Establish training plans for each group of users
Plan for changes to county culture that will accompany this solution
Construct the infrastructure necessary for quality reporting from county and county business partner information systems
Purchase the necessary software licenses based on initial plans for use Install the software and begin configuration and testing
Build a dictionary that serves as a guide to available data and sources of information for business and technical use
Establish security models for use of the solution Determine any needed data transformations
Design and create data structures that enable staff to obtain quick answers to questions Build access to external data sources
Develop a plan to support the tool financially
Create a proposed roadmap and plan for ongoing development and use of the solution Increase the functionality of the tool as needed and available
Continue to add additional data sources as needed
This project’s scope is threefold:
Implement a business intelligence and reporting solution, rolling the initial business solutions out to county employees in a planned way
Construct the infrastructure necessary to enable consistent, quality reporting from county and county business partner information systems
Create a proposed roadmap and plan for ongoing development and use of the solution
Project Scope
Objectives
1. 2.
Project Funding Proposal
October 22, 2004
Project Scope
Benefits
13 Benefits for the organization
Enable data-driven decision-making
Universal access to data and information reporting tools and templates for all county departments More effective data access and timely delivery of information for decision-making
Increased accuracy and consistency of the information delivered to management Consistency across departments in terminology and appearance of data
Alleviates the pressure to replace the FARS and Payroll/Personnel Systems and perhaps extends the life of these and other legacy systems
Makes creation of reports (even complex ones) repeatable
Provides Hennepin County with the tools to anticipate problems rather than react to them Goal alignment across the enterprise
Reduces information latency cost – how much time it takes to answer a question
Self-service Web-based queries can provide immediate response to customer service questions
Ability to closely monitor data and maintain internal control over information and data stored in the system Less duplication of efforts and redundancy of processes
Reduces the cost of doing business by identifying areas for process improvement Supports future planning and resource allocation
Helps maximize revenue from services delivered to clients
Benefits for the public and business partners
Better decision-making results in better services to the public More efficient use of county staff time
Benefit to the State of Minnesota through more effective communication with state data warehouses Possibility of e-government applications allowing constituents and business partners to create their own queries and reports via the Web
Project Scope
Deliverables
An infrastructure that provides better sharing of information and the county’s data assets A reporting solution that is easy to use for different types of users
A two pronged path for rolling out the solution –an early success path to help staff learn the software by accessing selected data and a longer-term path providing better functionality for experienced users Training and documentation for the solution
A tool that will assist departments in their performance measurement analysis A service level agreement recommending internal support structure
A proposed plan for the ongoing maintenance and development of the solution and ways to foster the necessary culture changes
A support infrastructure defining the roles and responsibilities for ongoing internal support
Project Scope
Exclusions
The scope of this project does not include the creation of new databases or data collection systems to supply data that is missing from current county systems
Project Scope
Assumptions
Subject matter experts and other resources will be available according to the project plan A suitable way to provide for the ongoing financing of the solution will be determined
There is a willingness to change from a department-centered to an enterprise-centered culture at the county
Project Scope
Constraints
Only selected county data will be immediately available to county staff through the solution, other data sources will be added over time
There must be sufficient time allowed to work through new issues that will arise when implementing the solution, such as could arise when accessing the State of Minnesota data and shared index data
Project Funding Proposal
October 22, 2004
15
Project Scope
Risks
Effective planning for both initial implementation and ongoing organizational support for this project can mitigate many of the risks involved including:
There is a risk that management is not ready to make decisions based on data
It is important that a tool used for performance measurement reporting not take the place of face-to-face discussions between county staff
Implementation of the business intelligence solution means large scale culture changes for Hennepin County government and its employees
Change in how many knowledge workers conduct their business Change from department-centered thinking to enterprise-wide thinking
It is likely some staff will not want to transition from the tools they are currently using to a new one – a plan must be in place to know what to do in this event
There are remaining questions about the system’s capabilities
Will the solution be easy enough to use for executives and managers?
Will the solution be able to access all the information that we need it to access?
Project Scope
Critical Success Factors
This project will be successful if the software solution is adopted as an integral part of the county’s everyday business.
What we need to do to ensure this project’s success
Secure the support of all department managers Link business intelligence to enterprise goals
Commit to training county staff members to accept change
Build a high quality, highly useable infrastructure to support the solution and provide links to the data sources
Build a coexistence strategy for using both current and new technologies side by side during implementation
Plan for early success in these areas:
Static reporting from FARS and Payroll/Personnel Systems Ad hoc capabilities for managers
Assistance with performance measurement and Balanced Scorecard reporting Plan for ongoing support of the product – both organizationally and technically Reward employees for achieving cross-functional results
Time and Schedule Estimates
The following estimates are based on our experience and information provided by business intelligence vendors. This is a preliminary schedule and subject to change. New information may alter the schedule. These are the most accurate estimates that are possible at this stage of the project. Some of the activities can be conducted concurrently.
The timeline for this project is projected to be 24 months from the completion of a successful proof of concept. Milestones will be defined in greater detail to evaluate project progress as well as vendor performance. Milestones will be identified in the following areas:
Easy access to enterprise-wide financial, payroll and human resources data with drill down capabilities including use of extract/transform/load (ETL) technology (Countywide through department/unit access to budget variance reporting with drill down to transaction level. Popular reports identified and distributed appropriately: FTE’s, turnover, vendor lists, and so forth.)
Balanced Scorecard presentation and analysis (All Scorecards connected and populated. Drill down from top to bottom within county and from strategy map to measure definition and trend data.)
Integration of financial and service data within business lines and departments - Human Services focus (Human Services outcome reporting as they define it; some smaller projects in other areas)
Executive views and alerts - all of the above (Executives and Top 300 managers have dashboard access and alerts that add value to their decision-making)
At milestone dates, progress reports will be provided Monthly reports to ITGB and project steering committee
1. 2. 4. 3.
4th Quarter 2005 2006 1st Quarter 2005 2nd Quarter 2005 3rd Quarter 2005 Contract Signed Build Infrastructure Install Software Countywide Scorecard Proof of Concept for Financial Data Project Completion
Public Works Business Line ScorecardsDevelop Short Term, Easy Access to Financial Data, Train Power Users
Roll Out Scorecards To Other Business Lines Develop Reports
Train Managers/Staff
Refine Scorecards and Automated Gathering of Performance Measures
Implement Reporting for Other Data Sources
Project Funding Proposal
October 22, 2004
17
Preliminary Cost Estimates
Note to reader: Cost estimates at the proposal stage are preliminary and subject to change. Additional
information gained after proposal acceptance may alter the estimate. We have exercised care to provide the most accurate estimates that are possible at this stage of the project.
Payments to the vendor will be based on performance milestones specified in the contract Software will be acquired as needed to help control costs
The cost of the redirection of existing resources is not included in the cost of the project The cost of internal staffing resources will need to be addressed in the implementation plan The process of learning to use the new tool will be a temporary cost
Item
Software Costs 1st Year Maintenance Implementation Costs Travel and Training
Hardware, Depreciation & IT Support
Total $599,195 $236,044 $345,000 $76,800 $72,000 $1,329,039
Next Steps
The return on investment from business intelligence comes from the actual consumption of information. To encourage business intelligence information consumption, the Gartner Group recommends pooling scarce analytical skills into a single team of specialists: The Business Intelligence Competency Center. This team supports business intelligence tools and processes, developing synergy and efficiencies that the organization can leverage for maximum ROI. Team members
should be proficient in more than one of three core disciplines: IT skills, analytic skills or business skills. The Competency Center collaborates with business areas to achieve strategic goals.
Negotiate a performance-based contract, price and terms with vendor
Consider options for hardware and installation of software
Recommend the structure for a Business Intelligence Design and Implementation Team that may include the following sub-teams:
Technical Reporting
Balanced Scorecard Training and Education Quality Assurance Culture Change
Develop a project plan for implementation and early adoption of the solution Recommend organizational changes
Create a business intelligence center of expertise in order to maximize the investment and ensure the success and sustained use of the tool. This team could facilitate and coordinate the distributed use of the solution including training, product administration and promotion.
Additional supporting documentation is available upon request.
Project Completion