Loading...
HomeMy WebLinkAbout10/20/2009, B6 - FOXPRO APPLICATION REPLACEMENT counat ^ w,°� 10-20-09 j AQcnaa Repout i:mn b CITY OF SAN LUIS OBISPO FROM: Bill Statler, Director of Finance & Information Technology Rachel Messner, Administrative Analyst SUBJECT: FOXPRO APPLICATION REPLACEMENT RECOMMENDATION Review and.discuss the City's Visual FoxPro application replacement project plan. DISCUSSION Background The City currently uses 34 computer applications written and customized in Visual FoxPro, a software program that is no longer supported by Microsoft. These applications were created for City staff over the past 25 years by an outside, one-person consulting firm (Bonnie McKee), who maintained and modified them as necessary. These applications are the user interface software for a wide range of mission-critical City business processes and are accessed by all departments. Many of the Visual FoxPro applications, such as work orders and building permits, are mission- critical to departmental operations. Other applications, such as land use inventory and timecards, are also mission-critical and affect organization-wide operations. Software Support Challenges The City has benefitted significantly over the past 25 years from high-quality, low-cost software in contracting with Ms. McKee for the development and support of these integrated applications. However, we are now facing two distinct challenges in supporting these applications: 1. Obsolescent Software. Microsoft has stated that it will no longer support Visual FoxPro nor will it release any further versions. The City has very limited ability to support and maintain these custom applications since this is a disappearing programming language and the code libraries that power them have been heavily customized by Ms. McKee, who recently passed away. 2. No riable Support for Existing risual FoxPro Applications. The obsolescence of the software language has been compounded by the recent and sudden death of Ms. McKee. During City staff efforts to find replacement contract programmers, it has become clear that due to the outdated software and lack of programming documentation, it will not be possible to contract with programmers both willing and capable of maintaining the current applications. FoxPro Application Replacement Page 2 In summary, the City has been well-served over the past 25 years from high-quality integrated applications at a very low cost in contracting with Ms. McKee. However, due to the lack of reliable, continuing support for these customized applications, it is necessary to replace them with one or more systems that will meet a variety of user needs, allow integration among programs and retain historical data. While this will require an unplanned resource commitment to do so, this replacement is necessary in delivering mission-critical, day-to-day services to our community in an efficient and cost effective manner. Original Plan The four-year Capital Improvement Plan (CIP) in the 2009-11 Financial Plan showed converting these applications to Microsoft's current `.NET" framework in 2011-13 at an estimated cost of $370,000. However, the City can no longer wait several years to convert these applications due to the lack of support and maintenance resources. The focus has now shifted from converting the applications to more current software to the more difficult and resource-intensive task of replacing them entirely. IT Acquisition and Support Policy Although the City has been well-served for many years by these custom, integrated Visual FoxPro applications that were developed and supported at a very low cost, the City's IT Acquisition and Support Policy supports using one or more "packaged" software systems in replacing these existing applications. As summarized in the attached Project Plan for replacing these applications, the policy outlines three perspectives when acquiring IT systems: risk assessment; acquisition and development strategies; and the decision-making strategy. Based on these factors, the City should use the packaged system approach rather than continue to use individual customized programs. Replacement Strategy The attached Project Plan provides additional background information on why it is important to replace these applications and on the City's IT Acquisition and Support Policy in supporting the "packaged system" approached. It also includes a detailed listing of the applications; outlines key tasks and due dates; and discusses the challenges we will face in replacing these applications. The goal is to complete the selection process by March 2010; and complete application installation by September 2010. Next Steps As detailed in the Project Plan, we have identified six essential steps for this project. 1. Complete inventory of all Visual FoxPro applications and functionality, and identify appropriate application groups. 2. Complete high-level assessment of other IT needs, including processes automated with software other than Visual FoxPro, as well as processes that are not currently automated. FoxPro Appfication Replacement Page 3 3. Select vendor(s) through a Request for Qualifications (RFQ) and subsequent Request for Proposals (RFP) process. 4. Develop ongoing maintenance plan. 5. Implement software system(s). 6. Train users on new software. Project Schedule The following summarizes the anticipated project schedule: Issue RFQ 10/9/09 Hold pre-qualification teleconference 10/20/09 Receive qualifications l l/l/09 Evaluate submittals/vendor demonstrations 11/23/09 Present RFP and funding strategy to Council for approval 1/5/10 Issue RFP to selected vendors 1/6/10 Receive proposals 2/8/10 Evaluate proposals/conduct interviews 3/26/10 Award contract(s) 3/31/10 Convert databases/conduct staff training 8/15/10 Complete replacement application installation 9/30/10 CONCURRENCES The Information Technology Steering Committee concurs with this project plan. Additionally, all City departments have been extensively involved in the Visual FoxPro application inventory and IT needs assessment. FISCAL IMPACT It is too early to identify the financial resources necessary for this project; however, significant staff resources will be required. As reflected in the 2011-13 CIP, simply upgrading to a ".NET" system was estimated to cost approximately $370,000. Accordingly, replacing all Visual FoxPro applications is likely to cost significantly more. Based on the experiences of other organizations, rough estimates range from $500,000 to $1.5 million. However, we will not have a clearer idea of likely costs until completion of the RFQ process. ATTACHMENT FoxPro Application Replacement Project Plan T:FoxPro Application RLplacennenJCouncil Agenda Report/10-20-09 86 -3 _JACHMENT Project Plan FOXP�,RO APPLICATION REPLACEMENT October 1, 2009 PURPOSE Respond to the lack of reliable, continuing support for the City's customized Visual FoxPro applications by replacing them with one or more packaged systems that will meet a variety of user needs, allow integration among programs and retain historical data. OBJECTIVES AND DELIVERABLES By March 31, 2010 1. Complete application inventory, identify replacement priorities and determine application groups (`blusters") as appropriate. 2. Select vendor(s) and begin replacing existing Visual FoxPro applications with appropriate packaged systems in accordance with the City's Information Technology (IT) Acquisition and Support Policy. 3. Allocate appropriate resources to acquire and support the replacement applications. 4. Develop training strategy for all City staff who use these applications. In the Interim Given the lack of vendor resources, it will be very difficult to support and maintain these applications in the interim (which is one of the reasons why it is so important to complete their replacement quickly). In-house IT staff has some ability to make minor programming changes. For this reason, any changes to these programs until they are replaced will need to be limited to those that are essential for mission- critical functions and within the capacity of existing staff. BACKGROUND The City currently uses 35 applications written and customized in Microsoft Visual FoxPro. These applications were created over the past 20 years by an outside, one-person consulting firm (Bonnie McKee), who maintained and modified them as necessary. These applications are the user interface software for a wide range of mission-critical applications throughout the organization, including land use inventory, geographic information system (GIS), payroll timecards, and work orders. The applications automate City business processes and are accessed by all departments. Most of the applications are front ends/user portals to Microsoft Structured Query Language (SQL) databases. There are two distinct challenges facing the City in supporting these applications: 1. Obsolescent Software. Microsoft has stated that it will no longer support Visual FoxPro nor will it release any further versions. The City has very limited ability to support and maintain these custom Visual FoxPro applications since this is a disappearing programming language and the code libraries that power the City's applications have been customized by Ms. McKee, who recently passed away. Support for both Visual FoxPro and ESRI's Map Objects (which powers the maps in the City's applications) is scheduled to end within the next few years. The specific version of Map Objects currently used by the City has been end-of-life for several years. For this reason, the four-year �b �`f ATTAC MENT FoxPro Application Replacement Project Plan Page 2 Capital Improvement Plan (CIP) in the 2009-11 Financial Plan showed converting these applications to Microsoft's current ".NET" framework in 2011-13 at an estimated cost of$370,000. 2. No Vable Support for Existing Visual FoxPro Applications. The obsolescence of the software language has been compounded by the recent and sudden death of Ms. McKee. In our efforts to find replacement contract programmers, it has become clear that due to the outdated software and lack of programming documentation, it will not be possible to contract with programmers both willing and capable of maintaining the City's current applications. In short, the challenge facing the City has shifted from converting these applications to more current software to the more difficult and resource-intensive task of replacing them entirely. IT Acquisition and Support Policy In 2001, the City contracted with the Gartner Group for a five-year IT Strategic Plan. At that time, Gartner advised against using locally-developed, customized software for mission-critical applications; and the recommendations of the IT Strategic Plan regarding system acquisition and support were subsequently adopted as part of the City's IT Acquisition and Support Policy. However, for many years prior to the IT Strategic Plan, the City was already contracting with Ms. McKee for design, development and maintenance of customized Visual FoxPro applications. While this approach was not consistent with the IT Acquisitions and Support Policy, the City was well-served for many years by these custom, integrated applications provided at a low cost that did an excellent job of meeting City needs. However, because the consultant is no longer available, and successors have been unable to support these applications, we now recommend using the packaged system approach in accordance with the IT Acquisition and Support Policy. This policy outlines three perspectives when acquiring IT systems: 1. Risk assessment 2. Locally-developed versus packaged systems features: advantages and disadvantages 3. Decision-making strategies: decentralized versus centralized StraLen, Selection: Risk Assessment I Low Risk High Risk Not Mission-Critical C Mission Criticality Highly Mission-Critical Familiar Technology C Technical Complexity ' New Technology Single Department C Organizational Complexity Organization-Wide Small, Simple Data Base C :application Size Extremely Large and Complex Loral Develo rnent Patika e- stern Ac uisition All of the factors except for technical complexity place most of the City's Visual FoxPro applications in the"packaged system" category. ATTR -WENT FoxPro Application Replacement Project Plan Page 3 Ac uisition and Development Strate tes Local Development ■ Application fully customized to meet local ■ Package provides pre-defined functionality; requirements. changes should be limited to formatting issues. ■ Relatively lower cost. a Relariyely higher cost. ■ Ability to meet City programming and data base ■ Selection criteria can include its ability to meet requirements. City data base standards_ ■ Design limited to local knowledge. a Design reflects thinking of a number of agencies. ■ Support limited to small. local organizations— ■ Support provided by the vendor as pan of the often a single person. package,with assistance by user groups. ■ Changes in requirements can be identified and ■ User groups and vendor can jointly develop implemented locally. modifications over time as needed to meet changing requirements. ■ There are inherent risks in new development ■ Ability to select system that is already efforts: operational.and conduct reference and • Incorrect or incomplete design. background checks before acquiring. • Faulty programming. • Incomplete or faulty testing. ■ Tendency to"pave the cow path- by ■ Tendency to re-look at business policies, programming to current City business policies, practices and procedures for improvement as part practices and procedures. of the acquisition and implementation process. Again, each of these features supports using packaged systems in replacing our existing Visual FoxPro applications. Decision-Making.Strate Department Decision Centralized Decision • Application is not part of strategic system • Application is part of a strategic system initiative. initiative- • Application can be supported on current City- • Application requires introduction of new standard computers, operating systems and technology; or requires system upgrades to networks without upgrades or deterioration in support the new application or maintain system system performance. performance. • All application users are in one department. Application users are in more than one department. • All information is generated and entered by one 0 Information is generated and entered by more department. than one department. • The application is not required for the operation • The application is required for the operation of of other applications, systems or work outside of other applications, systems or work outside of the the department. department. • Interfaces to existing or proposed applications • Application may need to integrate with and systems are well defined and requirements applications in other departments beyond well- can be met. defined standards. _ ATTR WENT FoxPro Application Replacement Project Plan Page 4 Based on the factors above, it makes sense to replace these applications with one or more packaged systems rather than continuing to use individual customized programs; and for the selection process and subsequent implementation to be highly coordinated, with oversight by the IT Steering Committee (ITSC). CURRENT SITUATION The following chart lists each Visual FoxPro application currently in use by all City departments. It identifies the department responsible for managing the application as well as other departments that use and depend on the application to do business: Departments That Use the Application Managing Public Comm Parks& City Human Finance Application Department Police Fire Works Utilities Dev Rec Admin Arty Res &IT Planning Applications Comm Dev x x x x Unreinforced Comm Dev x x x Masonry Planning Comm Dev x Library Building Comm Dev x x x x x Permits Plan Review Comm Dev x x x x x x x Violation Comm Dev x x x x x x Tracking Land Use Comm Dev x x x x x x x x x x Land Use Comm Dev x x x Maintenance Planning Job Comm Dev x x Tickets Building Damage Comm Dev x x x x x X Assessment Fixed Assets Finance&IT x Timecard Finance&IT x x x x x x x x x x Fire Calendar Fire x Fire Fire x Prevention Weed Fire x Abatement Fire Hose Fire x Tracking(?) Overtime& Fire x Staffing FoxPro Application Replacement Project Plan Page 5 Departments That Use the Application Managing Public Comm Parks& City Human Finance Application Department Police Fire Works Utilities Dev Rec Admin Airy Res &IT Plan File Public Works x x x x Management Bid Lists Public Works x Encroachment Public Works x x x x GIS Service Public Works x x x x x x x x x x Requests Parks Service Public Works x Requests Streets Service Public Works x Requests Building Maint Service Public Works x Requests General Service Public Works x x x x x x x x x x Requests Traffic Model TAZ Output Public Works x Processor Payment Vouchers/Pur Public Works x x x x x chase Orders GeoData Public Works x x x x x x x x x x Overtime Public Works x CIP Project Public Works x Management Trees Service Public Works x Request Keytrack Public Works x Industrial Waste Permit Utilities x Tracking Utilities Work Utilities/ Order Comm Dev x x x x Water Utilities x x x Conservation *Excludes FoxPro applications no longer in use or not in need of replacement ATI JHMENT FoxPro Application Replacement Project Plan Page 6 PROJECT STRATEGY AND SCOPE Project Strategy 1. Complete inventory of all r1sual FoxPro applications Meet with representatives from each department to determine which applications are managed and used by them and for what purpose. Also determine departments' application needs and desired functionality. Identify application groups and replacement priorities. 2. Complete high-level assessment of other IT needs. Gather information from department contacts on three other areas: a. Processes automated with software other than Visual FoxPro that work well. b. Processes automated with software other than Visual FoxPro that don't work well. c. Processes not currently automated that should be. While these other unmet needs may not be addressed in this process, they are important to consider in evaluating vendors. 3. Select vendors. Prepare and issue Request for Qualifications (RFQ) to identified vendors in order to assess their qualifications in meeting City application needs. Based on responses, prepare and issue Request for Proposals (RFP) to most qualified vendors based on application groups. Proposals will be evaluated by appropriate review teams using several criteria in assessing which systems best meet the City's needs, including responsiveness to the RFP; results of demonstrations and reference checks; implementation strategies and schedule; system cost for both acquisition and ongoing support; and ongoing system support needs by in-house staff. There will be one RFQ issued to identified vendors. However, based on the vendor responses to the RFQ, as well as the designation of application groups, it may be necessary to develop multiple RFPs to meet the wide variety of application needs and functionality. 4. Develop maintenance plan. Determine the amount of staffing and financial resources necessary to maintain and support the new applications. 5. Implement system. Develop and implement replacement systems based on identified priorities and application groups. 6. Train users. Develop and implement comprehensive strategy to coordinate the training of all City employees on the replacement applications. Project Scope The City currently uses 35 custom applications written in Visual FoxPro. Many of these are mission- critical applications that affect every department and must be replaced with systems that will meet our short-term needs as well as address long-term functionality and integration issues. Under the City's IT Acquisitions and Support Policy, packaged systems should be used in making this replacement. It would be preferable to move to an organization-wide, over-arching software application solution. ATTR -iMENT FoxPro Application Replacement Project.Plan Page 7 However, given the market place, it is more likely that existing applications will be replaced by several vendors, with applications clustered by similar groupings (such as permits, land-based/GIS applications and work orders). Many of the Visual FoxPro applications, such as work orders and building permits, are mission-critical to departmental operations. Other applications, such as land use inventory and timecards, are also mission-critical and affect organization-wide operations. As noted above, initial replacement plans focused solely on converting these applications to a more current software program, such as ".NET." Accordingly, as part of the four-year CIP, the 2009-11 Financial Plan showed converting these applications in 2011-13 at an estimated cost of$370,000. However, the City can no longer wait several years to address these applications due to the lack of support and maintenance resources. In the present situation, three broader concerns emerge: 1. How do we support these applications in the near-term? 2. What should be our longer-term strategy? 3. How do we get there from here? KEY PROJECT ASSUMPTIONS 1. We will be able to adequately support these applications with in-house resources until they are replaced. 2. We will be able to quickly select a packaged system for each application group at a reasonable cost. 3. These solutions will meet our short and long-term needs; maintain the current level of operational efficiency; allow program integration; and retain historical data. PROJECT CONSTRAINTS AND LIMITATIONS Project Constraints 1. Urgency to replace applications within a short time frame while keeping long-term needs in mind. 2. Commitment of significant financial and staffing resources in a challenging fiscal environment. 3. Need for stakeholder support and buy-in for the project. 4. Standardization of custom applications. 5. Integration of applications and prevention of overlap. 6. Highly unlikely that there will be one vendor with an acceptable universal solution. 7. Training users on new applications. 8. Ongoing support and maintenance of new programs. Project Limitations 1. Under the proposed packaged system approach, replacement applications will not be customized. However, City staff have become accustomed to using applications customized exactly to their specifications and that can be quickly modified at a low cost. It is highly unlikely that any packaged program will be able to deliver the exact functionality that users have become accustomed to. On the other hand, the benefit of the packaged systems is the greater assurance of their viability as ongoing application support is not dependent on one individual. B�-/° ATT! 'HMENT FoxPro Application Replacement Project Plan Page 8 2. It will be difficult to balance the need to make good long-term decisions with the need to also act quickly in selecting and implemeting replacement applications. PROJECT ORGANIZATION AND TEAM Sponsor: Director of Finance & Information Technology Oversight: IT Steering Committee (ITSC) Project Manager: IT Manager Team: Department Heads (from departments managing applications); Application Administrators; Finance & IT Administrative Analyst; IT staff STAKEHOLDERS Stakeholders include all City employees and the community at large that benefits from the cost-effective services performed and information provided by these applications. PROJECT TASKS AND SCHEDULE A.Initial Project Planning 1. Identify department contacts for all applications. Admin Analyst 8/28/09 2. Complete application inventory and identify"what and Application Administrators 9/24/09 why"for each. 3. Determine application groups, assess priorities, identify top Application Administrators; 9/28/09 vendors for each application and review project plan. ITSC 4. Present project plan to Council. Director of Finance& IT; IT 10/20/09 Manager; Admin Analyst B.Vendor Identification and Qualifications 1. Prepare draft RFQ. IT Manager; Admin Analyst 9/28/09 2. Review and approve RFQ. ITSC; Application 10/5/09 Administrators; IT Staff 3. Send RFQ to selected vendors. Admin Analyst 10/9/09 4. Conduct pre-qualification teleconference(s). ITSC; Application 10/20/09 Administrators; IT Staff, Admin Analyst 5. Submit qualifications package. Vendors 11/1/09 6. Evaluate submittals; conduct finalist interviews as needed; ITSC; Application 11/23/09 participate in vendor demonstrations and/or city visits as Administrators; IT Staff, necessary; finalize application groups and selected vendors. Admin Analyst C.Vendor Selection 1. Prepare draft RFP(s) for selected vendors. Admin Analyst; IT Manager 12/8/09 2. Review draft RFP(s). ITSC; Application 12/18/09 Administrators; IT Staff 3. Approve RFP(s)and appropriate funds. Council 1/5/10 4. Send RFP(s)to selected vendors. Admin Analyst 1/6/10 5. Submit proposals. Vendors 2/8/10 b%`// ATT' ;HMENT FoxPro Application Replacement Project Plan Page 9 6. Evaluate proposals and conduct interviews as necessary; ITSC; Application 3/26/10 prepare recommendation to City Manager. Administrators; IT Staff; Admin Analyst 7. Award contract(s). City Manager 3/31/10 D.Implementation* 1. Prepare detailed project implementation schedule. IT Manager; Admin Analyst 4/15/10 Application Administrators; Vendor(s) 2. Convert databases,complete installation and modify Application Administrators; IT 8/15/10 departmental procedures as needed to implement Staff;Vendor(s) applications. E.Training* 1. Identify training needs and develop strategy. Application Administrators; IT 4/15/10 Staff; Admin Analyst Vendor(s) 2. Finalize training schedule and delivery. Application Administrators; IT 5/15/10 Staff; Admin Analyst Vendor(s) 3. Conduct staff training. Vendor(s) 8/15/10 F.Project Wrap-Up 1. Troubleshoot and complete all aspects of project Application Administrators; IT 9/30/10 implementation. Staff 2. Present report on lessons learned to ITSC. IT Manager; Application 10/30/10 Administrators; IT Staff, Admin Analyst *Each identified application group may have its own implementation and training time schedule. However, they will allfollow the same process. PROJECT RESOURCES It is too early to identify the financial resources necessary for this project; however, significant staff resources will be required. As reflected in the 2011-13 CIP, simply upgrading to a ".NET" system was estimated to cost approximately $370,000. Accordingly, replacing all Visual FoxPro applications is likely to cost significantly more. Based on the experiences of other organizations, rough estimates range from $500,000 to $1.5 million. However, we will not have a clearer idea of likely costs until completion of the RFQ process.